SELECTING with multiple WHERE conditions on same column must exist simultaneously

Question

I have seen many posts but didn't get my answer i have a table with structure

 subject_level_id  | tutor_id  | level_id_fk  | subject_id_fk |
  118              | 99        | 4            | 1             |
  119              | 99        | 3            | 2             |
  120              | 99        | 3            | 3             |
  121              |100        | 3            | 1             | 
  122              |100        | 4            | 2             |

I want to extract tutor id's of tutors which teach 1, 2 or more subjects of a particular level( number of subjects depend upon user selection of subjects there can be but level will be unique for aparticular query)

I have worked out a query with help from other posts but it is very slow and gets worse if number of subjects are more than two because I have 10,000 records of tutors my query is:

select distinct
   a.tutor_id
from
   tutor_preferred_level_subject as a
inner join
   tutor_preferred_level_subject as b
   on a.level_id_fk = b.level_id_fk
where
   a.subject_id_fk = 1 and
   b.subject_id_fk = 10 and
   a.level_id_fk = 3

Suggestion: SELECTING with multiple WHERE conditions on same column

The difference in this question and mine is that I want the tutors who teach exactly both the subjects.

update: yes main concern is performance, this query is working fine for two subjects but for more than two subjects mysql server keeps on processing forever. The index is subject_level_id and there are almost 13000 records in it and the below query returns around 6500 results.

as Jakub Sacha demanded these are the results of

EXPLAIN select distinct
   a.tutor_id
from
   tutor_preferred_level_subject as a
inner join
   tutor_preferred_level_subject as b
   on a.level_id_fk = b.level_id_fk
where
   a.subject_id_fk = 1 and
   b.subject_id_fk = 10 and
   a.level_id_fk = 3



id  select_type     table   type    possible_keys   key     key_len     ref|
1   SIMPLE          a   ALL     NULL            NULL    NULL            NULL 
1   SIMPLE          b   ALL     NULL            NULL    NULL           NULL 

rows    Extra 
12733   Using where; Using temporary
12733   Using where; Distinct

Is your concern over the performance of this query? I don't see anything wrong with it syntax-wise... Does the table have any indexes and how many records are in it? — , Jan 03 '13 at 07:27

ypercubeᵀᴹ · Accepted Answer · 2013-01-03T08:36:55.360

Your query does not seem to be doing what you describe. The join should be on tutor_id and not on level_id_fk.

You need the DISTINCT only if the (level_id_fk, subject_id_fk, tutor_id) combination is not unique:

SELECT                              
    -- DISTINCT
   a.tutor_id
FROM
   tutor_preferred_level_subject AS a
INNER JOIN
   tutor_preferred_level_subject AS b
       ON a.tutor_id = b.tutor_id
WHERE
   a.subject_id_fk = 1 AND
   a.level_id_fk = 3 AND
   b.subject_id_fk = 10 AND
   b.level_id_fk = 3 ;

Additionally, you should add a (unique) index on (level_id_fk, subject_id_fk, tutor_id) for efficiency:

ALTER TABLE tutor_preferred_level_subject
  ADD INDEX level_subject_tutor_IDX
    (level_id_fk, subject_id_fk, tutor_id) ;

thank u so much u made my day!! sorry for late response.god bless u. — irohit786, Jan 03 '13 at 13:36

fthiella · Answer 2 · 2013-01-03T09:05:06.010

1

This will select all tutor_id that have exactly two distinct subjects that matches (1, 10):

SELECT
  Distinct tutor_id
FROM
  tutor_preferred_level_subject 
WHERE
  level_id_fk = 3
GROUP BY tutor_id, level_id_fk
HAVING
  Count(Distinct subject_id_fk) = 2 AND
  Sum(subject_id_fk NOT IN (1, 10)) = 0

edited Jan 03 '13 at 09:05

answered Jan 03 '13 at 08:08

fthiella

48,073
15
90
106

this query will also give results that have subject_id 1 and any othe subject_id for example 1 and 4 or 6 and 10 – irohit786 Jan 03 '13 at 08:14
@irohit786 edited, this will find only results that have exactly 1, 10 – fthiella Jan 03 '13 at 08:23

score 0 · Answer 3 · answered Jan 03 '13 at 07:30

0

It doesn't seem that you need to inner join the table to itself, that is what will be causing the slowness as you get more and more rows.

You say you need "tutors which teach 1, 2 or more subjects of a particular level" which basically just means you need tutors that teach that particular level. So try grouping and using the HAVING clause (this will require that your data is clean of course)

SELECT a.tutor_id
FROM tutor_preferred_level_subject AS a
WHERE a.subject_id_fk IN (1,10)
  AND a.level_id_fk = 3
GROUP BY a.tutor_id
HAVING COUNT(1) > 1

answered Jan 03 '13 at 07:30

Matt Dodge

10,833
7
38
58

yes i need the tutor_id only but only those tutors must teach the selected subjects the above query will give which teach any of the subjects of level 3 i want the tutors which must teach the subjects with subject id 1 and 10 (both of these subjects) – irohit786 Jan 03 '13 at 07:39
This query does what you ask, that is why the `IN (1,10)` is there. Assuming you have no duplicate subject IDs it should work. Did you try to execute it? – Matt Dodge Jan 03 '13 at 07:45
yes i have duplicate subject ids only the `subject_level_id` is unque nothing else, yes i did execute it.sorry it doesnot give what i require – irohit786 Jan 03 '13 at 08:17

SELECTING with multiple WHERE conditions on same column must exist simultaneously

3 Answers3