In the last episode (Jan 20), Balazs Rauznitz said:
> On Tue, Jan 20, 2004 at 08:23:32PM +0100, Aleksandar Bradaric wrote:
>
> mysql> explain select count(*) from sex where id>459000 and id <=460000 and sex =
> 'M';
> +-------+------+------------------------------------+-----------+---------+-------+--------+-------------+
> | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
> +-------+------+------------------------------------+-----------+---------+-------+--------+-------------+
> | sex | ref | id_index,sex_index,sex,id,sex_both | sex_index | 2 | const |
> 506151 | Using where |
> +-------+------+------------------------------------+-----------+---------+-------+--------+-------------+
> 1 row in set (0.00 sec)
Try hinting it to use the compound index, or maybe recreate the index
with the fields in the other order (sex,id), so it can do a straight
range scan. With an (id,sex) index, it should be able to do a range
scan but still has to discard half the records.
What's the difference between id, and id_index, and sex and sex_index?
"SHOW KEYS FROM sex" will list the subparts of all the keys, and their
cardinality (you may have to ANALYZE the table first).
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
