When you have a table with more than 10 million rows, one row less or one row more
shouldn't make a difference, right? ... Well, think again. Look at this EXPLAIN statements,
it seems that the optimizer prefers a full table scan in the normal query but uses the index
if there's a LIMIT *number_of_rows* - 1! Is this a bug in the optimizer?
BTW, the version of mysql is 4.0.16-nt.
Thanks in advance,
Hector
mysql> explain select tel, sum(minutos) as minutos from hist_in group by 1;
+---------+------+---------------+------+---------+------+----------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+------+---------+------+----------+---------------------------------+
| hist_in | ALL | NULL | NULL | NULL | NULL | 10618816 | Using temporary; Using filesort |
+---------+------+---------------+------+---------+------+----------+---------------------------------+
1 row in set (0.01 sec)
mysql> explain select tel, sum(minutos) as minutos from hist_in group by 1 limit 10618815;
+---------+-------+---------------+------+---------+------+----------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+-------+---------------+------+---------+------+----------+-------+
| hist_in | index | NULL | tel | 42 | NULL | 10618816 | |
+---------+-------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)
mysql> explain select tel, sum(minutos) as minutos from hist_in group by 1 limit 10618816;
+---------+------+---------------+------+---------+------+----------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+------+---------------+------+---------+------+----------+---------------------------------+
| hist_in | ALL | NULL | NULL | NULL | NULL | 10618816 | Using temporary; Using filesort |
+---------+------+---------------+------+---------+------+----------+---------------------------------+
1 row in set (0.00 sec)
mysql> show index from hist_in;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | In
dex_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+
| hist_in | 1 | tel | 1 | tel | A | 1179868 | NULL | NULL | YES | BT
REE | |
| hist_in | 1 | tel | 2 | telefb | A | 10618816 | NULL | NULL | YES | BT
REE | |
| hist_in | 1 | tel | 3 | rutaentran | A | 10618816 | NULL | NULL | YES | BT
REE | |
| hist_in | 1 | tel | 4 | rutasalien | A | 10618816 | NULL | NULL | YES | BT
REE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+
4 rows in set (0.22 sec)
mysql> explain hist_in; +------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+-------+ | tel | char(8) | YES | MUL | NULL | | | telefb | char(14) | YES | | NULL | | | rutaentran | char(8) | YES | | NULL | | | rutasalien | char(8) | YES | | NULL | | | minutos | int(7) | YES | | NULL | | +------------+----------+------+-----+---------+-------+ 5 rows in set (0.17 sec)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
