Top 10 MySQL Best Practices
Jan 12, 2011, 21:03 (0 Talkback[s])
(Other stories by Rob Gravelle)
"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."