6

Suppose i have a long query string for eg.

SELECT id from users where collegeid='1' or collegeid='2' . . . collegeid='1000'

will it affect the speed or output in any way?

SELECT m.id,m.message,m.postby,m.tstamp,m.type,m.category,u.name,u.img 
from messages m 
join users u on m.postby=u.uid 
where m.cid = '1' or m.cid  = '1' . . . . . . 
or m.cid = '1000'. . . .
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Ace
  • 841
  • 9
  • 23

3 Answers3

4

I would prefer to use IN in this case as it would be better. However to check the performance you may try to look at the Execution Plan of the query which you are executing. You will get the idea about what performance difference you will get by using the both.

Something like this:

SELECT id from users where collegeid IN ('1','2','3'....,'1000')

According to the MYSQL

If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants.

The number of values in the IN list is only limited by the max_allowed_packet value.

You may also check IN vs OR in the SQL WHERE Clause and MYSQL OR vs IN performance

The answer given by Ergec is very useful:

SELECT * FROM item WHERE id = 1 OR id = 2 ... id = 10000

This query took 0.1239 seconds

SELECT * FROM item WHERE id IN (1,2,3,...10000)

This query took 0.0433 seconds

IN is 3 times faster than OR

will it affect the speed or output in any way?

So the answer is Yes the performance will be affected.

Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • There won't be any performance gain if those columns are indexed..There won't be any significant gain in performance even if they aren't indexed – Anirudha Nov 10 '13 at 13:09
  • @Anirudh: I don't think this claim holds in gneneral. Some DBMS will use an index even if if there is a disjunction / IN sublist in your where clause. – Fabian Nov 10 '13 at 13:17
  • @Rahul Is there any limit on ids `In` can hold? – Ace Nov 10 '13 at 13:24
  • 1
    I would say that in that exact example 'IN' keyword is not the best answer either. I would use BETWEEN 1 AND 1000 – Ran Nov 10 '13 at 13:26
  • @user2894116:- Yes you can set it by the max_allowed_packet:- http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_packet – Rahul Tripathi Nov 10 '13 at 13:27
  • @Ran That would help if id's are in continuation. But in my query there are chances that ids may be like this 5,8,9 – Ace Nov 10 '13 at 13:28
2

Obviously, there is no direct correlation between the length of a query string and its processing time (as some very short query can be tremendeously complex and vice versa). For your specific example: It depends on how the query is processed. This is something you can check by looking at the query execution plan (syntax depends on your DBMS, something like EXPLAIN PLAN). If the DBMS has to perform a full table scan, performance will only be affected slightly, since the DBMS has to visit all pages that make up the table anyhow. If there is an index on collegeid, performance will likely suffer more the more entries you put into your disjunction, since there will be several (though very fast) index lookups. At some point, there will we an full index scan instead of individual lookups, at which point performance will not degrade significantly anymore.

However - details depend ony our DBMS and its execution planner.

Fabian
  • 2,822
  • 1
  • 17
  • 22
2

I' not sure you are facing what I suffered.

Actually, string length is not problem. How many values are in IN() is more important.

I've tested how many elements can be listed IN(). Result is 10,000 elements can be processed without performance loss.

Values in IN() should be stored in somewhere and searched while query evaluation. But 10k values is getting slower.

So if you have many 100k values, split 10 groups and try 10 times query. Or save in temp table and JOIN.

and long query uses more CPU, So IN() better than column = 1 OR ...

Jason Heo
  • 9,956
  • 2
  • 36
  • 64