Subquery vs. L.O. Join ... or, I suck at SQL ;)

dalecosp

My SQL is, like several other things I do, pretty bad.

Can someone clarify this:

mysql> explain select id from my_listings where id not in(select id from weekly_views);
+----+--------------------+-----------------+-----------------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type        | table           | type            | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+--------------------+-----------------+-----------------+---------------+---------+---------+------+-------+--------------------------+
|  1 | PRIMARY            | my_listings     | index           | NULL          | PRIMARY | 4       | NULL | 33176 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | weekly_views    | unique_subquery | PRIMARY       | PRIMARY | 4       | func |     1 | Using index              |
+----+--------------------+-----------------+-----------------+---------------+---------+---------+------+-------+--------------------------+

mysql> explain select my_listings.id from my_listings left outer join weekly_views on my_listings.id!=weekly_views.id where my_listings.live=1;
+----+-------------+----------------+-------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |
+----+-------------+----------------+-------+---------------+---------+---------+-------+-------+-------------+
|  1 | SIMPLE      | my_listings    | ref   | live          | live    | 1       | const | 30542 |             |
|  1 | SIMPLE      | weekly_views   | index | NULL          | PRIMARY | 4       | NULL  | 30466 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+-------+-------+-------------+

The subquery is fast and the LEFT JOIN never returns. Did I screw up the LEFT JOIN? (It kinda seems obvious that I did).

I'd like this to scale to 100K listings or more.... :queasy:

Suggestions? (and no, "find another job" isn't currently acceptable :p )

bradgrafelman

Can you explain (in words) what data it is you're after?

dalecosp

Sure. I have a table of items for sale with "id" as a primary key.

I'm starting a new feature where we'll track hits on said items on a weekly basis. I've added a table in the DB to do this, because I don't see a Good Way(tm) to get this functionality from our existing software.

New items could be added at any time, so I want to, every hour or so, see if any new listings have been added (which is not a problem). The SQL is designed to add any new listing ID's that are not already in the weekly_views table into the weekly_views table --- IOW, it's basically just a "diff" between the two tables, looking for ID's that have been added to one but not the other.

I'm sure that I could've figured out a PHP way to do this, but I've been attempting to stretch my SQL-fu. The subquery should work, I think, I was just curious why in the world the LOJ, which EXPLAINS just fine and fairly simply, takes so long. I suppose that 928M records "EXPLAINS" a bit ... but I apparently don't understand the LOJ, as I thought it was exclusive instead of inclusive....

johanafm

t1
LEFT JOIN t2 ON t1.id != t2.id

Assuming id is unique in t1 and id is unique in t2, there is at most one matching id in t2 for each id in t1, which means that each row in t1 will at least be joined to all but 1 rows in t2. Thus, you will end up with 30542 * 30466 > 930 million rows.

So, to do this using a join, you'd still need to left join on t1.id = t2.id and add a HAVING t2.id IS NULL, thus discarding all rows that have a matching id in t2 after the query has retrieved the results.