From: Dan Nelson
Date: January 20 2004 8:21pm
Subject: Re: Select on indexed columns
List-Archive: http://lists.mysql.com/mysql/158011
Message-Id: <20040120202132.GB362@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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
	dnelson@stripped