CODEWITHSUNDEEP
mysqlsqlperformanceoptimization
Ace
Ace

Reputation: 845

Does long query string affect the speed?

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

Upvotes: 6

Views: 946

Answers (3)

Jason Heo
Jason Heo

Reputation: 10236

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

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

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.

Upvotes: 4

Fabian
Fabian

Reputation: 2972

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.

Upvotes: 2

Related Questions