Greetings,
It seems the lists.mysql.com imposes a 50KB limit on messages, so this message will be sent in two parts. Thanks for your patience. I'm in the process of converting most of my databases from MyISAM to InnoDB, and I've come across the most peculiar problem. It seems that after running for some time, MySQL starts to choose different indexes to run same the exact same query with! After the daemon has been up and running for an undetermined amount of time, the output of EXPLAIN on some queries differs from the output of EXPLAIN taken upon server startup! The different EXPLAIN usually means a much, much slower query. This only happens on InnoDB tables. I can reproduce this error in both MySQL 5.0.24 and MySQL 5.0.27 installed from source on x86_64 Linux. The running kernel is 2.6.14.6, glibc is 2.3.9, and MySQL is linked against OpenSSL 0.9.8a Here's an example of what I mean by the exact same query being run in different ways: After the server has been up and running for a couple of days, I get complaints from end users about web applications running slowly. I log in and look at the processlist and see some queries have been "on the clock" for over 400 seconds. Since I'm testing and have separate InnoDB and MyISAM servers, I push the front ends back to the MyISAM version of the database, and begin troubleshooting the query on the now unused InnoDB version. mysql> EXPLAIN SELECT COUNT(rb.subscription_id) as recurring_cc_count, -> SUM(rb.grace_price) as recurring_cc, -> COUNT(sb.subscription_id) as single_cc_count, -> SUM(sb.initial_amt) as single_cc -> FROM customerdetail a -> LEFT JOIN recurringbilling rb -> ON a.subscription_id = rb.subscription_id -> LEFT JOIN singlebilling sb -> ON a.subscription_id = sb.subscription_id -> LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) -> ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) -> WHERE client_accnum = '12345' -> AND a.trans_timestamp -> BETWEEN '20070108000000' AND '20070108235959'; +----+-------------+-------+--------+----------------------------------- -----------------------------+------------+---------+------------------- ------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------------------------- -----------------------------+------------+---------+------------------- ------------+-------+-------------+ | 1 | SIMPLE | a | ref | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | client_idx | 3 | const | 25032 | Using where | | 1 | SIMPLE | rb | eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id | 1 | | | 1 | SIMPLE | sb | eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id | 1 | | | 1 | SIMPLE | ser | ref | PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id | 1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode | 1 | Using index | +----+-------------+-------+--------+----------------------------------- -----------------------------+------------+---------+------------------- ------------+-------+-------------+ 5 rows in set (0.61 sec) mysql> show index from customerdetail; +----------------+------------+-----------------+--------------+-------- ---------+-----------+-------------+----------+--------+------+--------- ---+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +----------------+------------+-----------------+--------------+-------- ---------+-----------+-------------+----------+--------+------+--------- ---+---------+ | customerdetail | 0 | subscription | 1 | subscription_id | A | 49382031 | NULL | NULL | | BTREE | | | customerdetail | 1 | client_idx | 1 | client_accnum | A | 86181 | NULL | NULL | | BTREE | | | customerdetail | 1 | client_idx | 2 | client_subacc | A | 103309 | NULL | NULL | | BTREE | ........................SNIP..................... +----+-------------+-------+--------+----------------------------------- -----------------------------+------------+---------+------------------- ------------+ mysql> SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, -> SUM(rb.grace_price) as recurring_cc, -> COUNT(sb.subscription_id) as single_cc_count, -> SUM(sb.initial_amt) as single_cc -> FROM customerdetail a -> LEFT JOIN recurringbilling rb -> ON a.subscription_id = rb.subscription_id -> LEFT JOIN singlebilling sb -> ON a.subscription_id = sb.subscription_id -> LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) -> ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) -> WHERE client_accnum = '12345' -> AND a.trans_timestamp -> BETWEEN '20070108000000' AND '20070108235959'; +--------------------+--------------+-----------------+-----------+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | +--------------------+--------------+-----------------+-----------+ | 4 | 119.80 | 0 | NULL | +--------------------+--------------+-----------------+-----------+ 1 row in set (434.77 sec) As you can see, the optimizer has chosen to use the client_idx index for the customerdetail table which is a compound index of client_accnum and client_subacc, which in this particular table of 49299139 rows, has horrible cardinality. So I restart the server, log back in, and try the EXACT SAME THING again: SEE NEXT PART