Hi Toru
On 24/03/2010, at 11:24 AM, Toru Maesaka wrote:
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.
This certainly helps. Thank you.
Ya I think what you were asking for can be accomplished now without
polluting EXPLAIN output, so while a specific addition to the API is
possible, it's not necessary. Also, engines that need to work on MySQL/
MariaDB as well would need to take both environments into account -
that could get complicated.
I think it's very engine specific, essentially you'd need to define a
cost of each access type/method.
It all depends on how "real" your tables and indexes are, and whether
they're in RAM, on disk, or remote.
For instance, OQGRAPH tells the optimiser a few lies so that it always
favours using one of the (fake) hash indexes, we've just made a table
scan so expensive that it'll never get chosen. So there it's not a
case of the cost of a range scan, we don't want any kind of scan - if
the optimiser were to do a scan, the query results would be incorrect.
That's an extreme example, but it can happen and OQGRAPH won't be the
only one that requires stuff like that.
On MySQL/MariaDB (since it's currently not in Drizzle) "Engine
condition pushdown" can also help, as it would allow an engine to do
the right thing even if the optimiser were to go for a scan.
When the optimiser scans, it expects to also get back some non-
matching data (and filters it appropriately) - but with engine
condition pushdown the engine can take whatever measures to only
return what's needed. In the case of OQGRAPH that would mean doing the
appropriate computations, giving the same result as indexed access
would yield. Of course, if the appropriate index is used, that "hack"
is not necessary.
Engine condition pushdown can however still be handy, since even with
an indexed lookup it allows us to limit the computation and result
based on other non-indexed columns. The only alternative is to craft
more "fake" indexes so that the optimiser always picks an appropriate
index. Doable but messy.
Regards,
Arjen.
--
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Exceptional Services for MySQL at a fixed budget.
Follow our blog at http://openquery.com/blog/
OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
