Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make this SQL query using IN (with many numeric IDs) more efficient?

I've been waiting over an hour already for this query, so I know I'm probably doing something wrong. Is there efficient way to tailor this query: ?

select RespondentID, MIN(SessionID) as 'SID'
from BIG_Sessions (nolock)
where RespondentID in (
1418283,
1419863,
1421188,
1422101,
1431384,
1435526,
1437284,
1441394,
/* etc etc THOUSANDS */
1579244 )
    and EntryDate between
    '07-11-2011' and '07-31-2012'
GROUP BY RespondentID 

I kknow that my date range is pretty big, but I can't change that part (the dates are spread all over) .

Also, the reason for MIN(SessionID) is because otherwise we get many SessionID's for each Respondent, and one suffices(it's taking MIN on an alphanumeric ID like ach2a23a-adhsdx123... and getting the first alphabetically)

Thanks

like image 469
Caffeinated Avatar asked Jul 31 '12 22:07

Caffeinated


2 Answers

  1. Put your thousands of numbers in a temporary table.
  2. Index the number field in that table.
  3. Index the RespondentID field in BIG_SESSIONS
  4. Join the two tables

eg:

select RespondentID, MIN(SessionID) as 'SID' 
from BIG_Sessions (nolock) 
    inner join RespondentsFilterTable 
        on BIG_SESSIONS.RespondentID = RespondentsFilterTable.RespondentID
where EntryDate between '07-11-2011' and '07-31-2012' 
GROUP BY BIG_Sessions.RespondentID

You could add indexes to EntryDate and SessionID as well, but if you're adding to big_sessions frequently, this could be counter productive elsewhere

In general, you can can get hints of how performance of a query can be improved by studying the estimated (or if possible actual) execution plans.

like image 185
podiluska Avatar answered Sep 18 '22 03:09

podiluska


If the smallest and largest ids in the IN statement are known beforehands and depending on how many ids are in the table then adding a respondedID > [smallest_known_id-1] AND respondedID < [largest_known_id+1] prior to the IN statement would help limiting the problem

like image 25
gts Avatar answered Sep 17 '22 03:09

gts