Description:
In newer versions of MySQL 5.0.x query execution plan generated sometimes changes unexpectedly (it was stabil and good in 5.0.27!). You may get:
mysql> explain SELECT SUM(r.totalStays) as totalStays, d.year as year, SUM(r.tot_nr_rooms)/SUM(r.totalStays) as avg_nr_rooms, SUM(r.roomnights) as roomnights, SUM(r.tot_nr_guests)/SUM(r.totalStays) as avg_nr_guests, SUM(r.totalLos)/SUM(r.totalStays) as averageLos, SUM(tot_booking_window)/SUM(r.totalStays) as averageBookingWindow, SUM( r.price_euro ) as price_euro, SUM( r.price ) as price, SUM(r.tot_commission_perc)/SUM(r.totalStays) as avg_commission_perc, SUM( r.commission_amount_euro ) as commission_amount_euro, SUM( r.commission_amount ) as commission_amount, r.currencycode as currencycode , d.month as month FROM date d join reservationsByHotelByDayNett r on (d.id = r.dateid) , dw.RegionCity as rc WHERE d.date >= '2007-04-01' AND d.date <= '2007-09-01' AND hotel_ufi = rc.ufi AND rc.region_id in (2408,1101,2407,2405,1707) GROUP BY d.monthyear , currencycode ORDER BY d.monthyear , currencycode\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: r
type: ALL
possible_keys: datehotelcc1,datehotelufi,datehotelid,datestaffid,hotelufidate
key: NULL
key_len: NULL
ref: NULL
rows: 9843157
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: d
type: ref
possible_keys: date,id,datemonthyearyearmonthid
key: id
key_len: 4
ref: rmtools.r.dateid
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: rc
type: ref
possible_keys: PRIMARY,ufi
key: ufi
key_len: 4
ref: rmtools.r.hotel_ufi
rows: 16
Extra: Using where
3 rows in set (0.03 sec)
instead of:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rc
type: range
possible_keys: PRIMARY,ufi
key: PRIMARY
key_len: 4
ref: NULL
rows: 53
Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: d
type: range
possible_keys: date,id,datemonthyearyearmonthid
key: datemonthyearyearmonthid
key_len: 3
ref: NULL
rows: 138
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: r
type: ref
possible_keys: datehotelufi
key: datehotelufi
key_len: 9
ref: rmtools.d.id,dw.rc.ufi
rows: 9843157
Extra: Using where
3 rows in set (0.01 sec)
That is, different join orfer, and, as a result, index is not used for a big table. FORCE INDEX and other hints help, so this is not S1 bug.
The real problem is that query execution plan may change (for the same tables and data) for unclear and undocumented reasons. Looks like it may depend on:
1. Minor kernel and glibc version.
Downgrade from 2.6.9-42.0.10.ELsmp to 2.6.9-42.0.3.ELsmp and upgrade from glibc-2.3.4-2.13 to glibc-2.3.4-2.36 lead to a stabil good plan.
2. CHECK TABLE ... EXTENDED execution.
I do not see anything in http://dev.mysql.com/doc/refman/5.0/en/check-table.html that explains the influence of this statement on optimizer's behaviour. Anyway, optimizer may generate good plan after running this statements for all the tables involved.
How to repeat:
No exact way to repeat each and every time outside customer's environment. This is the point of a bug report.
Suggested fix:
Generate same execution plan for the same query, tables and data even in slightly changed system environment.
As for CHECK TABLE's influence, this behaviour, if intended, should be just documented.
