resources

A lesson in MySql indexing

Optmyzr May 21, 2013

Manas Garg

So we have this table that has over 20 million rows of data, storing stats. The general structure of the table is like this:

ItemId (varchar) | GroupID (varchar) | Stat1 (float) | Stat2 (float) | … StatN

There is an index on ItemId + GroupId. When issuing queries, I observed that they would take very very long – upto 100 seconds. The queries were simple and looked like this:

SELECT * FROM TABLE WHERE ItemId=123456789 AND GroupId=987654321

After banging my head with different partitioning, query styles, I had almost decided that I need to upgrade the server (it was a micro instance on Amazon EC2 after all, and the CPU used to spike when the query was issued).

Then it struck me. I changed the query to this, and the query times came down to 0.2 seconds!

SELECT * FROM TABLE WHERE ItemId=’123456789’ AND GroupId=’987654321’

Got it? The columns are varchar, but the parameters I was supplying were ints, and that would cause a manual type casting of each value (over 40 million values), and then non-indexed search. No wonder it was so slow.

One of those days 🙂 Though I think the query optmyzr should probably try to typecast the parameter instead of the table columns.


Author

Manas Garg

Take control of PPC and save time with our PPC expert's toolkit.

You Might Also Like...