Select query takes more time than it should

Question

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?

Answer 1

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 :-)

Answer 2

1. Check the mysql innodb_buffer_pool_size. It should be big enough - the more, the better. But not to much to avoid OS swapping.

   show variables like 'innodb_buffer_pool_size'
   

   will show the buffer size in bytes.

2. Check the query more than once. The first run may be too long since the data should be read from the disk into the memory. When you're running query first time the data is still not in innodb buffer and has to be read from the disk. Which is much slower than if the data would be in cache already. So run the query a couple of times to make sure it is served from cache.

3. Disable the query cache since each consequent run will be fulfilled from it and will bias the results of the test. There is a mechanism in MySQL, called a "query cache" which is designed to store queries along with their results. So the second time MySQL is requested to run the query, it can bypass the execution and retrieve the results from the query cache.

4. Consider using a "covering index":

   ALTER TABLE key_word ADD KEY IX_hashed_word_indexVal (hashed_word, indexVal);
   

This would be much more efficient, since then MySQL can fulfil the query request from the index alone.