MySQL Forums
Forum List  »  Optimizer & Parser

Re: index choose and analyze table
Posted by: Toa Sty
Date: September 14, 2006 10:38AM

Just to reply to myself here.

I've done a bit more surfing and have just read that index cardinality is only used for join operations, and that for other cases, (such as WHERE somecol=1?) the number of rows that is seen in the EXPLAIN output is just a realtime estimate/guess (and thus I assume can be incorrect) This seems to suggest to me that running ANALYSE TABLE won't affect how a query is optimised if there's no joins.

Notes from 4.0.13 release say:
"ANALYZE TABLE now works also for InnoDB type tables. It makes 10 random dives to each of the index trees and updates index cardinality estimates accordingly. Note that since it is only an estimate, repeated runs of ANALYZE TABLE may produce different numbers. MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you may try using ANALYZE TABLE."

and mysqlperformanceblog.com says:

"Estimated number of rows may be very inaccurate: It is well known this number is aproximate but I guess not everyone knows how much wrong it could be. If number of rows is estimated performing btree dive with certainly structured tree you still can get number of rows off 3-10 times. It gets even worse if cardinality is used. In this case if data distribution is skewed and you statement looks at portion of data with different distribution results can be off by 10-100 times and even more."


My tests on MySQL 5.0/InnoDB don't reflect this though. The estimated number of rows examined for a WHERE condition does change as a result of ANALYZE TABLE.

eg:
mysql> explain select count(*) from s where timet < '1164699900';
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
| 1 | SIMPLE | s | range | timet | timet | 4 | | 114828 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> analyze table s;
+----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------+---------+----------+----------+
| mdtest.s | analyze | status | OK |
+----------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> explain select count(*) from s where timet < '1164699900';
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
| 1 | SIMPLE | s | range | timet | timet | 4 | | 110741 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from s where timet < '1164699900';
+----------+
| count(*) |
+----------+
| 223987 |
+----------+
1 row in set (0.13 sec)

etc (there's nothing modifying the table inbetween these statements)


So, does ANALYZE TABLE update something that isn't index cardinality but _is_ used for non-join query optimisation?
Or is this comment from the 4.0.13 release just out of date now?

Any detailed info on this kind of thing is much appreciated!

Thanks,
Toasty

-----------------------------------------
email: 'toasty'*3 at gmail

Options: ReplyQuote


Subject
Views
Written By
Posted
3216
September 11, 2006 04:37AM
2510
September 14, 2006 08:25AM
Re: index choose and analyze table
10646
September 14, 2006 10:38AM
3257
September 19, 2006 04:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.