StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Ok, I tested this. The queries from best to worst were:</p> <p><strong>Query 1: Joins (0.016s; basically <em>instant</em>)</strong></p> <pre><code>SELECT p.id, name FROM parent p JOIN relationship r1 ON p.id = r1.parent_id AND r1.other_id = 100 JOIN relationship r2 ON p.id = r2.parent_id AND r2.other_id = 101 JOIN relationship r3 ON p.id = r3.parent_id AND r3.other_id = 102 JOIN relationship r4 ON p.id = r4.parent_id AND r4.other_id = 103 </code></pre> <p><strong>Query 2: EXISTS (0.625s)</strong></p> <pre><code>SELECT id, name FROM parent p WHERE EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND other_id = 100) AND EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND other_id = 101) AND EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND other_id = 102) AND EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND oth </code></pre> <p><strong>Query 3: Aggregate (1.016s)</strong></p> <p>SELECT p.id, p.name FROM parent p WHERE (SELECT COUNT(*) FROM relationship WHERE parent_id = p.id AND other_id IN (100,101,102,103))</p> <p><strong>Query 4: UNION Aggregate (2.39s)</strong></p> <pre><code>SELECT id, name FROM ( SELECT p1.id, p1.name FROM parent AS p1 LEFT JOIN relationship as r1 ON(r1.parent_id=p1.id) WHERE r1.other_id = 100 UNION ALL SELECT p2.id, p2.name FROM parent AS p2 LEFT JOIN relationship as r2 ON(r2.parent_id=p2.id) WHERE r2.other_id = 101 UNION ALL SELECT p3.id, p3.name FROM parent AS p3 LEFT JOIN relationship as r3 ON(r3.parent_id=p3.id) WHERE r3.other_id = 102 UNION ALL SELECT p4.id, p4.name FROM parent AS p4 LEFT JOIN relationship as r4 ON(r4.parent_id=p4.id) WHERE r4.other_id = 103 ) a GROUP BY id, name HAVING count(*) = 4 </code></pre> <p>Actually the above was producing the wrong data so it's either wrong or I did something wrong with it. Whatever the case, the above is just a bad idea.</p> <p>If that's not fast then you need to look at the explain plan for the query. You're probably just lacking appropriate indices. Try it with:</p> <pre><code>CREATE INDEX ON relationship (parent_id, other_id) </code></pre> <p>Before you go down the route of aggregation (SELECT COUNT(*) FROM ...) you should read <a href="https://stackoverflow.com/questions/477006/sql-statement-join-vs-group-by-and-having/477013#477013">SQL Statement - “Join” Vs “Group By and Having”</a>.</p> <p><strong>Note:</strong> The above timings are based on:</p> <pre><code>CREATE TABLE parent ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE other ( id INT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE relationship ( id INT PRIMARY KEY, parent_id INT, other_id INT ); CREATE INDEX idx1 ON relationship (parent_id, other_id); CREATE INDEX idx2 ON relationship (other_id, parent_id); </code></pre> <p>and nearly 800,000 records created with:</p> <pre><code><?php ini_set('max_execution_time', 600); $start = microtime(true); echo "<pre>\n"; mysql_connect('localhost', 'scratch', 'scratch'); if (mysql_error()) { echo "Connect error: " . mysql_error() . "\n"; } mysql_select_db('scratch'); if (mysql_error()) { echo "Selct DB error: " . mysql_error() . "\n"; } define('PARENTS', 100000); define('CHILDREN', 100000); define('MAX_CHILDREN', 10); define('SCATTER', 10); $rel = 0; for ($i=1; $i<=PARENTS; $i++) { query("INSERT INTO parent VALUES ($i, 'Parent $i')"); $potential = range(max(1, $i - SCATTER), min(CHILDREN, $i + SCATTER)); $elements = sizeof($potential); $other = rand(1, min(MAX_CHILDREN, $elements - 4)); $j = 0; while ($j < $other) { $index = rand(0, $elements - 1); if (isset($potential[$index])) { $c = $potential[$index]; $rel++; query("INSERT INTO relationship VALUES ($rel, $i, $c)"); unset($potential[$index]); $j++; } } } for ($i=1; $i<=CHILDREN; $i++) { query("INSERT INTO other VALUES ($i, 'Other $i')"); } $count = PARENTS + CHILDREN + $rel; $stop = microtime(true); $duration = $stop - $start; $insert = $duration / $count; echo "$count records added.\n"; echo "Program ran for $duration seconds.\n"; echo "Insert time $insert seconds.\n"; echo "</pre>\n"; function query($str) { mysql_query($str); if (mysql_error()) { echo "$str: " . mysql_error() . "\n"; } } ?> </code></pre> <p>So once again joins carry the day.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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