Changying Li schrieb:
Hi. there is a table photo and two queries: mysql> show index from photo; mysql> show index from photo; +-------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+ | photo | 1 | user_id | 1 | user_id | A | 1372007 | NULL | NULL | | BTREE | NULL | | photo | 1 | user_id | 2 | banned | A | 1621463 | NULL | NULL | | BTREE | NULL | | photo | 1 | group_id | 1 | group_id | A | 12403 | NULL | NULL | | BTREE | NULL | | photo | 1 | group_id | 2 | album_id | A | 575358 | NULL | NULL | | BTREE | NULL | +-------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+ 14 rows in set (0.00 sec)explain select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id > '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; +----+-------------+-------+------+---------------------------------------------------+----------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------------------------------------------+----------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | photo | ref | PRIMARY,user_id,album_id,album_id_random,group_id | group_id | 3 | const | 1438 | Using where; Using filesort | +----+-------------+-------+------+---------------------------------------------------+----------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> select * FROM photo WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id > '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (51.21 sec) mysql> explain select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id > '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; +----+-------------+-------+------+---------------+---------+---------+-------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-------------+------+-----------------------------+ | 1 | SIMPLE | photo | ref | user_id | user_id | 4 | const,const | 1694 | Using where; Using filesort | +----+-------------+-------+------+---------------+---------+---------+-------------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> select * FROM photo use index (user_id) WHERE ( album_id != '0' AND banned = '0' AND group_id = '0' AND photo_id > '27103315' AND rating != '1' AND user_id = '882092' ) ORDER BY photo_id LIMIT 50; Empty set (0.00 sec) why does mysql use group_id index ?
because in this case group_id would be faster than user_id
how to let mysql choose user_id as an index ? what's the mean of 'rows' ?
valid rows after applying the WHERE to this index
how doese mysql get value of 'rows'?
count returned values from index with valid WEHERE
I really dont wnat to use 'force index' because I'm using DBIx::Class in perl catalyst framework.
why do you want to FORCE INDEX? did you tried an index(user_id, group_id)? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
