[Fwd: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query]
Please post to the list not to me personnally. Original Message Subject: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query From: John Anderson [EMAIL PROTECTED] Date:Thu, January 18, 2007 10:24 To: William R. Mussatto [EMAIL PROTECTED] -- I optimized every table after I first imported the data. The tables were probably in use, off and on for testing, for about a week after the optimize table was ran on every table before I noticed this problem. I'm not saying the problem didn't exist within that week, I'm just saying I didn't notice it ;) . Another thing. Does the query optimizer keep any sort of statistics and use them to make decisions for future queries on the same table? If so, then that could be the problem because we have certain fields, containing only numbers, but were previously setup as varchars for some unknown reason. I changed them all to int types but some queries in obscure parts of our applications are still querying this field as if it were a character field, using LIKE, etc. I'm slowly but sure tracking those down and fixing them, I'm just curious if that could have anything to do with this strange behavior. Thanks, John A. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 5:17 PM To: mysql@lists.mysql.com Subject: Re: [PART 2/2] InnoDB - Different EXPLAINs for same query Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: 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 '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain 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 '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 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
FW: [PART 2/2] InnoDB - Different EXPLAINs for same query
I optimized every table after I first imported the data. The tables were probably in use, off and on for testing, for about a week after the optimize table was ran on every table before I noticed this problem. I'm not saying the problem didn't exist within that week, I'm just saying I didn't notice it ;) . Another thing. Does the query optimizer keep any sort of statistics and use them to make decisions for future queries on the same table? If so, then that could be the problem because we have certain fields, containing only numbers, but were previously setup as varchars for some unknown reason. I changed them all to int types but some queries in obscure parts of our applications are still querying this field as if it were a character field, using LIKE, etc. I'm slowly but sure tracking those down and fixing them, I'm just curious if that could have anything to do with this strange behavior. Thanks, John A. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 5:17 PM To: mysql@lists.mysql.com Subject: Re: [PART 2/2] InnoDB - Different EXPLAINs for same query Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: 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 '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain 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 '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 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.00 sec) mysql show index from customerdetail
[PART 2/2] InnoDB - Different EXPLAINs for same query
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 '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain 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 '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 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.00 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 | +++-+--+ -+---+-+--++--+- ---+- ..SNIP | customerdetail | 1 | accno_trans_idx |1 | client_accnum | A | 17052 | NULL | NULL | | BTREE | | | customerdetail | 1 | accno_trans_idx |2 | trans_timestamp | A |49042196 | NULL | NULL | | BTREE | ..SNIP +++-+--+ -+---+-+--++--+- ---+-+ The query executes orders of magnitude faster, and the EXPLAIN shows why. MySQL has now chosen to use the accno_trans_idx index for the customerdetail table which has much better cardinality (almost 1 key per row). The obvious workaround is use FORCE INDEX(accno_trans_idx) in the query for now, but I'm beginning to think this may be a bug. Has anyone else had any similar issues? I haven't found anything like this in the bug database though. If anyone doesn't see
Re: [PART 2/2] InnoDB - Different EXPLAINs for same query
Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: 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 '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain 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 '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 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.00 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 | +++-+--+ -+---+-+--++--+- ---+- ..SNIP | customerdetail | 1 | accno_trans_idx |1 | client_accnum | A | 17052 | NULL | NULL | | BTREE | | | customerdetail | 1 | accno_trans_idx |2 | trans_timestamp | A |49042196 | NULL | NULL | | BTREE | ..SNIP +++-+--+ -+---+-+--++--+- ---+-+ The query executes orders of magnitude faster, and the EXPLAIN shows why. MySQL has now chosen to use the accno_trans_idx index for the
Re: [PART 2/2] InnoDB - Different EXPLAINs for same query
While I think optimize does an analyze, you may find that just an ANALYZE will do it instead of a full OPTIMIZE: http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html On 18/01/07, William R. Mussatto [EMAIL PROTECTED] wrote: Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: 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 '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain 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 '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 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.00 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 | +++-+--+ -+---+-+--++--+- ---+- ..SNIP | customerdetail | 1 | accno_trans_idx |1 | client_accnum | A | 17052 | NULL | NULL | | BTREE | | | customerdetail | 1 | accno_trans_idx |2 | trans_timestamp | A |49042196 | NULL | NULL | | BTREE | ..SNIP +++-+--+
