If I run the following queries each one returns quickly (0.01 sec) and gives me my desired result.
SELECT tagId FROM tag WHERE name='programming'
SELECT COUNT(DISTINCT workcode) FROM worktag WHERE tagId=123 OR tagId=124
(assume the two tagId numbers were the results from the first query)
I would like to combine these queries so I only have to run it once:
SELECT COUNT(DISTINCT workcode) FROM worktag WHERE tagId IN (SELECT tagId FROM tag WHERE name='programming')
However this query completes in about 1 min and 20 sec. I have indexes on worktag.workcode
, worktag.tagId
, tag.tagId
, and tag.name
.
If I run DESCRIBE
on the queries the first two use the indexes and the second one uses the index for the subquery (on the tag
table) but doesn't use any indexes on the worktag
table.
Does anyone know why this might be?
NOTE: the worktag
table has over 18 million records in it.