Entry tags:

mysql

Montreal on Rails
I spoke at Montreal on Rails on Tuesday night. I think I had 5 slides, but spoke for about 45 minutes (so there's no point in uploading them). For those that missed it (or couldn't take notes fast enough), here's a transcript of the examples I showed with the world database:

# take a look at this query.  To start with, we have no indexes used:
EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 5000000 ORDER BY Name;

# First let's look at an index on population
ALTER TABLE Country ADD INDEX p (Population);

# is that index effective?
EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 5000000 ORDER BY Name;

# no it wasn't.  what happens if we modify the query just slightly:
EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 50000000 ORDER BY Name;

# time for the next index:
ALTER TABLE Country ADD INDEX c (Continent);

# with two indexes on the table, which one will the optimizer prefer?
EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 50000000 ORDER BY Name;

# how about now?
EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 500000000 ORDER BY Name;

# This index is not always helpful.  Why?
ALTER TABLE Country ADD INDEX p_c (Population, Continent);

# How about this one?
ALTER TABLE Country ADD INDEX c_p (Continent,Population);

# Why is this one better than just c_p?
ALTER TABLE Country ADD INDEX c_p_n (Continent,Population,Name);

# Remote all the indexes before trying to add an index on n.
ALTER TABLE Country DROP INDEX p, DROP INDEX c, DROP INDEX p_c, DROP INDEX c_p, DROP INDEX c_p_n;
ALTER TABLE Country ADD INDEX n (Name);

# the optimizer still doesn't consider N.
EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 500000000 ORDER BY Name;

# how about now?
EXPLAIN SELECT Name FROM Country FORCE INDEX (n) WHERE Continent = 'Asia' AND population > 500000000 ORDER BY Name;

# drop the index on N.
alter table Country drop index n;


# SOME trick questions
# which is better.

EXPLAIN SELECT * FROM City WHERE id = 1810;
EXPLAIN SELECT * FROM City WHERE id = 1810 LIMIT 1;

# How about this one.

EXPLAIN SELECT * FROM City WHERE id BETWEEN 100 and 200;
EXPLAIN SELECT * FROM City WHERE id >= 100 and id <= 200;

# (the answer is that both of the two above are identical - 
#  they are rewritten internally to the same thing)

# This is a bad subquery.
EXPLAIN SELECT * FROM City WHERE countrycode IN (SELECT code FROM country WHERE name='Australia')

# this is the rewrite as a join.
EXPLAIN SELECT city.* FROM City, Country WHERE city.countrycode=country.code AND country.name='Australia'

# does this index help?
ALTER TABLE City ADD INDEX (countrycode);

# retry
EXPLAIN SELECT city.* FROM City, Country WHERE city.countrycode=country.code AND country.name='Australia';

# add an index on city.
ALTER TABLE Country ADD INDEX (name);

# how about a retry
EXPLAIN SELECT city.* FROM City, Country WHERE city.countrycode=country.code AND country.name='Australia';

Pretty neat, huh? I teach something similar in DBA classes. I have to thank Tobias for first showing me a fair chunk of this example.

Update Transcript of output:

mysql> EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 5000000 ORDER BY Name;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Country | ALL  | NULL          | NULL | NULL    | NULL |  239 | Using where; Using filesort | 
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE Country ADD INDEX p (Population);
Query OK, 239 rows affected (0.01 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 5000000 ORDER BY Name;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Country | ALL  | p             | NULL | NULL    | NULL |  239 | Using where; Using filesort | 
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 50000000 ORDER BY Name;
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Country | range | p             | p    | 4       | NULL |   54 | Using where; Using filesort | 
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE Country ADD INDEX c (Continent);
Query OK, 239 rows affected (0.00 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 50000000 ORDER BY Name;
+----+-------------+---------+------+---------------+------+---------+-------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | Country | ref  | p,c           | c    | 1       | const |   42 | Using where; Using filesort | 
+----+-------------+---------+------+---------------+------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 500000000 ORDER BY Name;
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Country | range | p,c           | p    | 4       | NULL |    4 | Using where; Using filesort | 
+----+-------------+---------+-------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE Country ADD INDEX p_c (Population, Continent);
Query OK, 239 rows affected (0.00 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Country ADD INDEX c_p (Continent,Population);
Query OK, 239 rows affected (0.00 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Country ADD INDEX c_p_n (Continent,Population,Name);
Query OK, 239 rows affected (0.01 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Country DROP INDEX p, DROP INDEX c, DROP INDEX p_c, DROP INDEX c_p, DROP INDEX c_p_n;
Query OK, 239 rows affected (0.00 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE Country ADD INDEX n (Name);
Query OK, 239 rows affected (0.01 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT Name FROM Country WHERE Continent = 'Asia' AND population > 500000000 ORDER BY Name;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Country | ALL  | NULL          | NULL | NULL    | NULL |  239 | Using where; Using filesort | 
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM Country FORCE INDEX (n) WHERE Continent = 'Asia' AND population > 500000000 ORDER BY Name;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Country | index | NULL          | n    | 52      | NULL |  239 | Using where | 
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table Country drop index n;
Query OK, 239 rows affected (0.01 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM City WHERE id = 1810;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | City  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM City WHERE id = 1810 LIMIT 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | City  | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM City WHERE id BETWEEN 100 and 200;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | City  | range | PRIMARY       | PRIMARY | 4       | NULL |  101 | Using where | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM City WHERE id >= 100 and id <= 200;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | City  | range | PRIMARY       | PRIMARY | 4       | NULL |  101 | Using where | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM City WHERE countrycode IN (SELECT code FROM country WHERE name='Australia');
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table   | type            | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | City    | ALL             | NULL          | NULL    | NULL    | NULL | 4079 | Using where | 
|  2 | DEPENDENT SUBQUERY | country | unique_subquery | PRIMARY       | PRIMARY | 3       | func |    1 | Using where | 
+----+--------------------+---------+-----------------+---------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT city.* FROM City, Country WHERE city.countrycode=country.code AND country.name='Australia';
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | City    | ALL    | NULL          | NULL    | NULL    | NULL                   | 4079 |             | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 | Using where | 
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE City ADD INDEX (countrycode);
Query OK, 4079 rows affected (0.03 sec)
Records: 4079  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT city.* FROM City, Country WHERE city.countrycode=country.code AND country.name='Australia';
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
|  1 | SIMPLE      | City    | ALL    | CountryCode   | NULL    | NULL    | NULL                   | 4079 |             | 
|  1 | SIMPLE      | Country | eq_ref | PRIMARY       | PRIMARY | 3       | world.City.CountryCode |    1 | Using where | 
+----+-------------+---------+--------+---------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE Country ADD INDEX (name);
Query OK, 239 rows affected (0.00 sec)
Records: 239  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT city.* FROM City, Country WHERE city.countrycode=country.code AND country.name='Australia';
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref                | rows | Extra       |
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------------+
|  1 | SIMPLE      | Country | ref  | PRIMARY,Name  | Name        | 52      | const              |    1 | Using where | 
|  1 | SIMPLE      | City    | ref  | CountryCode   | CountryCode | 3       | world.Country.Code |   18 |             | 
+----+-------------+---------+------+---------------+-------------+---------+--------------------+------+-------------+
2 rows in set (0.01 sec)

mysql>