MySQL SELECT query performance

Question

I have a query which has a list of values for checking against a single column in a multi column table.

Example query ONE......

SELECT `name`, `sleeps` 
FROM `properties` 
WHERE `town`='bude' OR `town`='newquay' OR `town`='widemouth' OR `town`='polzeath' OR `town`='crantock' OR `town`='perranporth' OR `town`='Porthmeor' OR `town`='St Ives' OR `town`='porthtowan'

Example query TWO......

SELECT `name`, `sleeps` 
FROM `properties` 
WHERE `town` IN ('bude', 'newquay', 'widemouth', 'polzeath', 'crantock', 'wadebridge', 'Porthmeor', 'St Ives', 'porthtowan')

I have run both in phpmyadmin and found the query speed to be very similar, all be it with a table of approx 5,000 records.

In terms of performance and durability when the table contains more data is is best to go for the 'IN' style query or use multiple instances OR?

Thanks

score 4 · Accepted Answer · answered Jul 18 '13 at 12:19

4

This is a simple test result,

SELECT * FROM item WHERE id = 1 OR id = 2 ... id = 10000
This query took 0.1139 seconds

SELECT * FROM item WHERE id IN (1,2,3,...10000)
This query took 0.0413 seconds

IN faster than OR

answered Jul 18 '13 at 12:19

Adem Öztaş

20,457
4
34
42

1

Great! +1 for giving evidence because science is all about expriments and evidence. – Mohammad Areeb Siddiqui Jul 18 '13 at 13:49

score 2 · Answer 2 · answered Jul 18 '13 at 12:16

2

Convert the less efficient one into the other. So I think the answer should rather depend on the readability of each (where I think that IN is more efficient)

IN is 3 times faster than OR

answered Jul 18 '13 at 12:16

Naveen Kumar Alone

7,536
5
36
57

score 2 · Answer 3 · answered Jul 18 '13 at 12:17

2

I tried some querys, IN is faster everytime.

answered Jul 18 '13 at 12:17

Fox

566
4
14

MySQL SELECT query performance

3 Answers3