Four Ways to Optimize Your MySQL Database, Page 2
Once the tables have been properly designed and normalized, you should next take some time to think about what data will most commonly be queried, and create special data structures known as indexes which will dramatically improve the performance of these query operations. Indexes are important because they organize the indexed data in a way that allows MySQL to retrieve the desired record in the fastest possible fashion. In the previous section you already encountered an index, although it isn't apparent: the primary key. But the primary key will only speed query operations when you're searching for a record by using that primary key as the identifier, for example:
SELECT name, telephone FROM employees WHERE id='3'What if you wanted to search for one or several records based on the employee name? The query might look like this:
SELECT id FROM employees WHERE name='John Smith'To speed these sorts of queries, you'll need to add an index to the name column. The revised employees table looks like this:
CREATE TABLE employees ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, INDEX (name), telephone CHAR(10) NOT NULL, position_fk TINYINT UNSIGNED NOT NULL, salary DECIMAL(8,2) NOT NULL, supervisor_pk SMALLINT UNSIGNED )You can also create multiple-column indexes that would greatly improve the performance of queries such as this:
SELECT id FROM employees WHERE firstname = 'John' and lastname = 'Smith'The revised employees table would look like this (I've also broken the name into two separate columns as indicated by the query):
CREATE TABLE employees ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(20) NOT NULL, lastname VARCHAR(20) NOT NULL, INDEX name (firstname, lastname), telephone CHAR(10) NOT NULL, position_fk TINYINT UNSIGNED NOT NULL, salary DECIMAL(8,2) NOT NULL, supervisor_pk SMALLINT UNSIGNED )This new index would actually improve quite a few queries, including the following:
SELECT telephone, position FROM employees WHERE firstname = 'John' SELECT * FROM employees WHERE firstname = 'John' and lastname = 'Smith'
Table indexing can be a rather confusing topic, although the time invested in learning its nuances will be well worth it. I suggest reading the following MySQL manual pages for more information:
MySQL is often used to dynamically generate web pages based on similar queries and rarely changing data. For instance, consider the home page of Developer.com, which likely depends on a single query used to repeatedly retrieve the latest ten or so articles. New articles are typically published early to mid-morning, meaning the relevant data found on this page really only chances perhaps once or twice a day. Why not cache the data returned by these queries, thereby bypassing the need to repeatedly retrieve it from the database? Introduced in version 4, enabling MySQL's query caching mechanism can result in a huge performance gain over neglecting to do so.
To learn more about how your MySQL installation's query caching mechanism is configured, run the following command:
mysql>SHOW VARIABLES LIKE '%query_cache%'
The reasoning behind this step should be obvious: how are you going to know what should be optimized if you're not actively monitoring how MySQL is operating? Thankfully, MySQL's developers have been particularly proactive in providing developers with the tools for keeping abreast of database performance.
For starters, you should familiarize yourself with MySQL's EXPLAIN query, which will provide detailed information regarding how MySQL goes about executing a SELECT query. You'll learn valuable tips regarding how MySQL is executing table joins, and whether additional indexes should be added.
If you're running a MySQL version earlier than 5.0, check out mytop, a console-based utility for monitoring MySQL performance.
Finally, be sure to have a solid understanding of MySQL's configuration variables, because they control crucial performance-related matters such as how much memory is allocated to queries, what sort of data is logged, how many simultaneous collections are allowed, and much more. You can review a complete list of these variables by executing:
I hope this introductory article left you thinking about how you can go about making your MySQL database absolutely scream. And moving forward, every time you begin a new database project, don't forget to approach it the NICM way!
About the Author
W. Jason Gilmore is Apress' Open Source Editorial Director, and co-founder of IT Enlightenment. He's the author of several books, including the best-selling "Beginning PHP and MySQL 5: Novice to Professional, Second Edition" (Apress, 2006. 913pp.). Jason loves receiving e-mail; so don't hesitate to write him at wjATwjgilmore.com.