Lately, I have seen a huge hype on Optimizing MySQL Statements, or on a more generalized approach of just optimizing your SQL. I want to reflect on this a bit and tell you that 10 tips will never solve your problem. 10 tips will not even come close to making a difference. 10 tips are not going to help you.
With that said, here are my reflections:
Reflection #1: Buy a Book
Why? It is purely simple, I can give you the best tips in the world and it won’t save your database from crawling if you do not fully understand why I gave the tips to begin with. Nor will it save you if the true cause isn’t entirely your SQL, but your schema as well. Sure there are things to keep in mind, but do us all a favor and buy a book, read it cover to cover, and fully indulge yourself in to how Databases work, especially the one you use for your site.
Reflection #2: Pick the Database for the Job
A professor once told me that there isn’t a single language that can do everything efficiently. Pick the language for the job, do not fit the job into a language. The same can be said about Databases. Pick the right Database for the job, do not try and fit your job into a specific Database just because you like it more.
Microsoft SQL, Oracle, Postgress, and MySQL (MyISAM or INNODB) all have their place. Do the research, figure out under which circumstance each work best, and then pick the one that is suitable for your task. Keep in mind cost and licensing can definitely be deciding factors. If you do not want to shell out the money for Microsoft SQL, then by all means, rule it out and research the others to see what will be more suitable. However, if you want to succeed, cost and licensing should not be major factors in your decision.
Reflection #3: Learn to ask for Help
The major issue with database and SQL design is primarily due to the inexperience of the user. If you are trying to design a database or write a complex SQL statement, by all means ask for help. I can’t tell you how many times I have rewritten the Stored Procedures at my work place because a newer developer wrote them.
There are countless places you can get help, countless forums, and chances are you may think you are designing something entirely revolutionary that has never been done. However, I am willing to bet it has been done and the user who can tell you his pitfalls and experience with doing it, is likely to be moderating a forum. Allow their experience to steer you clear from the same issues. No need to reinvent the wheel.
(more…)