table keywords (keyword_id int, keyword varchar(255));
table adwords (keyword_id int, local_traffic, global_traffic);
mysql> explain select * from keywords left join adwords on keywords.keyword_id = adwords.keyword_id where keyword like 'car%';
+----+-------------+----------+-------+---------------+----------+---------+------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+----------+---------+------------------------------+-------+--------------------------+
| 1 | SIMPLE | keywords | range | ukeyword | ukeyword | 152 | NULL | 92378 | Using where; Using index |
| 1 | SIMPLE | adwords | ref | PRIMARY | PRIMARY | 8 | keywords.keywords.keyword_id | 10735 | |
+----+-------------+----------+-------+---------------+----------+---------+------------------------------+-------+--------------------------+
mysql> explain select * from keywords left join adwords on keywords.keyword_id = adwords.keyword_id where keyword like 'car%' and local_traffic > 10000;
+----+-------------+----------+--------+------------------+---------+---------+-----------------------------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+------------------+---------+---------+-----------------------------+---------+-------------+
| 1 | SIMPLE | adwords | ALL | PRIMARY | NULL | NULL | NULL | 1073581 | Using where |
| 1 | SIMPLE | keywords | eq_ref | PRIMARY,ukeyword | PRIMARY | 8 | keywords.adwords.keyword_id | 1 | Using where |
+----+-------------+----------+--------+------------------+---------+---------+-----------------------------+---------+-------------+
mysql> explain select * from keywords force index(ukeyword) left join adwords on keywords.keyword_id = adwords.keyword_id where keyword like 'car%' and local_traffic > 10000;
+----+-------------+----------+-------+---------------+----------+---------+------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+----------+---------+------------------------------+-------+--------------------------+
| 1 | SIMPLE | keywords | range | ukeyword | ukeyword | 152 | NULL | 92378 | Using where; Using index |
| 1 | SIMPLE | adwords | ref | PRIMARY | PRIMARY | 8 | keywords.keywords.keyword_id | 10735 | Using where |
+----+-------------+----------+-------+---------------+----------+---------+------------------------------+-------+--------------------------+
You see, we are using "force index(xx)" to tell Mysql how to use index to execute the query, the performance has a big jump!