MYSQL Left join extremely slow on indexed columns

Question

Below are the 4 tables' table structure:

Calendar:

CREATE TABLE `calender` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `HospitalID` int(11) NOT NULL,
  `ColorCode` int(11) DEFAULT NULL,
  `RecurrID` int(11) NOT NULL,
  `IsActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `idxHospital` (`ID`,`StaffID`,`HospitalID`,`ColorCode`,`RecurrID`,`IsActive`)
) ENGINE=InnoDB AUTO_INCREMENT=4638 DEFAULT CHARSET=latin1;

CalendarAttendee:

CREATE TABLE `calenderattendee` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `CalenderID` int(11) NOT NULL,
  `StaffID` int(11) NOT NULL,
  `IsActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `idxCalStaffID` (`StaffID`,`CalenderID`)
) ENGINE=InnoDB AUTO_INCREMENT=20436 DEFAULT CHARSET=latin1;

CallPlanStaff:

CREATE TABLE `callplanstaff` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Staffname` varchar(45) NOT NULL,
  `IsActive` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID_UNIQUE` (`ID`),
  KEY `idx_IsActive` (`Staffname`,`IsActive`),
  KEY `idx_staffName` (`Staffname`,`ID`) USING BTREE KEY_BLOCK_SIZE=100
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

Users:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL DEFAULT '',
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_users_on_email` (`email`),
  UNIQUE KEY `index_users_on_name` (`name`),
  KEY `idx_email` (`email`) USING BTREE KEY_BLOCK_SIZE=100
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

What I'm trying to do is to fetch the calender.ID and Users.name using below query:

SELECT a.ID, h.name
    FROM `stjude`.`calender` a 
    left join calenderattendee e on a.ID = e.calenderID
    left join callplanstaff f on e.StaffID = f.ID
    left join users h on f.Staffname = h.email

The relation between those tables are:

It took about 4 seconds to fetch 13000 records which I bet it could be faster.

When I look at the tabular explain of the query, here's the result:

Why MYSQL isn't using index on callplanstaff table and users table?

Also, in my case, should I use multi index instead of multi column index?

And is there any indexes I'm missing so my query is slow?

=======================================================================

Updated:

As zedfoxus and spencer7593 recommended to change the idxCalStaffID's ordering and idx_staffname's ordering, below is the execution plan:

It took 0.063 seconds to fetch, much fewer time required, how does the ordering of the indexing affects the fetch time..?

Answer 1

This seems like a situation where inner join might be a better option than a left join.

SELECT a.ID, h.name
    FROM `stjude`.`calender` a 
    INNER JOIN calenderattendee e on a.ID = e.calenderID
    INNER JOIN callplanstaff f on e.StaffID = f.ID
    INNER JOIN users h on f.Staffname = h.email

Then let's get onto the indexes. The Calendar table has

PRIMARY KEY (`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`),

The second one, ID_UNIQUE is redundant. A Primary key is a unique index. Having too many indexes slows down insert/update/delete operations.

Then the users table has

UNIQUE KEY `index_users_on_email` (`email`),
UNIQUE KEY `index_users_on_name` (`name`),
KEY `idx_email` (`email`) USING BTREE KEY_BLOCK_SIZE=100

The idx_email column is redundant here. Other than that there isn't much to do by way of tweaking the indexes. Your explain shows that an index is being used on each and table.

Why MYSQL isn't using index on callplanstaff table and users table?

Your explain shows that it does. The it's using the primary key and the index_users_on_email indexes on these tables.

Also, in my case, should I use multi index instead of multi column index?

As a rule of thumb, mysql uses only one index per table. So a multi column index is the way to go rather than having multiple indexes.

And is there any indexes I'm missing so my query is slow?

As I mentioned in the comments you are fetching (and probably displaying) 13,000 records. That's where your bottleneck maybe.

Answer 2

Q: Is there any indexes I'm missing so my query is slow?

A: Yes. A suitable index on calendarattendee is missing.

---

We probably want an index on calenderattendee with a calendarid as the leading column, for example:

 ... ON calenderattendee (calendaid, staffid)

Answer 3

You're misinterpreting the EXPLAIN report.

• type: index is not such a good thing. It means it's doing an "index-scan" which examines every element of an index. It's almost as bad as a table-scan. Notice the column rows: 4562 and rows: 13451. This is the estimated number of index elements it will examine for each of those tables.
• Having two tables doing a index-scan is even worse. The total number of rows examined for this join is 4562 x 13451 = 61,363,462.
• Using join buffer is not a good thing. It's a thing the optimizer does as a consolation when it can't use an index for the join.

• type: eqref is a good thing. It means it's using a PRIMARY KEY index or UNIQUE KEY index, to look up exactly one row. Notice the column rows: 1. So at least for each of the rows from the previous join, it only does one index lookup.

• You should create an index on calenderattendee for columns (CalenderId, StaffId) in that order (@spencer7593 posted this suggestion while I was writing my post).

• By using LEFT [OUTER] JOIN in this query, you're preventing MySQL from optimizing the order of table joins. And since your query fetches h.name, I infer that you really just want results where the calendar event has an attendee and the attendee has a corresponding user record. It makes no sense that you're not using an INNER JOIN.

Here's the EXPLAIN with the new index and the joins changed to INNER JOIN (though my row counts are meaningless because I didn't create test data):

+----+-------------+-------+------------+--------+--------------------------------+----------------------+---------+----------------+------+----------+-----------------------+
| id | select_type | table | partitions | type   | possible_keys                  | key                  | key_len | ref            | rows | filtered | Extra                 |
+----+-------------+-------+------------+--------+--------------------------------+----------------------+---------+----------------+------+----------+-----------------------+
|  1 | SIMPLE      | a     | NULL       | index  | PRIMARY,ID_UNIQUE,idxHospital  | ID_UNIQUE            | 4       | NULL           |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | e     | NULL       | ref    | idxCalStaffID,CalenderID       | CalenderID           | 4       | test.a.ID      |    1 |   100.00 | Using index           |
|  1 | SIMPLE      | f     | NULL       | eq_ref | PRIMARY,ID_UNIQUE              | PRIMARY              | 4       | test.e.StaffID |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | h     | NULL       | eq_ref | index_users_on_email,idx_email | index_users_on_email | 767     | func           |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+--------+--------------------------------+----------------------+---------+----------------+------+----------+-----------------------+

The type: index for the calenderattendee table has been changed to type: ref which means an index lookup against a non-unique index. And the note about Using join buffer is gone.

That should run better.

---

how does the ordering of the indexing affects the fetch time..?

Think of a telephone book, which is ordered by last name first, then by first name. This helps you look up people by last name very quickly. But it does not help you look up people by first name.

The position of columns in an index matters!

You might like my presentation How to Design Indexes, Really.

• Slides: http://www.slideshare.net/billkarwin/how-to-design-indexes-really
• Video of me presenting this talk: https://www.youtube.com/watch?v=ELR7-RdU9XU