mysql query hang

[hikaro]

hello,

i'm working on mysql code now and found some problem in it, i got this query

INSERT INTO voice
SELECT
        v.*,
        COUNT(DISTINCT h.date)-1 AS history0,
        h1.mobile AS history_1,
        h3.mobile AS history_3,
        h7.mobile AS history_7,
        h14.mobile AS history_14
FROM world v
LEFT JOIN id4_history h ON h.vid = v.vid AND h.mobile >= v.mobile
LEFT JOIN id4_history h1 ON h1.vid = v.vid AND h1.date < DATE_SUB(NOW(), INTERVAL 1 DAY) AND h1.date > DATE_SUB(NOW(), INTERVAL 2 DAY)
LEFT JOIN id4_history h3 ON h3.vid = v.vid AND h3.date < DATE_SUB(NOW(), INTERVAL 3 DAY) AND h3.date > DATE_SUB(NOW(), INTERVAL 4 DAY)
LEFT JOIN id4_history h7 ON h7.vid = v.vid AND h7.date < DATE_SUB(NOW(), INTERVAL 7 DAY) AND h7.date > DATE_SUB(NOW(), INTERVAL 8 DAY)
LEFT JOIN id4_history h14 ON h14.vid = v.vid AND h14.date < DATE_SUB(NOW(), INTERVAL 14 DAY) AND h14.date > DATE_SUB(NOW(), INTERVAL 15 DAY)
GROUP BY v.vid;

from what i see there's no miss-spelled or something wrong with the code, perhaps any expert here may check it for me the code.
thank you

• hikaro -

[Bjom]

well it's not strictly "wrong" but usually < or > in the ON part of the JOIN clause leads to massive bloated results and seldom to the ones you really wanted.

Are you sure that you dont mean
LEFT JOIN id4_history h ON h.vid = v.vid WHERE h.mobile >= v.mobile

instead of

LEFT JOIN id4_history h ON h.vid = v.vid AND h.mobile >= v.mobile?

Bjom

[hikaro]

wow thats fast reply Bjom!

Yes, i'm pretty sure about the code. when i run it, it just hang and i see from top output on shell bash, mysql using 100&#37; cpu usage.

[Bjom]

get rid of > and < from the join statements. trust me, that's not what you want. This will link a couple of thousand rows to an other row producing a table with millions of rows. That's why your result is : "mysql using 100% cpu usage".