Are your queries optimized?
December 19th, 2006 by Kyle
We've all had problems with slow queries, which can lead to sluggish applications and dissatisfied users. You've looked at your code and just can't figure out what's causing the problem. Have you looked at the effectiveness of your MySQL queries? Here's an easy way to analyze your queries to make sure they are being executed efficiently.
Here's a very simplistic example. Let's start out by creating a table of NFL teams.
-
CREATE TABLE `nflteams` (
-
`nflteam_id` SMALLINT(2) UNSIGNED NOT NULL DEFAULT '0',
-
`nflteam_city` VARCHAR(50) DEFAULT NULL,
-
`nflteam_name` VARCHAR(50) DEFAULT NULL,
-
`nflteam_abbv` VARCHAR(3) DEFAULT NULL,
-
`nflteam_icon` VARCHAR(20) NOT NULL DEFAULT ''
-
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
So a sample record would look like this:
-
INSERT INTO `nflteams` VALUES (14, 'Pittsburgh', 'Steelers', 'PIT', 'pit.gif');
We're going to start out with a simple query. Let's try:
-
SELECT nflteam_name FROM nflteams
-
WHERE nflteam_id = '14';
Our results:
Simple enough, right? Now the question is...is this query as efficient as it could be?
MySQL's EXPLAIN syntax shows how queries are being executed. Is the query correctly using indexes...is it utilizing indexes at all?
Let's try running EXPLAIN on the previous query:
-
EXPLAIN SELECT nflteam_name FROM nflteams
-
WHERE nflteam_id = '14';
The result of the EXPLAIN query is below:
So, what does this tell us?
Posted in MySQL | No Comments »
