MySQL Forums
Forum List  »  Newbie

Re: Opinions on this schema please =)
Posted by: Rick James
Date: February 23, 2009 09:13AM

Two SELECTs versus one -- maybe a millisecond difference. Not noticeable. If your database is on the other side of the country from the front end, now you are talking about 10s of milliseconds difference.

On a similar note -- don't be afraid to CREATE TEMPORARY TABLE; this can make the queries simpler to read and (if needed) give you a chance to add an INDEX to it.

UNION -- a good way to do multiple queries in one shot.

OR -- This usually reverts to a table scan. :( The solution is to use UNION.

select ... from properties WHERE
    (properties.pname = 'property_1' and properties.pval = 'value1') OR
    (properties.pname = 'property_2' and properties.pval = 'value1') OR
    (properties.pname = 'property_3' and properties.pval = 'value1') OR)
becomes
( select iid from properties WHERE
    properties.pname = 'property_1' and properties.pval = 'value1'
  UNION DISTINCT
  select iid from properties WHERE
    properties.pname = 'property_2' and properties.pval = 'value1'
  UNION DISTINCT
  select iid from properties WHERE
    properties.pname = 'property_3' and properties.pval = 'value1'
) p
If you can be sure of no dup iid's, switch to UNION ALL (to avoid a pass for DISTINCT).

"One for the articles and one for the items." -- again, UNION can be used.

I have one UI that invokes a SELECT that is the UNION of 50 SELECTs; the performance is acceptable.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.