One of the basic nightmares of a web developer is optimizing queries. Every now and then, I keep seeing articles which advise lesser queries per web page, normalised tables, indexes on the most queried columns and a diligent logic.
Ofcourse, this is what is needed. But at the same time, not everyone is exposed to better optimization techniques. If you are one of those, who have written a perfect SQL but still wondering why it takes that longer for it to get executed, read on.
Are you using the right technique :
This is the most important aspect of any programming/optimizing technique and often the most overlooked aspect. My friend uses PHP just for printing data in loops. Almost all his logic gets done in the backend. Formatting data, executional statements etc. While am not saying that Mysql can’t format data or include IF clauses, one also needs to consider what is best for what while coding.
It is always better to do all formatting stuff in the front end and use Mysql for fetching data.
Joins Vs Subqueries:
If your complex query includes a subquery, check whether it could be optimized by using a join. Joins are fast, and give better performance.
Yes, this is a must for any RDBMS. Whenever you are searching for some topic in your book, didn’t you always refer to the index? That gave you quicker access to the topic. Ain’t it?
Similarly, by indexing the database columns you gain speed. Always index the columns that are used more frequently in your where clauses and in your select clause.
Redundancy Vs Normalisation:
There is so much debate on this topic and both are good if used properly. Normalising your content is good. But at the same time, it is also good to hold summarized data in summary tables. Particularly, if your project does more querying than inserting. That would speed up your report queries.
Once in a while, use OPTIMIZE table command to optimize your table.
Last but not the least, if you come across better optimization techniques, do let me know 🙂