Tip : Optimizing mysql select queries

[dman_2007]

You can learn a lot of about how mysql is going to execute a select query by using Explain select statement. You can use the information thus obtained to see what you can do to help mysql run this query faster. An Explain select statement returns information in the following fields :

1) id : Gives the actual numeric position of the sselect within the complete query statement

2) select_type : Displays the type of select query.

3) table : name of the table from which the rows are being retrived

4) type : the type of join which will be performed

5) possible_keys : Indexes available which can be used to select rows from the table

6) key : Display index which will be actually used find rows.

7) key_len : length of the key select for use by mysql

8) ref : Displays the constant or column against which the selected key will be compared with to retrieve rows

9) rows : Total numbers of rows which will be examined by mysql to successfully execute this query

10) extra : This column contains extra information regarding the query execution plan

In my next post, i'll discuss some important fields among these.


6)

 

[smeagain]

I like the advice, but for the PHP MYSQL struglers, it would be a good idea to show a select query and then show how to include the Explain instruction.

 

[dman_2007]

I like the advice, but for the PHP MYSQL struglers, it would be a good idea to show a select query and then show how to include the Explain instruction.

You're right smeagain, examples will help a lot of users and the good thing is that i am going to show some examples.

Here's the SQL to create table and enter some sample data :

create table students
(
  id          int,
  name     varchar(60),
  address varchar(240)
);

insert into students values (1, 'john', 'jsd djs dkh'), (2, 'scott', 'kfdh fkhsd fks'), (3, 'nathan', 'fkhs kfjsf kfjsd');

The query for selecting the record of student with student id 2 is :

Select * from students where id = 2
[CODE]

Lets run explain on this select to see how we can optimize it, the output is :

[CODE]
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

select_type field shows that it is a simple_select and mysql doesn't have any index available to find record for this query indicated by possible_keys field. Hence, it is scanning each row in the source table to find out the desired records indicated by the rows field. Since this table contains only 3 records, this query is pretty fast even without
using index, now imagine the time it would take if you had a table with million records.

Since we are filtering records based on the value of id field, lets add an index on id field.

alter table students add index(id);

Now, lets run explain statment again to see what it says now :

+----+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | students | ref  | id            | id   | 5       | const |    1 | Using where |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------+

As you can see now, mysql is using an index to find out the record and only 1 row is being scanned, even if we add more records to the table.

It might not seem very useful for optimization for simple queries, but explain statement can be very handy for optimizing complex queries with multiple table joins.