0

I have a big table (100 million entries) with three columns (id1, id2, id3)

All columns are int(11).

id1 is the primary key. So there is a default unique index on it.

I also created an index on id2. But the values of id2 are not unique. There are approximately 500.000 unique entries.

Now when I have the following query:

select count(*) from table where id2 < 100.000

The result of the query is approximately 4 million and the duration is 40 seconds (the fetch time is 0).

When I have the following query

select count(*) from table where id1 < 4.000.000

The result of the query is again 4 milion but the duration is about 6 seconds.

If I make the queries as below

select * from table where id2 < 100.000

select * from table where id1 < 4.000.000

The ratio of the performance difference gets much worse.

What might be the reason of this performance difference. The properties of the two indexes (as far as I learn from the SHOW INDEX command) are the same (for example both are b-tree indexes) except (as I noted above) one of them is an index on primary key and the other is a non-unique index.

Thanks Ahmet

Ahmet Yılmaz
  • 125
  • 1
  • 2
  • 8
  • 1
    1. What is your DB table store engine and MySQL version? MyISAM, InnoDB, etc. 2. Try COUNT(id1) instead of COUNT(*). 3. Read this question http://stackoverflow.com/questions/511820/select-count-is-slow-even-with-where-clause and the answers 4 How much RAM do you have available for your MySQL server? – Yasen Zhelev Apr 22 '13 at 15:24
  • DB Table Engine is InnoDB – Ahmet Yılmaz Apr 22 '13 at 19:15

0 Answers0