October 6, 2008

MySQL Select Optimization line

Weather used in php or any other scripting language… sometimes we have the need to query a database in order to fetch or manipulate certain data.

One of the most common request, widely written and used is

SELECT * FROM MyTable WHERE ID='ValueX' and NAME='ValueY';

now, ID and NAME are just columns… so nothing special

However.. how could someone optimize that line of code? Fact is, lots of coders out there, do not realize how unoptimize this line could be and the different problems it could generate.

So how do I optimize this line… although not much syntax will be needed to change

  1. Put INDEXES key on both ID and NAME (or whatever are your column)…
  2. Find which combination will return less values and use that combination as the first condition…

That’s it :-)