CODEWITHSUNDEEP
mysqlsqlperformance
Betafish
Betafish

Reputation: 1262

SQL query optimization (time)

Consider a sample table 'salary_details'

enter image description here.

Now, after some preprocessing, I have some Id's in my ArrayList ('user_id'). 

user_id(0): 1
user_id(1): 3
user_id(2): 4
user_id(3): 6

What, I would like to do is filter only those id's where salary = 2000.

Which approach is better:

1. 

   for (int i = 0; i < user_id.size(); i++) {
    SELECT ID FROM salary_details WHERE (part_id = user_id.get(i)) AND (SALARY = 2000)
    }

or 

2. 

SELECT ID FROM salary_details 
   WHERE ((part_id = user_id.get(0)) 
     OR (part_id = user_id.get(1)) 
     OR (part_id = user_id.get(2)) 
     OR (part_id = user_id.get(3))) 
     AND (SALARY = 2000)

Or, are there other better options?

Upvotes: 1

Views: 63

Answers (2)

e4c5
e4c5

Reputation: 53774

Neither of those queries will run in it's current form!

As a rule of thumb, one avoids hitting the database with an unnecessarily large number of queries. Based on that criteria looping and querying from each id one by one is a big no-no

The second query is a lot better, but still it will be a big task to build it up if you have a large number of elements in your array.

The best approach is to use the IN clause. This is a simple query, quite easy to build, easy for the query planner to parse and hits the database only once.

Having said, that when in doubt benchmark!

Upvotes: 2

Denys S&#233;guret
Denys S&#233;guret

Reputation: 382464

Use the IN clause:

SELECT ID FROM salary_details WHERE SALARY = 2000 and part_id (in user_ids)

The exact syntax depends on your driver/language (just one thing: use a prepared statement, don't concatenate sql and values to build your query).

Now, the most efficient query

  • must be found by measurement
  • is very dependent of your data and indexes (use explain)

Upvotes: 2

Related Questions