Articles

Slow Database queries

Details

Published on Thursday, 28 July 2011 12:03

There are countless reasons why you might get a slow query on your database.

1. You could be trying to retrieve all the data at once - do you really need all the data? You could reduce the select columns

2. You are joining large tables or several tables at once. Sometimes it can better to avoid joining tables and instead use multiple selects - this can make huge performance gains especially in MySQL.

3. No indexing. Setup some indexes and compare, don't add indexes that are not required.

4. You are running the query more often than is required. You could cache the query or simply save it at run time (I optimised a query years ago which ran twice, the first time it ran was to display the results in a list and the second time was to generate a calander on the same page using the same data).

5. Your queries are not being used efficiently. In some cases you might be trying to do too much at once, for instance it might be necessary to introduce pagination on the database side rather than retrieving all the results and then paginating using javascript.

There are loads more reasons and if your someone like Facebook you have no option but to create custom MySQL databases. This article makes interesting reading.