MySQL Forums
Forum List  »  Optimizer & Parser

Index distribution stats
Posted by: Gabriel Tataranu
Date: May 12, 2007 09:54PM

I have a weird problem with query optimizer because of index distribution stats. Check it out:

explain select count(*) from some_table WHERE some_field > 2;

+----+-------------+------------+-------+---------------+---------------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------------+---------+------+---------+--------------------------+
| 1 | SIMPLE | some_table | range | some_field_idx | some_field_idx | 2 | NULL | 1028074 | Using where; Using index |
+----+-------------+------------+-------+---------------+---------------+---------+------+---------+--------------------------+

select count(*) from some_table WHERE some_field > 2;

+----------+
| count(*) |
+----------+
| 447742 |
+----------+
1 row in set (0.11 sec)

The some_field is smallint.

I have other servers, virtually identical to this one (same mysql version, database copied from one to another), giving much better index stats. SHOW INDEX does look the same on all servers.
I've tried ANALYZE TABLE - did nothing, OPTIMIZE TABLE - did something but not much.
Any ideas ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Index distribution stats
2800
May 12, 2007 09:54PM


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.