April 14th, 2007 - by cpradio

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.

Now that I have expressed my thoughts, and note they can be applied to anything (such as programming), lets dive into things to keep in mind when designing a database or table, so your SQL statements will be much more efficient. I will be making assumptions, that you have some knowledge of Databases, or that you followed my first reflection and read a book. If you didn’t, I wouldn’t expect to take much from this.

Normalize
There is nothing more beautiful than a fully normalized database. Do not duplicate data across multiple tables. If you need to link multiple authors to multiple posts/comments, then realize it will take at least three tables, and not just two.

Also separate your large data into a smaller table that is easier to index. Any time you include a large data column, you are causing the database to do much more work every time you query that table.

For example, you can have the following for the above situation:
Authors: id_author int(11), name varchar(20)
AuthorsToPosts: id_author int(11), id_posts int(11)
Posts: id_post int(11), post_date datetime
PostsData: id_post int(11), post_data text

Primary Keys should be (AutoNumber/Auto_Increment) Integers
Every time I state this, someone always asks me “Why?”. These are typically from the people who use a GUID (Globally Unique IDentifier) as the primary key. The reason is simple. Integers are easier to sort and index than a GUID. GUIDs cannot be used to adequately index the table for easy querying with such a key. So do yourself a favor, and use Integers or Numeric types.

Use Stored Procedures (if you can, otherwise cache)
If your Database supports Stored Procedures and you are not using them, you are an IDIOT! Stored Procedures are cache-able SQL statements, and in some databases they are compiled to run faster.

Now if your database does not support Stored Procedures, then you need to cache your results! By caching your results, you can take off some of the load of the database. For example, if you look at the ‘Top 10 Commentators’ on this site. The results are cached every X hours. Why? Because the SQL statement was taking up to 10 seconds to execute at high peak times. Yes, I have worked on the SQL statement to get it down to about 5 seconds at peak times, but even that is too long and it is as efficient as it could get. So I talked with Mark, and we decided on caching the results so it only takes 5 seconds to run every X hours and after that, the results are pulled from the cache which takes less than a second.

So What?
Ask yourself, “So What?” or “What did I get from this?”. Then leave the answer in a comment. By all means, suggest any good books you have read too.

3 Responses to “Optimizing your SQL Statements”

1 Golgotha

“Learn to ask for Help” – perhaps the best advice anyone could ever give.

I get by with a little help from my friends… Ah, John Lennon…

[…] is a popular choice and it sometimes requires some maintenance to keep things running smoothly. Search-this.com has an awesome article about optimizing sql databases. It is a great way to cut back on your sites […]

3 ses5909

Stored procs are great and I’ll have to second the cache statement. A friend turned my onto memcached a while ago and it’s quite efficient at saving time.

http://www.danga.com/memcached/

mulberry sale spyder womens jacket cheap new balance 574 mulberry outlet cheap new balance 574 arcteryx outlet mulberry sale spyder womens jacket mulberry sale spyder womens jacket mulberry outlet mulberry outlet new balance 574

Popular Articles

Top 10 Commentators


Subscribe to this feed! Subscribe by Email!

Random Bits Podcast

You need to download the Flash player from Adobe

Blogs Worth Reading