Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

enter image description here

Below is some of its data.

enter image description here

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.

enter image description here

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

enter image description here

Below is some more information about my table.

enter image description here

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?

share|improve this question

migrated from serverfault.com Apr 26 at 6:26

This question came from our site for system and network administrators.

Which storage engine are you using? InnoDB? –  Rüdiger Voigt Apr 26 at 9:36
@RüdigerVoigt: Thanks for the reply. Yes. –  GloryOfSuccess Apr 26 at 9:45
"This table has no primary key, because nothing is unique." Yeah, right... Time to re-examine your design. All tables should have a primary (or a unique) key. –  ypercube Aug 2 at 17:29

3 Answers 3

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

share|improve this answer
  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.

  1. check the query more then once. The first run may be too long since the data should be read from the disk into the memory.
  2. disable the query cache since each consequent run will be fulfilled from it and will bias the results of the test.

  3. consider using "covering index":

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

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

share|improve this answer
Hi, Thanks for the reply. I didnt understand point 1 and 2. Can you explain please? –  GloryOfSuccess Apr 27 at 14:07
1. "check query more then once" - When you're running query first time the data is still not in innodb buffer and has to be red from the disk. Which is much slower then if the data would be in cache already. So run the query cpl of times to make sure its served from cache. 2. "disable the query cache" - There is a mechanism in mysql, called a "query cache" which designed to store queries along with their results. So the second time mysql requested to run the query, it can bypass the execution and retrieve the results from the query cache. –  Boris May 2 at 7:56

In your data sample hashed_word always contains 3 characters. If this is true for most of your data in this and your future table you should switch from VARCHAR to CHAR (reduces some overhead and allows MySQL to use other algorithms).

You should check MySQL's settings. For example the standard value for innodb_buffer_pool_size is set to 8 MB which is not enough for a table of this size. Finding the right value may need some experimentation. The innodb_log_buffer_size should be increased too - standard is 1 MB try 8.

For your future database you should consider using SSDs instead of normal harddrives. Be sure that you have enough RAM.

The MySQL manual has a very helpful chapter on InnoDB performance (here for version 5.7)

share|improve this answer
Thank you for the reply. My test table was like this, contains 150 million rows, and it is very very fast! I can't understand! –  GloryOfSuccess Apr 26 at 11:13
How can I check these settings? –  GloryOfSuccess Apr 26 at 11:40
@GloryOfSuccess SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; same for the other one. –  Mihai Apr 26 at 14:08
Both settings are in your configuration file. In Debian you find this file at /etc/mysql/my.cnf . (Other Linux distributions: use the locate command (maybe run updatedb before) / Windows: use search to find it) After you changed settings do not forget to restart mysql as it needs to read the new settings. –  Rüdiger Voigt Apr 27 at 0:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.