То ничего не изменится. Порядок выполнения определяет оптимизатор.
Ага... Для самостоятельного определения порядка таблиц есть опция STRAIGHT_JOIN:
SELECT STRAIGHT_JOIN ...
FROM table_first, table_second ...
STRAIGHT_JOIN forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use
this to speed up a query if the optimizer joins the tables in nonoptimal order. STRAIGHT_JOIN also can be used in the table_
references list. See Section 12.2.8.1, “JOIN Syntax”.
STRAIGHT_JOIN does not apply to any table that the optimizer treats as a const or system table. Such a table produces a
single row, is read during the optimization phase of query execution, and references to its columns are replaced with the appropriate
column values before query execution proceeds. These tables will appear first in the query plan displayed by EXPLAIN.
See Section 7.2.1, “Optimizing Queries with EXPLAIN”. This exception may not apply to const or system tables that are
used on the NULL-complemented side of an outer join (that is, the right-side table of a LEFT JOIN or the left-side table of a
RIGHT JOIN.
Вот в чем фишка "супер-оптимизации": если планируемый результат чтения таблицы состоит из одной строки, то она и считывается до завершения работы оптимизатора. Значит оптимизатор такой хреновый.
Добавлено через 4 минуты и 38 секунд:Вот влияние STRAIGHT_JOIN.
mysql> explain SELECT STRAIGHT_JOIN b.locID FROM geoip.blocks b, test.requests r WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+---------------------------------------------+
| 1 | PRIMARY | b | ALL | PRIMARY | NULL | NULL | NULL | 3648186 | |
| 1 | PRIMARY | r | index | NULL | ip | 4 | NULL | 12134 | Using where; Using index; Using join buffer |
| 2 | DEPENDENT SUBQUERY | b2 | index | PRIMARY | PRIMARY | 4 | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+-------+---------------+---------+---------+------+---------+---------------------------------------------+
3 rows in set (0.02 sec)
mysql> explain SELECT b.locID FROM geoip.blocks b, test.requests r WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| 1 | PRIMARY | r | index | NULL | ip | 4 | NULL | 12134 | Using index |
| 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | b2 | index | PRIMARY | PRIMARY | 4 | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
3 rows in set (0.01 sec)
Переставил таблицы.
mysql> explain SELECT STRAIGHT_JOIN b.locID FROM test.requests r, geoip.blocks b WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| 1 | PRIMARY | r | index | NULL | ip | 4 | NULL | 12134 | Using index |
| 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | b2 | index | PRIMARY | PRIMARY | 4 | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
3 rows in set (0.00 sec)
mysql> explain SELECT b.locID FROM test.requests r, geoip.blocks b WHERE b.endIP = (SELECT IFNULL(MIN(b2.endIP), 0) FROM geoip.blocks b2 WHERE b2.endIP >= r.ip);
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
| 1 | PRIMARY | r | index | NULL | ip | 4 | NULL | 12134 | Using index |
| 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | b2 | index | PRIMARY | PRIMARY | 4 | NULL | 3648186 | Using where; Using index |
+----+--------------------+-------+--------+---------------+---------+---------+------+---------+--------------------------+
3 rows in set (0.00 sec)
Добавлено через 3 минуты и 57 секунд:Выбираю отличающийся запрос. Запускаю...
SELECT STRAIGHT_JOIN b.locID
FROM geoip.blocks b, test.requests r
WHERE b.endIP = (
SELECT IFNULL(MIN(b2.endIP), 0)
FROM geoip.blocks b2
WHERE b2.endIP >= r.ip
);
Результат такой же хреновый: все 12 тыс. строк обработаны не за пару секунд - не стал ждать более минуты, ибо нет смысла.
Добавлено через 1 час, 4 минуты и 36 секунд:С выбранным порядком получается full join для внешних таблиц. Для оптимальной же формы выполняется:
1. Выборка единственного значения r.ip.
2. Подзапрос b2.
3. Выборка по равенству из b.
От того и быстро выполняется.
Почитал: тип "index" операции в плане выполнения - это full index join. Т.ч. Пух прав как никогда
Добавлено через 1 час, 12 минут и 21 секунду:Итого:
mysql> explain SELECT l.country, COUNT(*) FROM test.requests r, geoip.locations l WHERE l.locID = geoip.get_location_id2(r.ip) GROUP BY l.country;
+----+-------------+-------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
| 1 | SIMPLE | r | index | NULL | ip | 4 | NULL | 12134 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | l | eq_ref | PRIMARY | PRIMARY | 3 | func | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT l.country, COUNT(*) FROM test.requests r, geoip.locations l WHERE l.locID = geoip.get_location_id2(r.ip) GROUP BY l.country;
+---------+----------+
| country | COUNT(*) |
+---------+----------+
| AE | 1 |
| AM | 4 |
| AR | 21 |
| AZ | 52 |
| BE | 13 |
| BG | 1 |
| BR | 9 |
| BY | 101 |
| CH | 1 |
| CL | 18 |
| CN | 3 |
| CO | 2 |
| CZ | 57 |
| DE | 71 |
| DK | 14 |
| EC | 3 |
| EE | 42 |
| ES | 47 |
| FI | 1 |
| FR | 16 |
| GB | 19 |
| GR | 36 |
| GT | 2 |
| HR | 3 |
| HU | 2 |
| IL | 32 |
| IN | 26 |
| IT | 15 |
| JP | 23 |
| KG | 8 |
| KZ | 47 |
| LT | 35 |
| LU | 1 |
| LV | 9 |
| MD | 29 |
| MO | 17 |
| MX | 38 |
| NL | 13 |
| NO | 12 |
| NP | 2 |
| PE | 1 |
| PL | 28 |
| PT | 17 |
| RO | 20 |
| RS | 7 |
| RU | 5592 |
| SE | 3 |
| SK | 47 |
| TH | 7 |
| TR | 28 |
| TW | 3 |
| UA | 5443 |
| US | 90 |
| UY | 2 |
+---------+----------+
54 rows in set (2.10 sec)