How to index this query?

MySQL & MariaDB Other MySQL® Questions
1

select b.barId, userId, b.schoolId, barName, eventName, eventDay, eventMonth, eventYear from bars b LEFT OUTER JOIN events e on b.barId = e.barId and e.sortDate=20071128 where b.schoolId=6;

±—±------------±------±------±--------------±-------------±--------±-----±------±-------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±------±--------------±-------------±--------±-----±------±-------------------------+| 1 | SIMPLE | b | index | NULL | index_bars | 276 | NULL | 233 | Using where; Using index || 1 | SIMPLE | e | index | NULL | index_events | 288 | NULL | 83853 | Using index |±—±------------±------±------±--------------±-------------±--------±-----±------±-------------------------+

I have created these indexes (I’m fairly new at indexes and joined queries so go easy ))

CREATE INDEX index_bars ON bars (barId ASC, userId ASC, schoolId ASC, barName ASC, lastLogin ASC);

CREATE INDEX index_events ON events (eventId ASC, barId ASC, eventName ASC, eventYear ASC, eventMonth ASC, eventDay ASC, sortDate ASC, schoolId ASC);

The initial query takes about 3-6 seconds depending…after its initial query and cache are built it is fast, but relying on the cache isnt a good idea as it is lost once the table(s) are updated.

Any ideas on how to speed it up? Oh, I should probably include my table layout too.

Bars table:

±-------------±-------------±-----±----±------------------±------+| Field | Type | Null | Key | Default | Extra |±-------------±-------------±-----±----±------------------±------+| barId | int(10) | NO | PRI | 0 | || userId | int(10) | YES | | NULL | || schoolId | int(7) | YES | | NULL | || barName | varchar(255) | YES | | NULL | || barWebsite | varchar(255) | YES | | NULL | || barAddress | varchar(255) | YES | | NULL | || barCity | varchar(100) | NO | | NULL | || barState | char(2) | NO | | NULL | || barZipcode | int(5) | YES | | NULL | || barPhoneNum | varchar(20) | YES | | NULL | || barGenre | varchar(100) | YES | | NULL | || cover | float(3,2) | YES | | NULL | || quadDistance | int(3) | YES | | NULL | || lastLogin | timestamp | YES | | CURRENT_TIMESTAMP | || icon | varchar(100) | YES | | NULL | || barEmail | varchar(255) | NO | | NULL | |±-------------±-------------±-----±----±------------------±------+

Events table:

±-------------------------±-------------±-----±----±--------±---------------+| Field | Type | Null | Key | Default | Extra |±-------------------------±-------------±-----±----±--------±---------------+| eventId | int(10) | NO | PRI | NULL | auto_increment || barId | int(10) | YES | | NULL | || eventName | varchar(255) | NO | | NULL | || eventDescription | text | YES | | NULL | || eventTheme | varchar(255) | YES | | NULL | || eventFrequency | int(2) | YES | | NULL | || beginTime | int(2) | YES | | NULL | || beginAMPM | char(2) | YES | | NULL | || endTime | int(2) | YES | | NULL | || endAMPM | char(2) | YES | | NULL | || eventCover | float(3,2) | YES | | NULL | || entertainmentTitle | varchar(255) | YES | | NULL | || entertainmentType | varchar(255) | YES | | NULL | || entertainmentDescription | text | YES | | NULL | || eventYear | int(4) | YES | | NULL | || eventMonth | int(2) | YES | | NULL | || eventDay | int(2) | YES | | NULL | || sortDate | int(8) | YES | | NULL | || barGenre | varchar(100) | YES | | NULL | || schoolId | smallint(5) | NO | | 0 | |±-------------------------±-------------±-----±----±--------±---------------+

2

I would say that the two indexes that you really should need for this query is:
bars(shoolId)
events(barId,sortDate)

The reason:

  1. You are using a LEFT JOIN which forces MySQL to always join using the bars table first. Which means that you should first find these records.

You are only using e.barId and sortDate in the ON condition.

That query should do pretty OK with just those two indexes.
And yes you could add some more columns to the right side of these columns in the index if you want to avoid additional seeks, but that would also increase the index size and you are selecting so many fields that I don’t think it will benefit from it.