I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure
Below is some of its data.
Now, I ran a simple query
SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'
Unfortunately, this took more than 5 seconds to retrieve the data and show them to me. My future table will have 150 billion records, so this time is very very high.
I ran the Explain
command to see what's going on. Result is below.
Then I ran the Profile using below command.
SET profiling=1;
SELECT `indexVal` FROM `key_word` WHERE `hashed_word` = '001';
SHOW profile;
Below is the result of the profiling
Below is some more information about my table.
So, why this is taking too long? They are indexed too! In future, I have to run lot of LIKE
commands, so this is taking too much time. What has went wrong?