Hi Heath, MySQL cannot use the trans_team query because you're using !=, for which an index is never used (currently anyway). Do you think that trans_team is the best index that will find the least rows and produce the fastest result? If so, you can try using the following, which can be optimized, instead of !=
... AND (transfer_logs.trans_team < 'team oscar' OR transfer_logs.trans_team > 'team oscar') I think that's the same as !=. :-) But MySQL will only use the index if it will find few enough rows (< ~30%) -- in other words, if more than ~2/3 of the trans_team values ARE 'team oscar'. Hope that helps. Matt ----- Original Message ----- From: "heath boutwell" Sent: Wednesday, October 08, 2003 3:15 PM Subject: Innodb won't recognize index when optimizing query > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
