Slow LEFT JOIN!

[Sasha Mitich]

I have two tables

CREATE TABLE `Fin2011` (
`FK_Id` int(11) NOT NULL AUTO_INCREMENT,
`FK_Kto` varchar(10) DEFAULT NULL,
`FK_Date` date DEFAULT NULL,
`FK_D` decimal(15,3) DEFAULT '0.000',
`FK_P` decimal(15,3) DEFAULT '0.000',
PRIMARY KEY (`FK_Id`),
KEY `fk_kto` (`FK_Kto`),
KEY `fk_date` (`FK_Date`)
) ENGINE=InnoDB AUTO_INCREMENT=31259 DEFAULT CHARSET=utf8$$

'FZC.Fin2011', 'analyze', 'status', 'OK'

CREATE TABLE `FkdIkp` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Kto` varchar(10) CHARACTER SET latin1 NOT NULL,
`KtoName` varchar(120) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `kto` (`Kto`),
KEY `ktoname` (`KtoName`)
) ENGINE=InnoDB AUTO_INCREMENT=18302 DEFAULT CHARSET=utf8


'FZC.FkdIkp', 'analyze', 'status', 'OK'


I want simply to make sum on FK_P and FK_D column form first table grouped by fk_kto, append name from second and display result.

Here is first try:

select fk_kto, sum(fk_d), sum(fk_p), KtoName
from Fin2011, FkdIkp where fk_kto=kto group by fk_kto limit 10000

Explain
id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;SIMPLE;FkdIkp;ALL;NULL;NULL;NULL;NULL;17734;"Using temporary; Using filesort"
1;SIMPLE;Fin2011;ref;fk_kto;fk_kto;32;func;13;"Using where"

Fetched 1441 records Duration: 0.015sec Fethced in:0.0000
Perfect!!! I get a result in practicaly zero time!

But, some of keys from first table (fk_kto column) missing in the second, so they missing in result set! It should be easy! I'll make LEFT JOIN

Second try - small modification and LEFT JOIN:

select a.*, k.ktoname from (select fk_kto, sum(fk_d), sum(fk_p), from Fin2011 group by fk_kto) a
left join FkdIkp k on fk_kto=kto limit 10000

Explain:
id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;PRIMARY;<derived2>;ALL;NULL;NULL;NULL;NULL;1712;
1;PRIMARY;k;ALL;NULL;NULL;NULL;NULL;17734;
2;DERIVED;Fin2011;index;NULL;fk_kto;32;NULL;31025;

Fetched 1712 records Duration: 8.720sec Fethced in:58.423sec!!!!

OK. I've got the correct result set. 1712 records. But for 58.423 secs!!!
Disaster!!!! Almost a minute! There could be problem with subquery?

Third try - avoid LEFT JOIN

select a.*, k.ktoname from (select fk_kto, sum(fk_d), sum(fk_p), from Fin2011 group by fk_kto) a join FkdIkp k on fk_kto=kto limit 10000

Explain
id;select_type;table;type;possible_keys;key;key_len;ref;rows;Extra
1;PRIMARY;<derived2>;ALL;NULL;NULL;NULL;NULL;1712;
1;PRIMARY;k;ALL;NULL;NULL;NULL;NULL;17734;"Using where; Using join buffer"
2;DERIVED;Fin2011;index;NULL;fk_kto;32;NULL;31025;

Fetched 1441 records Duration: 1.060sec Fethced in:9.953sec

Wrong result (only 1441 records) but 6 time faster! And yet much, much slower then first try and get the same result!!!

So, subquery is the part of problem. The second part is LEFT JOIN witch produce incredibly slow query!

Variable_name;Value
bulk_insert_buffer_size; 8388608
innodb_buffer_pool_size; 8388608
innodb_log_buffer_size; 1048576
join_buffer_size; 131072
key_buffer_size; 16777216
myisam_sort_buffer_size; 8388608
net_buffer_length; 16384
preload_buffer_size; 32768
read_buffer_size; 131072
read_rnd_buffer_size; 262144
sort_buffer_size; 2097144
sql_buffer_result; OFF


What I (we) could do about that?

Any solutions?


P.S. The thing is that I try to move data from Firebird to MySQL. On many other aspects MySQL is faster than Firebird. But, this works on Firebird without problem and on MySQL I have unexpected problem!



Edited 1 time(s). Last edit at 11/22/2011 01:48AM by Sasha Mitich.