The query optimizer will not recognize an index on an innodb table. tranfer_logs is an
innodb
table, auth_user is not. As demonstrated below, trans_team is not even recognized as
a possible
key when EXPLAIN SELECT is used. (my apologies for the extra wide message). I am not
sure if this
is an innodb issue or just an ignorant user(me) issue.
mysql> show keys from transfer_logs;
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality |
Sub_part | Packed |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+
| transfer_logs | 0 | PRIMARY | 1 | trans_id | A |
3573681 |
NULL | NULL |
| transfer_logs | 1 | trans_time | 1 | trans_time | A |
3573681 |
NULL | NULL |
| transfer_logs | 1 | user_id | 1 | user_id | A |
11872 |
NULL | NULL |
| transfer_logs | 1 | event_id | 1 | event_id | A |
108293 |
NULL | NULL |
| transfer_logs | 1 | trans_team | 1 | trans_team | A |
1786840 |
NULL | NULL |
| transfer_logs | 1 | trans_type | 1 | trans_type | A |
21 |
NULL | NULL |
| transfer_logs | 1 | trans_cat | 1 | trans_cat | A |
21 |
NULL | NULL |
+---------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+
mysql> EXPLAIN SELECT
-> SUM(transfer_logs.trans_net)/100 AS all_fees
-> FROM transfer_logs, auth_user
-> WHERE
-> auth_user.user_ref = '37' &&
-> transfer_logs.user_id = auth_user.user_id &&
-> transfer_logs.trans_time < 20031008153915 &&
-> transfer_logs.trans_cat = 'deposit' &&
-> transfer_logs.trans_app = 't' &&
-> transfer_logs.trans_team != 'team oscar';
+---------------+------+------------------------------+----------+---------+-------------------+------+------------+
| table | type | possible_keys | key | key_len | ref
|
rows | Extra |
+---------------+------+------------------------------+----------+---------+-------------------+------+------------+
| auth_user | ref | PRIMARY,user_ref | user_ref | 20 | const
|
13 | where used |
| transfer_logs | ref | trans_time,user_id,trans_cat | user_id | 32 |
auth_user.user_id |
301 | where used |
+---------------+------+------------------------------+----------+---------+-------------------+------+------------+
As witnessed above, possible_keys doesn't even list trans_team as a possible index. I
want to use
the index on trans_team but when I try to force this via USE INDEX
mysql> EXPLAIN SELECT
-> SUM(transfer_logs.trans_net)/100 AS all_fees
->FROM transfer_logs USE INDEX(trans_team), auth_user
->WHERE
-> auth_user.user_ref = '37' &&
-> transfer_logs.user_id = auth_user.user_id &&
-> transfer_logs.trans_time < 20031008153915 &&
-> transfer_logs.trans_cat = 'deposit' &&
-> transfer_logs.trans_app = 't' &&
-> transfer_logs.trans_team != 'team oscar';
+---------------+--------+------------------------------+---------+---------+-----------------------+---------+------------+
| table | type | possible_keys | key | key_len | ref
| rows | Extra |
+---------------+--------+------------------------------+---------+---------+-----------------------+---------+------------+
| transfer_logs | ALL | trans_time,user_id,trans_cat | NULL | NULL | NULL
| 2036463 | where used |
| auth_user | eq_ref | PRIMARY,user_ref | PRIMARY | 32 |
transfer_logs.user_id | 1 | where used |
+---------------+--------+------------------------------+---------+---------+-----------------------+---------+------------+
2 rows in set (0.00 sec)
Any ideas on wny innodb won't recognize the index on trans_team? This query shouldn't
take 1
minute+ but it does unless I can force the optimizer to use the proper index. ANALYZE
TABLE has no
effect on innodb tables, correct?
As you can see above even trying to use USE INDEX doesn't get the optimizer to behave.
This is on
MysQL 3.23.58-Max.
Thanks,
sql query
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
