Top 10 MySQL Best Practices

"Many groups and individuals are involved in the field of data management, from MySQL administrators, architects, developers, as well as infrastructure support people. Each of these plays a part in the security, maintenance, and performance of a MySQL installation. Therefore, when speaking of best practices, one has to consider which of these functions that specific practice pertains to. In this top 10 list, I will try to include a bit from each discipline. I have considered my own experiences as well as consulted with numerous other sources to compile this final list. Whether or not you agree with each and every item and their ordering, the important thing is to at least consider each of the points raised here today. So, without further ado, here is my personal top 10 list.

"1. Index Search Fields

"You should always index columns that you plan on searching on. Creating an index on a field in a table creates another data structure which holds the field value, and pointer to the record it relates to. This index structure is then sorted, allowing binary searches to be performed on it. An index can be defined for a single column or multiple columns of a given table.

"This rule also applies to fields where partial string searches will be performed on the start of the field. For instance the phrase "last_name LIKE 'rob%'" will use the index, whereas "WHERE last_name LIKE '%ert%'" will not."

Complete Story

Related Stories:

• Business Intelligence - SQL or NoSQL - Which is better(Jan 11, 2011)
• Troubleshooting MySQL Slow Queries with Tcpdumps(Dec 22, 2010)
• Oracle Releases MySQL 5.5(Dec 15, 2010)
• 10 PHP Tricks for Associative Array Manipulation(Dec 11, 2010)
• Optimizing MySQL Query Retrieval Speed Through Table Joins(Dec 10, 2010)