Sebastian Mendel <[EMAIL PROTECTED]> writes: > 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 but in fact group_id is very slow (51.21 sec), user_id is (0.00 sec) > >> 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 the result is empty set, if what you said is true, then the rows must be 0 ? > >> 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)? because this table is too big. if user_id can resolve this problem, I'll not add one more index . > > -- > Sebastian > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Thanks & Regards Changying Li -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
