Imagine you have a primary composite index of last_name,first_name. Then you performed a search of WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%'.
Does the wildcard used in the last_name condition mean that the first_name condition will not be used in further helping MySQL find indexes? In other words, by putting a wildcard on the last_name condition MySQL will only do a partial index lookup (and ignores conditions given in the columns that are to the right of last_name)?
Further clarification of what I'm asking
Example-1: Primary key is last_name, first_name.
Example-2: Primary key is last_name.
Using this WHERE clause:WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%', would Example-1 be faster than Example-2?
Update
Here is an sqlfiddle: http://sqlfiddle.com/#!9/6e0154/3
CREATE TABLE `people1` (
`id` INT(11),
`first_name` VARCHAR(255) NOT NULL,
`middle_name` VARCHAR(255) NOT NULL,
`last_name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
INDEX `name` (`last_name`(15), `first_name`(10))
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
CREATE TABLE `people2` (
`id` INT(11),
`first_name` VARCHAR(255) NOT NULL,
`middle_name` VARCHAR(255) NOT NULL,
`last_name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
INDEX `name` (`last_name`(15))
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
INSERT INTO `people1` VALUES
(1,'John','','Smith'),(2,'Joe','','Smith'),(3,'Tom','','Smith'),(4,'George','','Washington');
INSERT INTO `people2` VALUES
(1,'John','','Smith'),(2,'Joe','','Smith'),(3,'Tom','','Smith'),(4,'George','','Washington');
# Query 1A
EXPLAIN SELECT * FROM `people1` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'smi%';
# Query 1B
EXPLAIN SELECT * FROM `people1` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'john';
# Query 2A
EXPLAIN SELECT * FROM `people2` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'smi%';
# Query 2B
EXPLAIN SELECT * FROM `people2` WHERE `first_name` LIKE 'joh%' AND `last_name` LIKE 'john';
Here are your questions. Plural. By rephrasing them (with "in other words") they are just different questions. Doing so does not make it easier for responders necessarily. On the contrary.
Q1: [Title question] Does wildcard in left-most column of composite index mean remaining columns in index aren't used in index lookup (MySQL)?
A1: No, it does not mean that.
Q2: Does the wildcard used in the last_name condition mean that the first_name condition will not be used in further helping MySQL find indexes?
A2: No, it does not mean that. Plus the tail of that question is ambiguous. It already knows what Index to use could be one offshoot answer to such vagueness.
Q3: In other words, by putting a wildcard on the last_name condition MySQL will only do a partial index lookup (and ignores conditions given in the columns that are to the right of last_name)?
A3: No. The right-most columns are served from the index similar to a covering index strategy benefiting from the slowness of data page lookup.
Q4: ...would Example-1 be faster than Example-2?
A4: Yes. It is a covering index in regards to those columns. See covering indexes.
As an aside concerning Q4. It is irrelevant if it is a PK or non-PK. There are probably a dozen reasons why that as a PK would be dreadful for your application.
Original answer(s) below:
with only a composite key on (last_name,first_name)
and a query as you mention
WHERE first_name LIKE 'joh%'
... It won't use the index at all. It will do a table scan. Due to the absence of
first_namefirst_name left-mostSo table scan here we come.
Please see the Manual page Multiple-Column Indexes to read more. And focus on the left-most concept of it. In fact, go to that page, and search on the word left.
See the Manual Page on the Explain facility in mysql. Also the article Using Explain to Write Better Mysql Queries.
There have been a few edits to the question since I was here an hour or two ago. I will leave you with the following. Run your actual query thru explain, and decipher thru the Using Explain ... link above or another reference
drop table myNames;
create table myNames
( id int auto_increment primary key,
lastname varchar(100) not null,
firstname varchar(100) not null,
col4 int not null,
key(lastname,firstname)
);
truncate table myNames;
insert myNames (lastName,firstName,col4) values
('Smith','John',1),('Smithers','JohnSomeone',1),('Smith3','John4324',1),('Smi','Jonathan',1),('Smith123x$FA','Joh',1),('Smi3jfif','jkdid',1),('r3','fe2',1);
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
insert myNames (lastName,firstName,col4) select lastname,firstname,col4 from mynames;
select count(*) from myNames;
-- 458k rows
select count(*)
from myNames
where lastname like 'smi%';
-- 393216 rows
select count(*)
from myNames
where lastname like 'smi%' and firstname like 'joh%';
-- 262144 rows
Explain renders voodoo numbers for rows. Voodoo? Yes, because a query that will potentially run for an hour, you are asking explain to give you a fuzzy count, not run it, and give you that answer in 2 seconds or less. Don't consider these to be real count #'s for criteria when it is run for real, without explain.
explain
select count(*)
from myNames
where lastname like 'smi%';
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| 1 | SIMPLE | myNames | range | lastname | lastname | 302 | NULL | 233627 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
explain
select count(*)
from myNames
where lastname like 'smi%' and firstname like 'joh%' and col4=1;
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| 1 | SIMPLE | myNames | range | lastname | lastname | 604 | NULL | 233627 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
-- the below chunk is interest. Look at the Extra column
explain
select count(*)
from myNames
where lastname like 'smi%' and firstname like 'joh%' and col4=1;
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | myNames | ALL | lastname | NULL | NULL | NULL | 457932 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+--------+-------------+
explain
select count(*)
from myNames
where firstname like 'joh%';
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| 1 | SIMPLE | myNames | index | NULL | lastname | 604 | NULL | 453601 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
analyze table myNames;
+----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| so_gibberish.mynames | analyze | status | OK |
+----------------------+---------+----------+----------+
select count(*)
from myNames where left(lastname,3)='smi';
-- 393216 -- the REAL #
select count(*)
from myNames where left(lastname,3)='smi' and left(firstname,3)='joh';
-- 262144 -- the REAL #
explain
select lastname,firstname
from myNames
where lastname like 'smi%' and firstname like 'joh%';
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+
| 1 | SIMPLE | myNames | range | lastname | lastname | 604 | NULL | 226800 | Using where; Using index |
+----+-------------+---------+-------+---------------+----------+---------+------+--------+--------------------------+