Hi Toru,
Not sure if this helps, but by increasing the value returned by
scan_time(), increases the cost of a table scan, so the optimizer is
more likely to use an index scan.
On Mar 23, 2010, at 4:25 AM, Toru Maesaka wrote:
G'day Jay,
Hi Toru! By "prioritize" I'm assuming you mean "always prefer an
index
scan over a table scan"?
Yep! I should have made this more clear :( I also realized that I
spoke too soon after sending the previous email. Returning a constant
cost factor in records_in_range() does seem to work. I really need to
thank Domas for pointing me out to this approach.
Another solution that could be neat is an API that would return a
boolean of whether the index should be used for certain query types.
Actually, this is precisely what InnoDB does. :( Not only does this
result in a call to malloc() for a copy of the MySQL key value
coverted into InnoDB's internal format, but it also results in a new
InnoDB mem_heap allocation and a call to
btr_estimate_n_rows_in_range(). For *every* call to
Cursor::records_in_range().
Ouch. Although I wonder what the performance difference would be if we
could optimize this away...
Not exactly efficient...I agree that the interface and implementation
could be much simpler and cleaner.
Yeah... What really caught my attention was that an engine could end
up doing the same search twice in a simple statement that involves
ranges. That is, we provide the optimizer a count of rows in the given
range by traversing through the tree. IF the optimizer decides to use
the index, then the engine would most likely go through the same
search, which I wanted to avoid.
Cheers,
Toru
On Tue, Mar 23, 2010 at 4:31 AM, Jay Pipes <[email protected]> wrote:
On Mon, Mar 22, 2010 at 1:05 PM, Toru Maesaka <[email protected]> wrote:
Hi!
Would it make sense to have an engine-level option to tell the
optimizer to prioritize index use at all times? Something along the
line of HA_PRIORITIZE_INDEX.
Hi Toru! By "prioritize" I'm assuming you mean "always prefer an
index
scan over a table scan"?
Or do you mean something else?
I'm saying this because the relationship between
Cursor::records_in_range() and HA_READ_RANGE seems important to
effectively perform range scans at the moment. I tried purposely
returning a constant small cost factor in records_in_range() but it
seems that the range search will fail if the number of estimated
rows
(in my case a total lie) is significantly smaller than the number of
actual rows in the table. If I don't set, HA_READ_RANGE then it
seems
Drizzle will resort to a table scan (despite knowing that
'possible_keys' exist in EXPLAIN).
Preferably, I don't want to count through the number of rows in a
range for the optimizer on every simple query. Although... a
completely dishonest implementation of records_in_range() would
dirty
the EXPLAIN statement result. So there is definitely a downside to
what I was experimenting with.
Actually, this is precisely what InnoDB does. :( Not only does this
result in a call to malloc() for a copy of the MySQL key value
coverted into InnoDB's internal format, but it also results in a new
InnoDB mem_heap allocation and a call to
btr_estimate_n_rows_in_range(). For *every* call to
Cursor::records_in_range().
Not exactly efficient...I agree that the interface and implementation
could be much simpler and cleaner.
-jay
It would be great to hear what you all think :)
- Toru
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
--
Toru Maesaka <[email protected]>
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
