Sebastian Mendel <[EMAIL PROTECTED]> writes: > Changying Li schrieb: >>>>>> 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) >>> ok, at least MySQL does think so >> I know, but I what I really want to know is how does mysql think so ? > > because "rows" is smaller for this index, when deciding what index to > use it seems MySQL does not take into account what other/later steps > needed (on joined tables) to get the final result ... > > >>>>>> 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 ? >>> no, not the final result, only for this index >>> >>> read about EXPLAIN in the MySQL manual >>> >>> http://dev.mysql.com/doc/refman/5.0/en/using-explain.html >> I has read it , and it described like what you said, I don't really know >> what is the mean of 'only for this index', >> I tried 'select count(*) from photo where group_id=0 and album_id!=0,' >> ant it get a huge number, but not the value of rows. > > value of rows for > > EXPLAIN select count(*) from photo where group_id=0 and album_id!=0 > > ? mysql> EXPLAIN select count(*) from photo where group_id=0 and album_id!=0; +----+-------------+-------+-------+-----------------------------------+----------+---------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-----------------------------------+----------+---------+------+----------+--------------------------+ | 1 | SIMPLE | photo | range | album_id,album_id_random,group_id | group_id | 6 | NULL | 16567648 | Using where; Using index | +----+-------------+-------+-------+-----------------------------------+----------+---------+------+----------+--------------------------+ 1 row in set (0.00 sec)
mysql> select count(*) from photo where group_id=0 and album_id!=0; +----------+ | count(*) | +----------+ | 17155770 | +----------+ 1 row in set (9.05 sec) > > beside the fact, MySQL should not need to investigate any row at all > for this query, it should satisfy this query from the index, without > looking up any row > > so this is a bad example > > > -- > 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]
