Mysql multiple row index

I have a following query in mysql.

SELECT
    *
FROM
    Accounts AS a
WHERE
    ('s' IS NULL OR (a.FirstName LIKE CONCAT('s','%') OR
      开发者_如何学运维                a.LastName LIKE CONCAT('s','%') OR
                      a.FullName LIKE CONCAT('s','%')
                     )
    )

How Should I put indexes for the table?

p.s. 's' is actually a variable in stored proc, so 's' IS NULL and concat are necessary.

First of all, just a quick suggestion: do not use concat if you don't have to. Your query can be rewritten as ('s' is NULL) is always FALSE so you can will always get all rows based on the second condition anyway:

SELECT
    *
FROM
    Accounts AS a
WHERE
    a.FirstName LIKE 's%' OR
    a.LastName LIKE 's%' OR
    a.FullName LIKE 's%'

Indexes that might help, but no necessarily will are:

create index idx_01 on accounts(FirstName);
create index idx_01 on accounts(LastName);
create index idx_01 on accounts(FullName);

You can also consider a FULL TEXT SEARCH index for your table.

1. 's' IS NULL is always false

2. Is there any reason you're using CONCAT('s','%') instead of 's%'?

3. Try a composite index on (FirstName, LastName, FullName), although it might not work really well for (VAR)CHARs (or even at all it seems)

Since #3 didn't work, I can only refer you to MySQL manual now. THere's a bit about using how MySQL uses indexes with LIKE here

FOR you full text indexing is also an option

add fulltext index for 3 fields then

use

MATCH() AGAINST() syntax

Eg

 SELECT * FROM articles WHERE MATCH (title,body)
 AGAINST ('superb catch' IN BOOLEAN MODE);