Quote:
Originally Posted by 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.
|
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 :
Code:
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 :
[code]
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.
Code:
alter table students add index(id);
Now, lets run explain statment again to see what it says now :
Code:
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------+
| 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.