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 | 0 | PRIMARY | 1 | photo_id |
A | 17836101 | NULL | NULL | | BTREE | NULL |
| 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 | MD5 | 1 | MD5 |
A | 17836101 | NULL | NULL | | BTREE | NULL |
| photo | 1 | category_id | 1 | category_id |
A | 230 | NULL | NULL | | BTREE | NULL |
| photo | 1 | category_id | 2 | time |
A | 17836101 | NULL | NULL | | BTREE | NULL |
| photo | 1 | album_id | 1 | album_id |
A | 575358 | NULL | NULL | | BTREE | NULL |
| photo | 1 | album_id | 2 | user_id |
A | 1981789 | NULL | NULL | | BTREE | NULL |
| photo | 1 | album_id_random | 1 | album_id |
A | 575358 | NULL | NULL | | BTREE | NULL |
| photo | 1 | album_id_random | 2 | random |
A | 8918050 | 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 |
| photo | 1 | original_signature | 1 | original_signature |
A | 17836101 | NULL | NULL | | BTREE | NULL |
| photo | 1 | file_name | 1 | file_name |
NULL | 3567220 | NULL | NULL | | FULLTEXT | 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 ? how to let mysql choose user_id as an index
? what's the mean of 'rows' ? how doese mysql get value of 'rows'?
I really dont wnat to use 'force index' because I'm using DBIx::Class in perl
catalyst framework.
--
Thanks & Regards
Changying Li
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
