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
