Query with joins takes too long

I have following query: select p.fofTreeId, p.treeSnapnum, p.x,p.y,p.z from MDR1.FOFMtree as m, MDR1.FOFMtree as p where m.fofTreeId < 100000000+5 and p.fofTreeId between m.fofTreeId and m.mainLeafId

It could be much faster, if first m.fofTreeId < 100000000+5 is used to get a smaller subset of the table before joining. (I tried to enforce this with a subquery, but got a parse error). This is what PaQu is trying, which is very inefficient:

CALL paquExec('SELECT p.fofTreeId AS p.fofTreeId,p.treeSnapnum AS p.treeSnapnum,p.x AS p.x,p.y AS p.y,p.z AS p.z FROM MDR1.FOFMtree AS m JOIN MDR1.FOFMtree AS p ORDER BY NULL ', 'aggregation_tmp_89562445'); CALL paquExec('SELECT p.p.fofTreeId AS p.fofTreeId,p.p.treeSnapnum AS p.treeSnapnum,p.p.x AS p.x,p.p.y AS p.y,p.p.z AS p.z FROM MDR1.FOFMtree AS m JOIN MDR1.FOFMtree AS p JOIN ( SELECT p.fofTreeId,p.treeSnapnum,p.x,p.y,p.z FROM aggregation_tmp_89562445 ) AS p WHERE ( ( m.fofTreeId < 100000000 + 5 ) and ( p.p.fofTreeId between m.fofTreeId and m.mainLeafId ) ) ', 'aggregation_tmp_75999751'); CALL paquDropTmp('aggregation_tmp_89562445'); USE spider_tmp_shard; SET @i=0; CREATE TABLE multidark_user_kristin./*@GEN_RES_TABLE_HERE*/ ENGINE=MyISAM SELECT @i:=@i+1 AS row_id, distinct_res_table.* FROM ( SELECT DISTINCT p.fofTreeId,p.treeSnapnum,p.x,p.y,p.z FROM aggregation_tmp_75999751 ) as distinct_res_table; CALL paquDropTmp('aggregation_tmp_75999751');

adrpar / paqu

Query with joins takes too long #13