Optimizing MySQL and PHP



EXPLAIN SELECT City.Name,Country.Name,City.Population FROM City, Country
         WHERE City.CountryCode = Country.Code AND City.Population > 8000000
     ORDER BY City.Population;

+---------+--------+---------------+---------+---------+------------------+------+-----------------------------+
| table   | type   | possible_keys | key     | key_len | ref              | rows | Extra                       |
+---------+--------+---------------+---------+---------+------------------+------+-----------------------------+
| City    | ALL    | NULL          | NULL    |    NULL | NULL             | 4079 | Using where; Using filesort |
| Country | eq_ref | PRIMARY       | PRIMARY    |       3 | City.CountryCode |    1 |                          |
+---------+--------+---------------+---------+---------+------------------+------+-----------------------------+

• system: table only has one row

• const: query matches at most one row from this table

• eq_ref: one row from this table will match from previous tables

• ref: rows matching from this table will match reference from earlier table

• ref_or_null: like ref, but also matching null references (used by subqueries)

• range: range of rows will be retrieved

• index: the entire index will be scanned

• ALL: the entire table will be scanned

Add a new index



ALTER TABLE City ADD INDEX (population);

+---------+--------+---------------+------------+---------+------------------+------+-------------+
| table   | type   | possible_keys | key        | key_len | ref              | rows | Extra       |
+---------+--------+---------------+------------+---------+------------------+------+-------------+
| City    | range  | Population    | Population |       4 | NULL             |   78 | Using where |
| Country | eq_ref | PRIMARY       | PRIMARY    |       3 | City.CountryCode |    1 |             |
+---------+--------+---------------+------------+---------+------------------+------+-------------+