Skip to main content
edited tags
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

`Select` Select query taketakes 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:

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

`Select` query take 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

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?

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:

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

Tweeted twitter.com/#!/StackDBAs/status/460019287038885888
Post Migrated Here from serverfault.com (revisions)
Source Link

`Select` query take 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

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?