Hello, I am running into a problem with MySQL. I just can't figure it out and I have spent several days scouring the Internet for ideas. I have a query that joins 3 tables and I just cannot stop it from using filesort.
The three tables are "prop_data2", "prop_data_int", and "prop_data_string". TABLE 1: explain prop_data2; +-------------+---------------------+------+-----+---------+---------------- + | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+---------------- + | id | int(10) unsigned | | PRI | NULL | auto_increment | | type | tinyint(3) unsigned | | MUL | 1 | | | wopr_status | tinyint(3) unsigned | | MUL | 1 | | | creator | tinyint(3) unsigned | | MUL | 1 | | +-------------+---------------------+------+-----+---------+---------------- + show index from prop_data2; +------------+------------+-------------+--------------+-------------+------ -----+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +------------+------------+-------------+--------------+-------------+------ -----+-------------+----------+--------+---------+ | prop_data2 | 0 | PRIMARY | 1 | id | A | 19532 | NULL | NULL | | | prop_data2 | 0 | id | 1 | id | A | 0 | NULL | NULL | | | prop_data2 | 1 | id_2 | 1 | id | A | 19532 | NULL | NULL | | | prop_data2 | 1 | type | 1 | type | A | 9 | NULL | NULL | | | prop_data2 | 1 | wopr_status | 1 | wopr_status | A | 2 | NULL | NULL | | | prop_data2 | 1 | type_status | 1 | type | A | 7 | NULL | NULL | | | prop_data2 | 1 | type_status | 2 | wopr_status | A | 18 | NULL | NULL | | | prop_data2 | 1 | creator | 1 | creator | A | 1 | NULL | NULL | | +------------+------------+-------------+--------------+-------------+------ -----+-------------+----------+--------+---------+ TABLE 2: explain prop_data_int; +-------------------+---------------------+------+-----+---------+---------- ------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+---------- ------+ | id | bigint(20) unsigned | | PRI | NULL | auto_increment | | fkey_propid | int(10) unsigned | | MUL | 0 | | | fkey_propattribid | int(10) unsigned | | | 0 | | | value | bigint(20) unsigned | | | 0 | | +-------------------+---------------------+------+-----+---------+---------- ------+ show index from prop_data_int; +---------------+------------+----------+--------------+-------------------+ -----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---------------+------------+----------+--------------+-------------------+ -----------+-------------+----------+--------+---------+ | prop_data_int | 0 | PRIMARY | 1 | id | A | 199131 | NULL | NULL | | | prop_data_int | 0 | id | 1 | id | A | 199131 | NULL | NULL | | | prop_data_int | 1 | id_2 | 1 | id | A | 199131 | NULL | NULL | | | prop_data_int | 1 | id_2 | 2 | fkey_propid | A | 199131 | NULL | NULL | | | prop_data_int | 1 | id_2 | 3 | fkey_propattribid | A | 199131 | NULL | NULL | | | prop_data_int | 1 | id_2 | 4 | value | A | 199131 | NULL | NULL | | | prop_data_int | 1 | pav | 1 | fkey_propid | A | 19913 | NULL | NULL | | | prop_data_int | 1 | pav | 2 | fkey_propattribid | A | 199131 | NULL | NULL | | | prop_data_int | 1 | pav | 3 | value | A | 199131 | NULL | NULL | | +---------------+------------+----------+--------------+-------------------+ -----------+-------------+----------+--------+---------+ TABLE 3: explain prop_data_string; +-------------------+---------------------+------+-----+---------+---------- ------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+---------- ------+ | id | bigint(20) unsigned | | PRI | NULL | auto_increment | | fkey_propid | int(10) unsigned | | MUL | 0 | | | fkey_propattribid | int(10) unsigned | | MUL | 0 | | | fkey_stringid | bigint(20) unsigned | | MUL | 0 | | +-------------------+---------------------+------+-----+---------+---------- ------+ show index from prop_data_string; +------------------+------------+----------+--------------+----------------- --+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +------------------+------------+----------+--------------+----------------- --+-----------+-------------+----------+--------+---------+ | prop_data_string | 0 | PRIMARY | 1 | id | A | 475914 | NULL | NULL | | | prop_data_string | 0 | id | 1 | id | A | 0 | NULL | NULL | | | prop_data_string | 1 | id_2 | 1 | id | A | 475914 | NULL | NULL | | | prop_data_string | 1 | id_2 | 2 | fkey_propid | A | 475914 | NULL | NULL | | | prop_data_string | 1 | id_2 | 3 | fkey_propattribid | A | 475914 | NULL | NULL | | | prop_data_string | 1 | id_2 | 4 | fkey_stringid | A | 475914 | NULL | NULL | | | prop_data_string | 1 | akey | 1 | fkey_propattribid | A | 228 | NULL | NULL | | | prop_data_string | 1 | sid | 1 | fkey_stringid | A | 6180 | NULL | NULL | | | prop_data_string | 1 | pas | 1 | fkey_propid | A | 19829 | NULL | NULL | | | prop_data_string | 1 | pas | 2 | fkey_propattribid | A | 475914 | NULL | NULL | | | prop_data_string | 1 | pas | 3 | fkey_stringid | A | 475914 | NULL | NULL | | +------------------+------------+----------+--------------+----------------- --+-----------+-------------+----------+--------+---------+ The query I am using is the following: SELECT p.id FROM prop_data2 AS p USE INDEX(type_status) LEFT JOIN prop_data_int AS price USE INDEX(pav) ON price.fkey_propid=p.id LEFT JOIN prop_data_string AS com USE INDEX(pas) ON com.fkey_propid=p.id WHERE p.type=1 && p.wopr_status=1 && price.fkey_propattribid=316 && com.fkey_propattribid=326 && ( com.fkey_stringid=3030 ) GROUP BY p.id ORDER BY price.value DESC; When I look at the optimizer through EXPLAIN I see this: +-------+------+------------------------------+-------------+---------+----- --------+------+---------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+------------------------------+-------------+---------+----- --------+------+---------------------------------------------+ | p | ref | type,wopr_status,type_status | type_status | 2 | const,const | 4455 | where used; Using temporary; Using filesort | | price | ref | pav | pav | 4 | p.id | 10 | where used; Using index | | com | ref | pas | pas | 4 | p.id | 24 | where used; Using index | +-------+------+------------------------------+-------------+---------+----- --------+------+---------------------------------------------+ What concerns me is row 1, for the table "prop_data2" which is aliased as "p". I am trying to force the index "type_status" but it is not taking it. I understand that if the rows matched are more than 30% of the total rows that mysql will abandon the index for that table, so I tried adding a "LIMIT 500" clause. However, it didn't make any difference. Is my SQL query structured poorly or could I be running into a MySQL configuration problem? Thanks for any help. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
