CODEWITHSUNDEEP
mysqlindexingmultiple-columns
Ivan
Ivan

Reputation: 2481

MySQL multi-column indexes and OR statement

I found an interesting SitePoint article about indexes in MySQL

The above article talks about the "AND" statement, where a "SELECT" like 

SELECT peopleid 
FROM people 
WHERE firstname='Mike' 
    AND lastname='Sullivan' 
    AND age=17

can take advantage of a multi-column indexing like 

ALTER TABLE people 
    ADD INDEX fname_lname_age (firstname,lastname,age);

But in my web applications I have often a textfield input where the user can type portions of firstname or lastname, resulting in a query like this: 

SELECT peopleid 
FROM people 
WHERE ((firstname LIKE '%$parameter%') 
        OR (lastname LIKE '%$parameter%')) 
    AND age=17

So, does the multi-column indexing improve performance also in cases like these?

Thanks in advance

Upvotes: 1

Views: 442

Answers (1)

fancyPants
fancyPants

Reputation: 51888

There's a chapter in the official MySQL manual especially for this.

But what you really should learn about is EXPLAIN. Put it in front of your query like this:

EXPLAIN SELECT peopleid 
FROM people 
WHERE ((firstname LIKE '%$parameter%') 
        OR (lastname LIKE '%$parameter%')) 
    AND age=17

And you can see if an index is used or not.

Upvotes: 2

Related Questions