5

Consider the following queries:

mysql> SELECT count(*) FROM list l WHERE l.source='blink';
+----------+
| count(*) |
+----------+
|     3372 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----------+
| count(*) |
+----------+
|     5116 |
+----------+
1 row in set (2.47 sec)

There were no INSERT or other queries run on the database between these two queries running. The second query seems more restrictive than the first query, so how could it return more rows? Note that the list table has over 2 million rows, and the cardinal table has about half a million rows.

EDIT: Adding EXPLAIN output:

mysql> EXPLAIN SELECT count(*) FROM list l LEFT JOIN cardinal c ON l.id=c.id WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink';
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref         | rows   | Extra       |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | c     | ALL    | NULL              | NULL    | NULL    | NULL        | 536258 | Using where |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY,id,source | PRIMARY | 8       | direct.c.id |      1 | Using where |
+----+-------------+-------+--------+-------------------+---------+---------+-------------+--------+-------------+
2 rows in set (0.00 sec)

mysql> select count(*) from list;
+----------+
| count(*) |
+----------+
|  2165664 |
+----------+
1 row in set (1.32 sec)

mysql> select count(*) from cardinal;
+----------+
| count(*) |
+----------+
|   537007 |
+----------+
1 row in set (0.23 sec)
  • Thank you Phil, but the WHERE clause of l.source='blink' still applies to the left table in the second query. – dotancohen Dec 9 '13 at 13:22
  • Testing with INNER JOIN gives the same result of 5116. – dotancohen Dec 9 '13 at 13:26
  • Sorry, I'm not awake yet and the answer is obvious. It's a 1<>many relationship between the two tables – Philᵀᴹ Dec 9 '13 at 13:31
  • I'm brewing coffee :) I'm not seeing how adding more restrictions increases the results size. Both queries are restricted to WHERE l.source='blink'. The second query is additionally restricted to WHERE c.resolution IN ('Left','Right','Up'). I don't think that the JOIN will add records as it is a LEFT or INNER JOIN (I tried both, same result). – dotancohen Dec 9 '13 at 13:37
7

Each row in the first table may match more than one row in the second table.

You can check this with:

SELECT l.id,count(*) 
FROM list l 
LEFT JOIN cardinal c ON l.id=c.id 
WHERE c.resolution IN ('Left','Right','Up') AND l.source='blink' 
group by l.id 
having count(*)>1;

A little demo is on SQL Fiddle here.

| improve this answer | |
3

Your problem is not linked to you adding restrictions but with the JOIN.

When a row from table l has for instance 3 corresponding rows in c, three rows would be added to the result set.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.