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

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