CODEWITHSUNDEEP
mysql
arruah
arruah

Reputation: 37

mysql index is not used on big table

I have a table with 40 million records.

When I make a request:

SELECT COUNT(*) 
FROM shares
WHERE id > 36725307
AND our_result = 'N'

index on our_result is used.

In a statement using a different value:

SELECT COUNT(*)
FROM shares
WHERE id > 36725307
AND our_result = 'Y'

the index is not used.

my.cnf

key_buffer_size         = 900M
join_buffer_size        = 512M
query_cache_size        = 128M
thread_cache_size       = 8
query_cache_limit       = 64M
tmp_table_size          = 512M
max_heap_table_size     = 128M
key_buffer_size         = 256M
key_cache_division_limit= 70
max_heap_table_size     = 256M
sort_buffer_size        = 128M  
max_tmp_tables          = 64
table_cache             = 2048
read_rnd_buffer_size    = 128M

Records with the field our_works = Y: 37 million
Records with the field our_works = N: 2 million

Why is the index not used in the second query?

Upvotes: 2

Views: 88

Answers (2)

arruah
arruah

Reputation: 37

I found the reason. Search has been performed on the index because of memory over the place reserved for him.

Upvotes: 0

000
000

Reputation: 27247

Low-cardinality indexes are bad news. Cardinality means the number of unique values in the column. If several million rows all have the same value, an index is pretty much useless.

Check this out for a reference: http://www.lullabot.com/blog/article/slow-queries-check-cardinality-your-mysql-indexes

Running EXPLAIN SELECT COUNT( * ) FROM shares WHERE id >36725307 AND our_result = 'Y' will show you how mysql is using (or not using) your indexes.

Upvotes: 1

Related Questions