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
+++-+--+
