Re: [PHP] SQL Syntax [improved SQL]

[Jan Reiter]

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

RE: [PHP] SQL Syntax [improved SQL]

[Tommy Pham]

 -Original Message-
 From: Jan Reiter [mailto:the-fal...@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


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php