Question 20544 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql optimize too many where conditions

the story: i have 2 tables of students. i need to find for every student in table A whether there is some student in table B that is younger and has the same name.

TABLE A (not real table, could be user input)

id name age
0 jim 12
1 jack 13
2 tom 23

TABLE B

id name age
0 jim 14
1 jill 10
2 tim 8
3 jack 12

this is my current sql query:

select * from table where name=jim and age<12 or name=jack and age<13 or ......

say i have to filter out the rows that match the input filter conditions（table A）, and the where keyword could be followed by 1000 more conditions combining name and age in pair. I tried this and could generate query using mybatis foreach, but the efficiency is very low. how to optimize the above query?

Tags:

2

Answers


  1. 0 
    SELECT tableA.* 
  FROM tableA 
  INNER JOIN tableB ON tableA.name = tableB.name and tableA.age > tableB.age

    see: DBFIDDLE

    You only need fixed comparsons when you need to find only studends that did not pass the age of 100 (WHERE age<100)

    Using tableA.name = tableB.name will compare the name in tableA with the name in tableB

    Login or Signup to reply.
  2. 0

    Try this:

    SELECT * 
  FROM StudentsA 
  JOIN StudentsB ON StudentsA.name = StudentsB.name
     WHERE StudentsA.age > StudentsB.age

    If you define index on (name,age) Or name it will be faster and more performant!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search