maria-developers team mailing list archive

[worklog-noreply@xxxxxxxxxxxx]

-----------------------------------------------------------------------
                              WORKLOG TASK
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
TASK...........: Take into account the selectivity of single-table range predicates on
		non-indexed columns
CREATION DATE..: Wed, 06 Mar 2013, 17:25
SUPERVISOR.....: 
IMPLEMENTOR....: Igor
COPIES TO......: Psergey
CATEGORY.......: Server-BackLog
TASK ID........: 253 (http://askmonty.org/worklog/?tid=253)
VERSION........: Server-9.x
STATUS.........: Assigned
PRIORITY.......: 60
WORKED HOURS...: 0
ESTIMATE.......: 0 (hours remain)
ORIG. ESTIMATE.: 0

PROGRESS NOTES:



DESCRIPTION:

Description of the problem
==========================

Currently the optimizer completely ignores any conditions on non-indexed columns
when searching for the best execution plan. As a result in many cases it chooses
a suboptimal plan.

Let's consider the query:
SELECT * FROM t1,t2 WHERE t1.a=t2.a and t2.b BETWEEN (1,3)
assuming that:
-table t1 contains 100 records 
-table t2 contains 1000 records
-there is a primary index on t1(a)
-there is a secondary index on t2(a)
-there is no index defined on column t2.b
-the selectivity of the condition t2.b BETWEEN (1,3) is high (~ 1%)

Under these conditions currently the optimizer will choose the plan that
accesses t1 using a table scan
accesses t2 using index t2(a)
checks the condition t2.b BETWEEN (1,3)
This plan examines all rows of both tables and it performs 100 index look-ups.

The alternative plan that:
accesses table t2 in a table scan
checks the condition t2.b BETWEEN (1,3)
accesses t1 using index t1(a)
also would examine all rows of t2, but it performs only ~10 look-ups
to access ~10 rows of table t1.
The second plan is expected to be more efficient and it is so.

The plan to resolve the problem
===============================

1. Build single-table range conditions over different columns of all tables of
the join query.
2. Calculate the selectivity of each such condition using persistent statistics
on table columns.
3. Take this statistics into account when calculated the cardinality of each
partial join that are evaluated by the optimizer.


ESTIMATED WORK TIME

ESTIMATED COMPLETION DATE
-----------------------------------------------------------------------
WorkLog (v4.0.0)