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 professional 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

2 Answers 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.

  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

 
discard

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.