Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Optimize your MySQL database usage

Friday, December 9th, 2011 by Servage

It’s important for your website performance that you use the database carefully. Inefficient queries can place extreme load on the server, and make your website seem unresponsive or slow. In fact most database server related problems are caused by poorly created queries, that do things more complicated than necessary. Therefore I suggest you take a look at these tips for improvements:

Carefully consider how to design your database. When using an MVC programming pattern, object models are usually a good starting point for the table structure of your database. Remember to use proper keys and indexing for performance. Have a look at this article for further tips.

Only select what your need. Beginners tutorials for database programming often include a select query that fetches all data. However, it saves resources to select only the necessary fields:

SELECT * FROM my_table; // BAD !!!
SELECT field1, field2, field3 FROM my_table; // GOOD :-)

Remember to use limit, and let the database sort results for you. It’s unnecessary to load all entries, if you’re for example showing a view, where entries are divided into multiple pages, or similar. Also, having the database sorting entries for you is better than doing so via a script later on. Example:

SELECT field1, field2, field3 FROM my_table ORDER BY field1 DESC LIMIT 10;

Advanced: Avoid using wildcards where unnecessary, and use joins instead of subqueries. This topic is for advanced queries, where you may search more broadly in the database, rather than just selecting by ID. Joins are for fetching related data from multiple tables with one query.

Read more about optimization

Optimize your MySQL database usage, 3.1 out of 5 based on 9 ratings
Categories: Tips & Tricks

Keywords:

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.