List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:January 20 2004 8:21pm
Subject:Re: Select on indexed columns
View as plain text  
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
Thread
Select on indexed columnsBalazs Rauznitz20 Jan
  • Re: Select on indexed columnsAleksandar Bradaric20 Jan
    • Re: Select on indexed columnsBalazs Rauznitz20 Jan
      • Re[2]: Select on indexed columnsAleksandar Bradaric20 Jan
        • Re: Select on indexed columnsBalazs Rauznitz20 Jan
          • Re: Select on indexed columnsDan Nelson20 Jan
  • Re: Select on indexed columnsolinux21 Jan