How to optimise your MySQL queries for better performance

MySQL’s EXPLAIN explained

Optimizing SQL queries is often viewed as a darkly mysterious art form, about as fun as filing an income tax return or undergoing root canal surgery. But careful crafting of statements should be a matter of pride for anyone using databases and can offer big benefits to a careful programmer. How?

The worst case scenario in the database world is reading the entire table to find an answer. It’s known as a full table scan. Imagine having to read the entire dictionary from A to Z just to look up the plural of moose. This is a slow process, and if the data is on disk instead of memory it can be slowed by a factor of 100,000! If you were able to do a back flip a second and started now, you would be finished in just under seventy days.

Imagine that your carefully crafted PHP project uses an SQL query that is executed a thousand times a day and that query takes five seconds to run. What if you could run that same query in one second? Would you and/or your users be happier? Would your boss be happier? Would you be able scale to serve more users with the same hardware?

The two points to remember with MySQL or any other database are (a) read just what you need for speed and (b) minimise disk input and output whenever possible.

Files to Download

The examples that follow all use the Community (free!) edition of MySQL and the ‘World’ Database. The World database is the MySQL DBA’s equivalent of a ‘Hello World’ program – it has been used extensively in examples, books, articles and blogs as it is just complex enough to be used to demonstrate some advanced functionality but simple enough for quick demos.

Start by downloading a copy of MySQL and a copy of the World database, preferably the InnoDB version. The World database is an old standard in the MySQL world and used for many examples, the MySQL 5.0 Certification Guide, and large segments of the MySQL Documentation. And if you happen to scramble a column, drop a table, or some other mistake erupts, you can always start over again.


Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

- Gib Deinen Standort ein -
- or -