optimize mysql query

[andy susanto]

hai,

i have a problem query that query will 40213 record with innodb engine,

but this query take times about more or less 1 minute.

i am using my.ini standard, i try to configure it, but it is killing me, because i am doing try and try again. is there any guide, i browse to inet i got a calculate mysql but still i can not solve it.

this my explain result

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL \N \N \N \N 40213
1 PRIMARY <derived10> ALL \N \N \N \N 1
1 PRIMARY <derived11> ALL \N \N \N \N 1
11 DERIVED a index PRIMARY TRANSWAREHOUSEMASTER_IDXTOWAREHOUSE 52 \N 1 Using index; Using temporary; Using filesort
11 DERIVED b ref PRIMARY PRIMARY 42 accounting.a.TRANSWAREHOUSEMASTER_NO 1
10 DERIVED a index PRIMARY TRANSWAREHOUSEMASTER_IDXFROMWAREHOUSE 52 \N 1 Using index; Using temporary; Using filesort
10 DERIVED b ref PRIMARY PRIMARY 42 accounting.a.TRANSWAREHOUSEMASTER_NO 1
2 DERIVED a ref ITEM_IDXITEMGROUPID ITEM_IDXITEMGROUPID 5 20270 Using where; Using temporary; Using filesort
2 DERIVED <derived4> ALL \N \N \N \N 5
4 DERIVED <derived5> ALL \N \N \N \N 5 Using temporary; Using filesort
5 DERIVED a ALL PRIMARY \N \N \N 1 Using temporary; Using filesort
5 DERIVED b eq_ref PRIMARY PRIMARY 42 accounting.a.ADJUSTMENTDETAIL_ADJUSTMENTMASTERNO 1
6 UNION a ALL PRIMARY \N \N \N 1 Using temporary; Using filesort
6 UNION b eq_ref PRIMARY PRIMARY 42 accounting.a.PIDETAIL_PIMASTERNO 1
7 UNION a ALL PRIMARY \N \N \N 1 Using temporary; Using filesort
7 UNION b eq_ref PRIMARY,UQ_SIMASTER_SIMASTERNO PRIMARY 42 accounting.a.SIDETAIL_SIMASTERNO 1
8 UNION a ALL PRIMARY \N \N \N 1 Using temporary; Using filesort
8 UNION b eq_ref PRIMARY PRIMARY 42 accounting.a.SRDETAIL_SRMASTERNO 1
9 UNION a ALL PRIMARY \N \N \N 1 Using temporary; Using filesort
9 UNION b eq_ref PRIMARY PRIMARY 42 accounting.a.PRDETAIL_PRMASTERNO 1
\N UNION RESULT <union5,6,7,8,9> ALL \N \N \N \N \N Using filesort
3 UNION a index ITEM_IDXITEMGROUPID PRIMARY 17 \N 40541 Using where
\N UNION RESULT <union2,3> ALL \N \N \N \N \N

ps :
mysql 5.1.34
mysql JDBC 5.1.7
pentium 4 3.20 GHZ and RAM 256


Regards,

Andy Susanto



Edited 1 time(s). Last edit at 06/27/2009 07:21PM by andy susanto.