Does long query string affect the speed?

Question

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'. . . . 

Answer 1

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.