Best index for searching both first_name and last_name with trailing wildcards?

Question

My first attempt at a question proved to be confusing and I received some mixed answers (probably due to my confusing question). Here's a different and better question...

Assume my table looks like this in MySQL:

CREATE TABLE `people` (
    `person_id` INT(11),
    `alias_num` TINYINT(3),
    `first_name` VARCHAR(255) NOT NULL,
    `last_name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`person_id`,`alias_num`)
  )
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

With data like this:

person_id alias_num first_name last_name
--------- --------- ---------- ---------
1         1         John       Smith
2         1         Joe        Smith
3         1         Bill       Smith     # <-- Notice this guy has 3 aliases
3         2         Billy      Smith     # <--
3         3         William    Smith     # <--
4         1         Susan      Thompson
...

Assume jo and smi were entered into an HTML search form (with both fields required) and my query will always be like this:

SELECT person_id FROM people WHERE first_name LIKE 'jo%' AND last_name LIKE 'smi%';

Question: What's the best index(es) to add to my table to make the above query the fastest?

Note: I did some quick testing on a table of almost a million rows and it looks like that 2 seperate indexes of first_name(15) and last_name(15) seem faster than a composite index of last_name(15),first_name(15) using SQL_NO_CACHE? But maybe I'm testing this wrong. I'm also considering that maybe a combination of both composite index and an index on a single name would be good (if that doesn't confuse the optimizer)?

Bonus Question:
Considering that I'm searching for partial words, not full words, would something like ElasticSearch do this query any better?

Answer 1

You're correct, separate first_name and last_name indexes are going to work better.

In my experience, a composite index is best on non-variable fields (like 2 numbers). I'd go with one index on each name field.

You might also adjust your my.cnf settings if you have not already, tweaking memory available to MySQL can make dramatic differences in sorting/searching on indexes.

As for my.cnf, that's a whole other question, IMO. You can start here: https://dev.mysql.com/doc/refman/5.6/en/server-default-configuration-file.html. Mysql ships with my-large.cnf, my-huge.cnf so those should give you a good start.

Answer 2

Adding to the answers above from @mikeb and @RickJames,

The MySQL docs say here:

For a BTREE index, an interval might be usable for conditions combined with AND, where each condition compares a key part with a constant value using =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN, or LIKE 'pattern' (where 'pattern' does not start with a wildcard). An interval can be used as long as it is possible to determine a single key tuple containing all rows that match the condition (or two intervals if <> or != is used).

The optimizer attempts to use additional key parts to determine the interval as long as the comparison operator is =, <=>, or IS NULL. If the operator is >, <, >=, <=, !=, <>, BETWEEN, or LIKE, the optimizer uses it but considers no more key parts. For the following expression, the optimizer uses = from the first comparison. It also uses >= from the second comparison but considers no further key parts and does not use the third comparison for interval construction:

key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10

The single interval is:

('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)

It is possible that the created interval contains more rows than the initial condition. For example, the preceding interval includes the value ('foo', 11, 0), which does not satisfy the original condition.

When using LIKE on a key part of a composite, the key parts to the right are not used. So this confirms what @mikeb says in that two single indexes would work better because MySQL can judge which one has better cardinality and use it. However, I ended up using the answer from Rick James with last_name,first_name,person_id(prefix/size removed) since I was only selecting person_id. This acts as a covering index and works just as fast (possibly faster) in my tests than single separate indexes, plus gives me good sorting by last_name then first_name. Composite keys are usually the better way to go anyways.

Answer 3

SELECT person_id FROM people WHERE first_name LIKE 'jo%' AND last_name LIKE 'smi%';

Case 1 - Covering (rare): All the fields of the entire SELECT are included in the index. Either of these is "covering" and optimal:

INDEX(first_name, last_name, person_id)
INDEX(last_name, first_name, person_id)

"Covering" implies that it does all the work inside the index and does not need to touch the data. Note: The "Data" and the PRIMARY KEY live together in one BTree; each secondary index lives in another BTree.

Case 2 - non-Covering: If you don't want to, or can't (because of TEXT, etc) include all the fields, then either of these is optimal:

INDEX(first_name)
INDEX(last_name)

Create both indexes and let the optimizer pick the better one dynamically. INDEX(first_name, last_name) is of no use because of the wild card; it will not get past the first column of the index.

Prefixing: Do not use first_name(15). It will not save much space, and it will not help in performance. Just as with Case 2, it will not get past the first column in a composite index.

(255): Don't indiscriminately use VARCHAR(255). The 255 gets involved in the details of the temp table that might be used to perform the SELECT, and you will slow down the query over what would happen with a sensible max length. In some cases you will exceed a limit and not be allowed to build the index.

Secondary keys: In InnoDB, each "secondary key" implicitly includes all the columns from the PRIMARY KEY. So INDEX(first_name, last_name) will actually include person_id (and alias_num), thereby making equivalent to my recommended INDEX(first_name, last_name, person_id).

INDEX(a) and INDEX(a,b): The former is virtually always redundant; keep only the latter.

my.cnf: The most important setting for this discussion is to set innodb_buffer_pool_size to about 70% of available RAM.

Further discussion: Building an index from a SELECT and Compound indexes.

Answer 4

It seems to use the key ?!?

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,first_name VARCHAR(12) NOT NULL
,last_name VARCHAR(12) NOT NULL
,INDEX fl (first_name,last_name)
);

INSERT INTO my_table (first_name,last_name) VALUES
('John','Brown'),
('John','Smith'),
('John','Johnson'),
('John','Lewis'),
('John','Lennon'),
('John','Major'),
('James','Brown'),
('James','McIlroy'),
('James','Napier'),
('Jamie','Oliver'),
('James','May'),
('James','Martin');

SELECT * FROM my_table WHERE first_name LIKE 'Ja%' AND last_name LIKE 'Bro%';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  7 | James      | Brown     |
+----+------------+-----------+

EXPLAIN 
SELECT * FROM my_table WHERE first_name LIKE 'Ja%' AND last_name LIKE 'Bro%';
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | my_table | range | fl            | fl   | 28      | NULL |    6 |   100.00 | Using where; Using index |
+----+-------------+----------+-------+---------------+------+---------+------+------+----------+--------------------------+