why doesn"t mysql select the correnct index?

why doesn"t mysql select the correnct index?

am 14.04.2008 10:25:05 von Changying Li

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why doesn"t mysql select the correnct index?

am 14.04.2008 14:14:07 von Cybot

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why doesn"t mysql select the correnct index?

am 14.04.2008 15:34:12 von Changying Li

Sebastian Mendel 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/mysql?unsub=gcdmg-mysql-2@m.gmane.org
>
>

--

Thanks & Regards

Changying Li


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why doesn"t mysql select the correnct index?

am 14.04.2008 16:11:32 von Cybot

Changying Li schrieb:
> Sebastian Mendel 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)

ok, at least MySQL does think so

>>> 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

--
Sebastian

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why doesn"t mysql select the correnct index?

am 15.04.2008 03:52:42 von Changying Li

>>>> 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 ?
>
>>>> 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.
>
> --
> Sebastian
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
>
>

--

Thanks & Regards

Changying Li


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why doesn"t mysql select the correnct index?

am 15.04.2008 09:59:42 von Cybot

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

?

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: why doesn"t mysql select the correnct index?

am 15.04.2008 11:23:26 von Changying Li

Sebastian Mendel 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/mysql?unsub=gcdmg-mysql-2@m.gmane.org
>
>

--

Thanks & Regards

Changying Li


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org