Have Geek Brain

Quickly select all rows with "1 or more" matching rows in another table

I would like to select all the rows from one table that most effectively match "one or more" rows in another table.

SELECT identity.id FROM identity
INNER JOIN task ON
  task.identityid=identity.id
  AND task.groupid IN (78, 122, 345, 12, 234, 778, 233, 123, 33)

Currently, if there are multiple mapping tasks, this will return the same ID multiple times (but ineffectively degrading performance when resolving them later). I would like this to instead return just one row for each id that matches one or more of these task groups, and I was wondering if there was a more efficient way than doing DISTINCT or GROUP BY.

The problem with doing DISTINCT or GROUP BY is that the task table is still checked for all groupid matches, after which they are subsequently reduced to one using a temporary table (sometimes with a file phone). I would prefer it to do some kind of short circuit evaluation - don't pursue further subsequent task matches for the same identity after it has found it.

I was thinking about doing the EXISTS subquery, but I don't know how they are optimized. I need it to join the task table first, before the identity table, so I don't do a full scan of the identity table, which is very large and will have a lot of inconsistencies.

+1


a source to share


3 answers


Just using "SELECT DISTINCT" with what you need to be effective in mysql. You may need to put your values ​​in a table and join it instead of using "IN (...)".



+1


a source


Does MYSQL support TOP N syntax? If yes:



SELECT TOP 1 identity.id FROM identity
INNER JOIN task ON
  task.identityid=identity.id
  AND task.groupid IN (78, 122, 345, 12, 234, 778, 233, 123, 33)
0


a source


Exists should only do a fine for you if the column you are comparing in the subquery is indexed.

I would have expected the creature to perform slightly better than mix and group, but I would have to try this to be sure. I've come across enough experience in MySQL where my prediction was wrong to know what was worth trying.

0


a source






More articles:


All Articles