Select query takes more time than it should

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 so long? They are indexed too! In future, I have to run lot of LIKE commands, so this is taking too much time. What has gone wrong?

You asked "why this is taking too long?". You also said "Unfortunately, this took more than 5 seconds to retrieve the data and show them to me". Also, you reported the profiling output of your query.

As you can see yourself, the sum of times reported by the profiler for each steps count to 0.000154 seconds. So, from the point of view of the profiler, the query was completed in such a time (0.000154).

So why you're getting results in "...more than 5 seconds?".

You said you are filtering a 23 millions record table with a 3 char field. Unfortunately you don't tell us how many records your query is returning... but thanks to the provided EXPLAIN SELECT, it seems your query returned 336052 records.

It seems, also, that all of your activity run through some GUI (PHPMyAdmin?).

So, after all of the above, we can reformulate your original question as:

"why am I getting, within my GUI, 336.052 records shown in more than 5 seconds, if MySQL execution time for the related query is 0.000154 seconds?"

The answer, in my opinion, is quite simple: 5 seconds is the (really low, indeed) time to let 336.052 records travels along the path: MySQL engine => MySQL client libraries => PHP MySQL module => Apache => Network => your PC TCP/IP stack => Browser => DOM parser/builder/etc. => Rendered HTML page.

As for my previous experience, time required by the transmission of results is "normally" much higher than the time needed to retrieve such data. This is expecially true when libraries like PHP-MySQL or Perl-DBD-MySQL are involved: they really require lot of time to retrieve the records, after MySQL has properly identified (...and extracted) all of them.

How to solve this problem?

Again, quite easily: are you really sure that you need ALL of the 336.052 record, in a single, whole, dataset?

-
If your answer is really "YES! I need the all of them", than your application will handle PAGINATION and/or USER-Interaction by itself and... once it has gathered all of such data, it will probably spend lots of time interacting with the user without requiring any further MySQL interaction. In a such a case, waiting for 5 seconds (or even more) should not be a problem;

-
If your answer is "NO, I want to deal with a more 'human' dataset size", than you have to refine your query (at least) so that it will give back to you a more "human" dataset (tens or, hundreds, at most, records). In such a case, I bet that you'll get your result in a shorter time.

BTW: this is exactly the same issue you experienced in this other post, at ServerFault: 88 seconds to let 132M records travels along the.... not-mysql-strictly-related magic path :-)

StackExchange Database Administrators Q#63971, answer score: 11