Create multi-column index including column from related table

Question

I have a model similar to the following:

Alert:
+ id : int
+ timestamp : datetime
+ message : text

UserAlert:
+ id : int
+ user_id : int, foreign key User.id
+ alert_id : int, foreign key Alert.id
+ archive : bool, default false

Basically, I'm creating alerts which are shared by multiple users (which users receive the alerts varies depending on the alert), but each user can "archive" each alert within his own alert inbox.

The problem: How do I give a particular user his newest non-archived alerts in a split second? ... or put more precisely, how do I create an index which runs the following query with optimum efficiency?

SELECT a.id, a.timestamp, a.message
FROM UserAlert AS ua
INNER JOIN Alert AS a
ON a.id = ua.alert_id
WHERE ua.user_id = 1234 and ua.archive = false
ORDER BY a.timestamp DESC
LIMIT 100;

Basically, I need an index on columns (UserAlert.user_id, Alert.timestamp). But I don't know how to create an index which uses multiple columns from two or more related tables. Seems like it should be a common problem, but I don't know what words to look for on Google, and so here I am looking for a nudge in the right direction.

Hi Isaac, you can use for below code, also you can search as composite index "CREATE INDEX index_name ON table_name(c2,c3,c4);" Related link https://www.mysqltutorial.org/mysql-index/mysql-composite-index/#:~:text=MySQL%20allows%20you%20to%20create,two%20columns%2C%20and%20so%20on. — Ferhat BAŞ, May 31 '21 at 21:48
@FerhatBAŞ The indexes created at your link are composed of multiple columns *from the same table*. I want to compose columns *from different tables* into one index. — Isaac Sutherland, Jun 01 '21 at 13:43
I just found this related stack overflow question: https://stackoverflow.com/questions/8509026/is-cross-table-indexing-possible It looks like this is possible in SQLServer as Indexed Views, which is not supported in mysql, which is my target environment. — Isaac Sutherland, Jun 01 '21 at 13:45

score 0 · Answer 1 · answered Jun 20 '21 at 04:07

MySQL does not have an index that works across multiple tables. (VIEWs don't help.)

The best that can be done is to use "covering" indexes.

ua:  INDEX(archive, user_id, alert_id)
a:  -- no recommendation assuming InnoDB and PRIMARY KEY(id)

If you could redundantly store the timestamp in Alert, then you could get 100 ids before reaching into the other table. (Currently, it must look at all the Alerts for the user, sort them, then deliver 100.)

Create multi-column index including column from related table

1 Answers1