MySql performance issues in queries

Question

I'm a newbie using MySql. I'm reviewing a table that has around 200,000 records. When I execute a simple:

 SELECT * FROM X WHERE Serial=123

it takes a long time, around 15-30 secs in return a response (with 200,000 rows) .

Before adding an index it takes around 50 seconds (with 7 million) to return a simple select where statement. This table increases its rows every day. Right now it has 7 million rows. I added an index in the following way:

 ALTER TABLE `X` ADD INDEX `index_name` (`serial`)

Now it takes 109 seconds to return a response.

Which initial approaches should I apply to this table to improve the performance?
Is MySql the correct tool to handle big tables that will have around 5-10 million of records? or should I move to another tool?

please post the result from EXPLAIN SELECT * FROM X WHERE Id='123' — Bernd Buffen
– Bernd Buffen, Commented Oct 29, 2015 at 20:38
maybe it is doing a cast. How about just id=123. Show create table x could clear up our assumptions — Drew
– Drew, Commented Oct 29, 2015 at 20:50
Thank you for your comments.I edited the question with more details. — Alberto Montellano
– Alberto Montellano, Commented Oct 30, 2015 at 16:35

Rick James · Accepted Answer · 2015-11-01 22:08:40Z

1

Assuming serial is some kind of numeric datatype...

You do ADD INDEX only once. Normally, you would have foreseen the need for the index and add it very cheaply when you created the table.

Now that you have the index on serial, that select, with any value other than 123, will run very fast.

If there is only one row with serial = 123, the indexed table will spit out the row in milliseconds whether it has 7 million rows or 7 billion.

If serial = 123 shows up in 1% of the table, then finding all 70M rows (out of 7B) will take much longer than finding all 70K rows (out of 7M).

Indexes are your friends!

If serial is a VARCHAR, then...

Plan A: Change serial to be a numeric type (if appropriate), or

Plan B: Put quotes around 123 so that you are comparing strings to strings!

answered Nov 1, 2015 at 22:08

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql performance issues in queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related