Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The <a href="https://stackoverflow.com/questions/845255/better-method-for-matching-id-sets-in-mysql-statement/845331#845331">answer</a> given by Andomar using <code>GROUP BY</code> and <code>HAVING</code> is a common way to solve this kind of problem, but it doesn't perform very well. The <code>GROUP BY</code> often causes a temporary table.</p> <p>Back to your example:</p> <blockquote> <p>...AND ((id = '25' AND id = '566') OR (id = '156' AND id = '166') OR (id = '7345'))</p> </blockquote> <p>This can never be true. The conditions in a <code>WHERE</code> clause apply to <em>one</em> row at a time. The <code>id</code> column can never have two values on a given row. I understand conceptually what you are intending to test for, but this isn't the way SQL works.</p> <p>When you need to write a condition that involves values occurring on multiple rows, another solution is to use a <strong>self-join</strong>. </p> <pre><code>SELECT * FROM ArticleKeywords k1 LEFT OUTER JOIN ArticleKeywords k2 ON (k1.article_id = k2.article_id) WHERE k1.keyword_id = '7345' OR (k1.keyword_id = '25' AND k2.keyword_id = '566') OR (k1.keyword_id = '156' AND k2.keyword_id = '166'); </code></pre> <p>Most people would call <code>k1</code> and <code>k2</code> "table aliases." But if you think of these aliases as pointing to individual rows in the table, instead, then it becomes clearer how to write conditions using self-joins.</p> <p>Of course if you need to test for a small group of three values instead of two values, you need to do another self-join.</p> <p>This solution may not be able to use indexes efficiently, but it won't incur the temporary table that the <code>GROUP BY</code> solution does. Try both solutions, use <a href="http://dev.mysql.com/doc/refman/5.1/en/explain.html" rel="nofollow noreferrer"><code>EXPLAIN</code></a> to analyze them, and then measure their performance to compare.</p>
    singulars
    1. This table or related slice is empty.
    1. PO'Better' Method for matching ID sets in MySQL statement
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USBill Karwin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COBill, this gets cumbersome when he doesn't know the number of keywords (and hence the number of joins). You are right about the group-by cost, which is why I suggested adding a where clause filter with Andomar's solution. Assuming a decent optimizer, the where should get pushed down, returning very few keywords, and the join and group by can be performed cheaply.
      singulars
      1. PO
      1. USSquareCog
    2. CO@SquareCog: Fair enough. Just offering this as an alternative method to keep in your toolbox. :)
      singulars
      1. PO
      1. USBill Karwin
    3. CO@Bill Karwin: I was thinking about a solution that involves a "Search" table (SearchId INT, KeywordId INT). Each search condition group would be entered there, e.g. ((1, 25), (1,566)). This could be joined to ArticleKeywords to find the ArticleIds that satisfy search #1. However, I'm struggling with writing a JOIN that does that - is this at all possible?
      singulars
      1. PO
      1. USTomalak
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload