Optimizing MySQL search query

Question

Need yours help optimizing one mysql query. Lets take simple table for example.

CREATE TABLE `Modules` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `moduleName` varchar(100) NOT NULL,
 `menuName` varchar(255) NOT NULL,
PRIMARY KEY (`ID`),
KEY `moduleName` (`moduleName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Lets Fill it with some data:

INSERT INTO  `Modules` (`moduleName` ,`menuName`)
VALUES 
    ('abc1',  'name1'), 
    ('abc',  'name2'), 
    ('ddf',  'name3'), 
    ('ccc',  'name4'), 
    ('fer',  'name5');

And some sample string. Let it be abc_def;

Traditionally we are trying to find all the rows containing search string.

On the contrary, my task is, to find all rows which contains moduleName in input string. For now I have following query to get desired result:

SELECT `moduleName` ,`menuName` 
FROM `Modules` 
WHERE 'abc_def' LIKE(CONCAT(`moduleName`,'%'))

This will return

moduleName   | menuName 
---------------------------
abc          | name2

The problem is, that this query is not using index.

Is there some way to force it to use one?

Answer 1

You seem to misunderstand what is index and how it can help to speed up a query.

Let's look at what is your moduleName index. It is basically a sorted list of mappings from moduleName to ID. And what you are selecting?

SELECT moduleName, menuName 
FROM Modules
WHERE 'abc_def' LIKE CONCAT(moduleName,'%');

That is you want some two fields for each row that has some relation to a somehow mapped value of moduleName field. How can ever index help you? There is no exact match, and there is no way to take advantage from the fact that we have moduleNames sorted.

What you need to take advantage from the index, is to have a check for exact match in the condition:

SELECT moduleName, menuName 
FROM Modules
WHERE moduleName = LEFT('abc_def', LENGTH(moduleName));

Now we do have an exact match, but since the right part of the condition depends on the moduleName as well, this condition will be checked for each row. Since in his case MySQL can not predict how many rows will match, but it can predict that it will need randon disk access to fetch menuNames for each matching row, MySQL will not use the index.

So you have basically two approaches:

1. if you know that the condition narrows the number of matching rows significantly, then you can just force the index
2. another option is to extend your index to a covering composite index (moduleName, menuName), then all results for query will be fetched from the index directly (that is from memory).

Approach #2 (see SQLfiddle) will get you an index hit with a simple query, and should offer much better performances on a larger table. On small tables, I (i.e., lserni - see comment) don't think it's worth the effort.