RE: [PHP] SQL Syntax [improved SQL]

> -----Original Message----- > From: Jan Reiter [mailto:the-fallen@gmx.net] > Sent: Wednesday, June 16, 2010 8:55 AM > To: php-general@lists.php.net > Subject: Re: [PHP] SQL Syntax [improved SQL] > > 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 Jan, What do you get from this query and how fast does it execute? SELECT * FROM picture_attrib_rel par INNER JOIN pictures p ON p.pid = par.pid WHERE (par.aid = 2 AND par.val_int >= 1500) OR (par.aid = 5 AND par.val_int < 1000) Regards, Tommy