Postoji ovaj upit:
SELECT *
FROM `zonex_content` AS `c`
WHERE c.module = 20
AND c.categoryID
IN ('50', '31', '16', '10')
ORDER BY `c`.`released` DESC
LIMIT 5
Tabela ima oko 500 000 redova ali ovaj upit nece vratiti nijedan.
Ako uradim EXPLAIN za LIMIT 3 dobijam ovo:
+----+-------------+-------+-------+-------------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | c | index | module,categoryID | released | 5 | NULL | 1075 | Using where |
+----+-------------+-------+-------+-------------------+----------+---------+------+------+-------------+
Ako uradim EXPLAIN za limit 4 dobijam ovo:
+----+-------------+-------+------+-------------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+--------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | c | ref | module,categoryID | module | 1 | const | 1364 | Using where; Using filesort |
+----+-------------+-------+------+-------------------+--------+---------+-------+------+-----------------------------+
Kao sto mozete videti za LIMIT 4 (i vece od 4) je ukljucen filesort tj. ne radi se ORDER BY optimizacija.
Da li neko zna kako ORDER BY optimizacija zavisi od LIMIT i kako (i da li) tu zavisnost mogu da izmenim?
Hvala unapred.