3

I've been working on optimisations for a while now, but I really can't crack this one.

The query:

SELECT `field` FROM `table` WHERE `column_id` = 12341

The index:

ALTER TABLE `table`
ADD INDEX `column_id` (`column_id`);

Now the explain of the query (EXPLAIN SELECT field FROM table WHERE column_id = 12341) shows me there are 50.000 rows. However, in reality, there are only 20.000 rows with column_id = 12341.

  • EXPLAIN shows me it's using the column_id index
  • However, it shows me there are '50.000' rows
  • In reality it's only 20.000 rows
  • I know the EXPLAIN rows is an estimate, but this is such an easy index..
  • This is an InnoDB
  • I tried optimize table / analyze table..

Now this could be annoying since MySQL uses the number of rows estimation to pick the right indexes and it could end up picking up the wrong index, simply because of the wrong estimate.

Is there some indexing trick I don't know about?

Lennaert van Dijke
  • 138
  • 5
  • 1
    possible duplicate of [Why the rows returns by "explain" is not equal to count()?](http://stackoverflow.com/questions/1037471/why-the-rows-returns-by-explain-is-not-equal-to-count) or http://stackoverflow.com/questions/1037471/why-the-rows-returns-by-explain-is-not-equal-to-count – gbn Jul 02 '13 at 14:15
  • I see. Especially in INNODB it's just an estimate. But a really annoying estimate, since MySQL is now picking the wrong indexes for more complex queries. Is there a way to force the estimate into a specific number? I really want to avoid `USE INDEX (column_id)`, so any solution to keep the estimates more accurate would be helpful. – Lennaert van Dijke Jul 02 '13 at 14:20

1 Answers1

0

Comparing count to explain is not really valid, since (as described in the already linked answer) explain shows the number of rows processed, not the number expected in the result set.

Hearth
  • 383
  • 1
  • 4
  • 13