Hi,
this is the solution I came up with, that is over 10 times faster than my
first attemps.
Tested @31,871 entries in table 'picture' and 222,712 entries in table
'picture_attrib_rel'.
Old Version:
SELECT * FROM picture as p
INNER JOIN picture_attrib_rel as pr1
ON (p.pid = pr1.pid)
INNER JOIN picture_attrib_rel as pr2
ON (p.pid = pr2.pid and pr2.val_int < 1500)
WHERE pr1.aid = 2 AND pr1.val_int >= 1500
AND pr2.aid = 5 AND pr2.val_int < 1000
Takes about 1.9 Seconds on average to return.
The version with temporary tables:
DROP temporary table if exists tmp_size;
DROP temporary table if exists tmp_qi;
CREATE temporary table tmp_size
SELECT pid FROM picture_attrib_rel
WHERE aid = 2 AND val_int >= 1500;
CREATE temporary table tmp_qi
SELECT pid FROM picture_attrib_rel
WHERE aid = 5 AND val_int < 1000;
SELECT pid,uid FROM tmp_size JOIN tmp_qi USING(pid) JOIN pictures
USING(pid);
DROP temporary table if exists tmp_size;
DROP temporary table if exists tmp_qi;
This takes 0.12 seconds to return, which is quite bearable for now.
Thanks again for all your input!
Regards,
Jan
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
