I also tried this (printf1 in irc suggested "folding" the joins) :

SELECT
w8.wid,
w8.variant,
w8.num_variants,
sum_text(w8.unicode) as unicodes,
sum_text(w8.pinyin) as pinyins
FROM
(words as w8 JOIN
(words as w7 JOIN
(words as w6 JOIN
(words as w5 JOIN
(words as w4 JOIN
(words as w3 JOIN
(words as w2 JOIN
(words as w0 JOIN words as w1

ON(w1.wid = w0.wid AND w1.variant = w0.variant AND w1.sequence = w0.sequence + 1 AND w1.pinyin LIKE 'fu_'))
ON(w2.wid = w1.wid AND w2.variant = w1.variant AND w2.sequence = w1.sequence + 1 AND w2.pinyin LIKE 'ji_'))
ON(w3.wid = w2.wid AND w3.variant = w2.variant AND w3.sequence = w2.sequence + 1 AND w3.pinyin LIKE 'guan_'))
ON(w4.wid = w3.wid AND w4.variant = w3.variant AND w4.sequence = w3.sequence + 1 AND w4.pinyin LIKE 'kai_'))
ON(w5.wid = w4.wid AND w5.variant = w4.variant AND w5.sequence = w4.sequence + 1 AND w5.pinyin LIKE 'fang_'))
ON(w6.wid = w5.wid AND w6.variant = w5.variant AND w6.sequence = w5.sequence + 1 AND w6.pinyin LIKE 'xi_'))
ON(w7.wid = w6.wid AND w7.variant = w6.variant AND w7.sequence = w6.sequence + 1 AND w7.pinyin LIKE 'tong_'))
ON(w8.wid = w7.wid AND w8.variant = w7.variant))



WHERE w0.wid > 0 AND w0.pinyin = 'zheng4' AND w0.def_exists = 't' AND w0.sequence = 0 GROUP BY w8.wid, w8.variant, w8.num_variants, w8.page_order, w0.sequence , w1.sequence , w2.sequence , w3.sequence , w4.sequence , w5.sequence , w6.sequence , w7.sequence ORDER BY w8.page_order;


this gets teh time down to 800ms (not too shabby..).. and as a prepared statement, it only takes 15ms!!! i am hopeful there is a way to totally bypass most of this overhead.. but i need more help :\


_________________________________________________________________
MSN Toolbar provides one-click access to Hotmail from any Web page FREE download! http://clk.atdmt.com/AVE/go/onm00200413ave/direct/01/



---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Reply via email to