OFF натолкните на мысль по оптимизации запроса

Bitmag 21.05.2009 15:31

Не могу добиться быстрой работы:

select SQL_CALC_FOUND_ROWS * from tours_details_86_379 where D>='2009-5-21' and D<='2009-08-31' and roomNA='4' and aFCK='379' and htlD>='3' and htlD<='10' and roomNC='2' and (htlRK in ('2','3','4','5','6','15016','15018','16538','16550','16552','16555','16556','24','16557','16542','25','16558','16543','16559','16544','16546','16547','15030','15031','16561','16562','16563','16565','16567','16569','16572','16575','16576','16578','16579','16591','16592','16593','16595','16580','16583','16584','16586','16587','16588','32')) and (htlK in ('3273','2284','2317','3274','3275','2305','2470','2287','3276','2132','3277','2307','2133','2289','2472','2030','3279','2032','3290','2475','2033','3291','2476','2138','2034','3292','2321','2477','2478','2139','2322','3294','2323','2037','2151','3281','3282','1994','2326','1551','2152','2039','3283','3284','3317','3302','1554','3285','3286','2141','1555','3511','2142','3512','3287','3305','2203','3306','3288','2204','3513','2524','2143','3307','3289','2144','3514','2205','2040','3515','2056','2041','3516','3501','2208','1424','3502','1250','2059','1251','3503','1252','3504','2149','2332','3477','3478','2333','1253','3322','3505','2334','3506','1429','3507','2336','2337','2163','3509','3491','3327','2338','2164','2061','2165','3494','3521','2532','3495','3217','2350','1246','2533','2534','3496','3218','3523','3482','3219','3524','1751','3497','3498','3525','2415','2537','3499','3484','1754','1800','3485','2355','3527','1755','3330','1087','2610','1756','3331','2357','1817','1757','1803','3332','2343','3333','3489','3334','2553','3222','2600','2347','2408','2601','1390','2349','2541','2603','2542','2604','3227','2543','2559','1821','2544','2545','3229','1761','3534','2546','1104','1763','1825','2548','3355','2194','2561','1655','3404','3230','3343','2562','2623','1769','2092','3344','2196','2563','2624','1596','3232','1658','1643','2565','1644','3348','2185','3541','2567','3542','3543','1589','1773','1836','3253','2438','2439','1777','1664','2570','2571','3090','2450','1779','2572','3242','2451','2452','2574','2635','1790','3243','3258','2453','2010','2454','2576','2637','2011','1308','2577','2013','1309','2578','1474','3247','1855','2381','2442','2579','1781','3249','1476','2383','1321','2590','2445','1478','2003','3261','2446','2592','1785','1846','2004','2386','2593','2655','3263','2448','2580','3264','2449','1326','2657','2007','2596','1789','2009','3267','2598','2599','1679','2124','2586','2464','1482','1055','2291','2126','2022','2127','2310','2588','2466','2293','2311','2129','2312','2313','2469','1331','2314','2281','1046','3271','2316')) and (htlBK in ('20','21','72','78','14','16','17','19','63','65','66','68','59','1','2','42','30','31','32','36','38','39')) order by P limit 0,100


Ключи сделал такие:

tours_details_86_379 0 PRIMARY 1 id A 4903136 BTREE
tours_details_86_379 1 tourid_ind 1 tourid A 108 YES BTREE
tours_details_86_379 1 D_ind 1 D A 132 YES BTREE
tours_details_86_379 1 P_ind 1 P A 44982 YES BTREE
tours_details_86_379 1 htlCN_ind 1 htlCN A 11 YES BTREE
tours_details_86_379 1 htlK_ind 1 htlK A 293 YES BTREE
tours_details_86_379 1 htlBK_ind 1 htlBK A 21 YES BTREE
tours_details_86_379 1 htlRK_ind 1 htlRK A 9 YES BTREE
tours_details_86_379 1 roomNA_ind 1 roomNA A 9 YES BTREE
tours_details_86_379 1 roomNC_ind 1 roomNC A 4 YES BTREE
tours_details_86_379 1 search_index 1 D A 132 YES BTREE
tours_details_86_379 1 search_index 2 aFCK A 132 YES BTREE
tours_details_86_379 1 search_index 3 roomNA A 1149 YES BTREE
tours_details_86_379 1 search_index 4 htlD A 7807 YES BTREE
tours_details_86_379 1 search_index 5 roomNC A 23572 YES BTREE
tours_details_86_379 1 search_index 6 htlRK A 77827 YES BTREE
tours_details_86_379 1 search_index 7 htlK A 1225784 YES BTREE
tours_details_86_379 1 search_index 8 htlBK A 1225784 YES BTREE
tours_details_86_379 1 search_index 9 P A 4903136 YES BTREE

explain говорит:

1 SIMPLE tours_details_86_379 ref D_ind,htlK_ind,htlBK_ind,htlRK_ind,roomNA_ind,roomNC_ind,search_index roomNA_ind 2 const 388589 Using where; Using filesort

---