Web Design & Marketing Blog

Speed Matters - Why You Need To Optimize Your SQL & How To Get Started

Category: Blog

Written by Matthew Millet

Few people think about their website once it's up and running, but a good website will always have to be worked on, and a good website shows. Speed is a huge factor in website conversions and revisits; the two holy grails every website strives for. Speed on the web is measured in fractions of a second. You'll set yourself apart from your competitors if you save them a half second delay, and getting started isn't as intimidating as you may think.

The biggest speed bump your web site is going to have to get over is talking to the database. Because of this, the first thing I would strongly recommend that any developer do is hone his SQL skills.

Query Optimization

The greatest improvements in load times are almost always made by writing better SQL. Writing good queries is a sure way to make your employer fall in love you. I can't count the times I've blown an employer or client away with just a little extra effort in my SQL. I had a client swear I was 10 times the genius of his original website developer when I increased the speed of one of his frequently visited pages four times simply by adding 3 words to a query. At one job I rewrote the query that made a report on their home page to run over 60 times faster, and before I was finished with my first week they decided to keep me forever.

These things aren't as hard as some newer SQL coders seem to fear when they hear the ominous words “optimization” and “refactor.” It's true that the smallest little detail can make a world of difference in SQL, and this can be a bit daunting at first, but if you test little parts of your queries as you go you'll catch these little worlds of difference right when they happen.

Also, try to remember these two simple tips that will get you through most of the issues you'll run into:

1. Remember Your Indexes

The most important thing to remember is to try as hard as you can to make as many clauses in your where statements indexed as possible. A thousand records returned on a query testing an indexed fields in a database with 100,000 records can take seconds, a thousand records returned on a query not testing any indexed fields in the same database can take two to three minutes. If you're searching for a record and indexed fields can limit your possible or final results to very few results or a single result than in a typical environment you can find those records amongst tens of thousands in less than a second. If you aren't testing against any indexed fields the processing time of your queries will endlessly skyrocket as the tables you're searching through increase in size.

Something about bigger queries gives the human mind the impression that it is more complicated and will take longer, but this isn't true with SQL. Imagine a library database. You could probably find all of the books you need in a query against a table of book subjects and a table of book titles by searching for specific titles, but if the titles aren't indexed, which they shouldn't be, you may want to add where clauses before the title for subjects, print dates, publishers, or anything and everything else you could possibly think of that could limit the possible number of records.

But remember, when you are adding criteria to your search queries to

2. Never Get a Field More Than Once

That report I bragged about increasing in speed 60 times earlier wasn't a feat of mad SQL 1337 on my part, but just a little bit of hindsight where there was obviously no forethought. All of the data in the report was more obvious to retrieve by writing 20 separate queries, and this didn't matter when there were a thousand invoices to report on. Fast forward three years and countless developers, those 20 queries were going over the exact same 100,000 invoices and 80,000 customer accounts, well, 20 times. I don't blame the original developer. I know how it is and I may have done the same thing. But with a little forethought he could have written one single query, as I later did, and then just let his server side code, PHP in this case, figure out what invoices went where.

Remember: your server side code is often going to be faster than the database. If your database is taking a long time figuring out which 5 records you want out of 10, let your server side code handle it. The best tip I can give you when it comes to deciding when to do this is to try it both ways if you have a clause in your query that is taking a long time and you're struggling to get it done quicker in SQL alone.

When In Doubt, Plagiarize

If this is all a bit new or overwhelming then look at the database schema and queries in some free website frameworks and make your schema and queries look like theirs. Seriously, hacking around with someone else's code is how most developers learn some of their best tricks. Or just trust your hosting and database solutions to a service like Lexy so you can worry about more important things. As a web developer is expected to be an expert on more and more technologies, there's nothing amateur about leaving aspects of it to other professionals.

Further Reading

A lot of what I'm saying may bring up some good questions. Like when to index a field, what database engine to use, what fields to make foreign key constraints between. There's a lot more to know when trying to make a web site give the visitor what they want, when the want it; which is everything, and right now. The most important part is through: write good queries. If you're code is running too slow for your boss or your visitors, regardless of how unrealistic their expectations are always going to be, if there's an easy solution it's almost always in the advice I've given you above. If not, I'd start by reading the manual to your database engine be it MySQL or Microsoft SQL.