CODEWITHSUNDEEP
phpmysqlmysqliprepared-statementwhere-clause
Memor-X
Memor-X

Reputation: 2970

does having mutliple OR Cluses slow down a query

i have a function which turns this string

[games]|[nexis core]|[core]|[lemure lemure]|[chrome tower dlc]

into this where clause

WHERE 
(
    main.tags LIKE '%[games]%' OR 
    main.tags LIKE '%[nexis core]%' OR 
    main.tags LIKE '%[core]%' OR 
    main.tags LIKE '%[lemure lemure]%' OR 
    main.tags LIKE '%[chrome tower dlc]%'
)

in the table it's doing the search in, a record will has a tags field like the following

[boss]|[lemure lemure]|[astra]|[divine]|[kamidori]|[3rd party]|[razil]|[core]

so when the above where clause runs, it can search the tags fields and the order of the tags will have no effect on the search

the reason why i encase every tag in [] and do a LIKE cluse with it included is if someone searches for the tag party they may get [3rd party] [party members] [nunnally's battle party] [battle party reality marble] which all are completely different things as [3rd party] is used to describe a battle which a 3rd group participates which isn't allied with the player party or enemy party while [battle party reality marble] is used to describe an ability

i separate each tag with a | so that i can explode the string in PHP when i pull it from the database and output each tag to the page, similar to the suggest tags when a question is asked here

now, while i can impose a limit to the number of tags a use can use in a search like how this site limits the number of tags used in a question to 5 i want to try and avoid that if possible, the WHERE clause i generate can have technically infinite ORs because of how it generates

i am wondering, can a prepared statement in mysqli slow down if i use too many OR in the query

Upvotes: 0

Views: 42

Answers (1)

Sven
Sven

Reputation: 70853

The performance impact is not because of the OR combination, but because of the LIKE '%search', which cannot use an index.

And really, your database is badly normalized - and because of this it is harder to get what you need, and it takes more performance than necessary.

Upvotes: 6

Related Questions