Hi, On Wed, 2002-03-06 at 11:12, Shankar Unni wrote: > (Using mysql 3.23.38 - commercial version) > > I have a table that has two indexes: one on just "creationTime" > (iv_alert_creation_ix), and one on "(state, creationTime)" (iv_alert_state_ix). > > When I issue this query: > > explain select uuid from iv_alert > where state = 1 > and creationTime >= '20020201000000' > and creationTime <= '20020301000000' > order by state, creationTime desc; > > mysql says (output munged to fit): > > table iv_alert > type range > possible_keys iv_alert_creation_ix, iv_alert_state_ix > key iv_alert_creation_ix > key_len 4 > ref NULL > rows 1 > Extra where used; using filesort > > > Why doesn't it use the iv_alert_state_ix key? Is there any way to make it > do so? There can be lots of entries in that time range, but only a few > with "state". > > Of course, I'm using a synthetic database to test the query, and it > currently doesn't have that many rows - is the "explain" output influenced > by the actual data present, or is it just using a fixed heuristic?
The optimiser uses table statistics, if known (like from ANALYZE TABLE ...) But in any case, it does know about the total # of rows in a table. If the number is so low that using an index would make the query slower, then it will not use a query. So, indeed, EXPLAIN is affected by the actual table content (like cardinality), or at least the quantity (total number of rows). Therefore, usin EXPLAIN on this query with *this* data set is pretty much useless. You'll need to create a bigger data set if you want to do this kind of analysis. Regards, Arjen. -- MySQL Training in Brisbane: 18-22 March, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
