Questions about index mysql> show index from listing; +---------+------------+-----------------+--------------+-------------+----- ------+-------------+----------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +---------+------------+-----------------+--------------+-------------+----- ------+-------------+----------+ | listing | 0 | PRIMARY | 1 | my_key | A | 137677 | NULL | | listing | 1 | mls_index | 1 | mls_number | A | 137677 | NULL | | listing | 1 | sold_new | 1 | sold_new | A | 2 | NULL | | listing | 1 | index3 | 1 | city | A | 57 | NULL | | listing | 1 | stat_city_price | 1 | stat | A | 45892 | NULL | | listing | 1 | stat_city_price | 2 | city | A | 45892 | NULL | | listing | 1 | stat_city_price | 3 | price | A | 45892 | NULL | +---------+------------+-----------------+--------------+-------------+----- ------+-------------+----------+ 7 rows in set (0.00 sec)
stat and city are enum types price is an int. mysql> explain select mls_number from listing where stat='A' and city='AH'; +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ | listing | range | index3,stat_city_price | stat_city_price | NULL | NULL | 304 | | +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ 1 row in set (0.00 sec) Question - Why is key len,ref null ?? Is it not using my key? Why does it say type range here and below it says type ref the table has 134000 records in it so it should be using key. mysql> explain select mls_number from listing where stat='S' and city='AH'; +---------+------+------------------------+-----------------+---------+----- -+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+------+------------------------+-----------------+---------+----- -+------+-------+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +---------+------+------------------------+-----------------+---------+----- -+------+-------+ 1 row in set (0.00 sec) This is what I expect mysql> explain select mls_number from listing where stat='S' and city='AH' and price>0 and price<500000 order by price; +---------+------+------------------------+-----------------+---------+----- -+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+------+------------------------+-----------------+---------+----- -+------+-------+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +---------+------+------------------------+-----------------+---------+----- -+------+-------+ 1 row in set (0.00 sec) why didn't it use price in the key?? mysql> explain select mls_number from listing where (stat='B') and city='AH' and price>0 and price<500000 order by price; +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ | listing | range | index3,stat_city_price | stat_city_price | NULL | NULL | 29 | | +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ 1 row in set (0.00 sec) mysql> explain select mls_number from listing where (stat='S' or stat='B') and city='AH' and price>0 and price<500000 order by price; +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ | listing | range | index3,stat_city_price | stat_city_price | NULL | NULL | 2535 | | +---------+-------+------------------------+-----------------+---------+---- --+------+-------+ 1 row in set (0.00 sec) Why didn't mysql use the key and 689 +29 != 2535 for rows?? stat and city are enum types price is an int Any pointers would be helpful trying to speed up queries they currently take about 16 sec table has 134000 rows. Thanks, Rick [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
