Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL : Selecting values from a specific column is very slow

I have a mysql database called key_word. It has 3 columns, 23 million rows.

Please have a look at the below query.

SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'

This query takes around 6 seconds to display me the result. Result contains 169669 rows. This is very very slow. However, I have noticed that this occurs "only" if I trying to get records in indexVal. All the below queries are very fast.

SELECT `primary_key` FROM `key_word` WHERE `hashed_word`='001'
SELECT `hashed_word` FROM `key_word` WHERE `indexVal`=0

All these 3 columns are indexed separatly, so I don't understand why it is slow when I am trying to get data from indexVal by searching hashed_word

Below is my Table structure.

enter image description here

Below is some more information about the table

enter image description here

So, why this selecting from indexVal while searching from hashed_word is slow? How can I solve this?

UPDATE

As requested, I am posting the result of

EXPLAIN SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'

below

enter image description here

UPDATE

The result of SHOW VARIABLES LIKE '%query_cache_size%'; SHOW VARIABLES LIKE '%query_cache_type%'; is below

enter image description here

like image 301
PeakGen Avatar asked Apr 26 '14 11:04

PeakGen


1 Answers

SELECT primary_key FROM key_word WHERE hashed_word='001' is faster than SELECT indexVal FROM key_word WHERE hashed_word='001' because in InnoDB the primary key value is always included in any secondary index; this means that primary_key is read from the index. In the second query however, MySQL first reads the primary key from the index, than it reads the value of indexVal from the table row. In other words it persoms 2x disk reads.

like image 96
Maxim Krizhanovsky Avatar answered Sep 23 '22 02:09

Maxim Krizhanovsky