I have the following query that might be taking a long time.
SELECT b.*, ubh.bookId, ubh.position, ubh.completedDate, ubh.userId
FROM user_book_history AS ubh
INNER JOIN books AS b ON ubh.bookId = b.id
WHERE ubh.userId = ? ORDER by ubh.createDate desc
What can I do to optimize this query? What indices should I add to the mysql table? Can someone help?
CREATE TABLE "user_book_history" (
"id" int NOT NULL AUTO_INCREMENT,
"bookId" int DEFAULT NULL,
"userId" int DEFAULT NULL,
"position" int DEFAULT '0',
"createDate" bigint DEFAULT NULL,
"completedDate" bigint DEFAULT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "books" (
"id" int NOT NULL AUTO_INCREMENT,
"amazonBuyUrl" text,
"audioUrl" varchar(255) DEFAULT NULL,
"author" varchar(255) DEFAULT NULL,
"authorDesc" text,
"body" longtext,
"categories" varchar(255) DEFAULT NULL,
"description" text,
"image" text,
"publishDate" bigint DEFAULT NULL,
"subtitle" varchar(255) DEFAULT NULL,
"title" varchar(255) DEFAULT NULL,
"uploadDate" bigint DEFAULT NULL,
"video" varchar(255) DEFAULT NULL,
"mediaUrl" varchar(255) DEFAULT NULL,
"subid" varchar(255) DEFAULT NULL,
"tags" varchar(255) DEFAULT NULL,
"free" tinyint DEFAULT '0',
"infographicUrl" varchar(255) DEFAULT NULL,
"vector" varchar(255) DEFAULT NULL,
"comingSoon" tinyint DEFAULT '0',
"isPopular" tinyint DEFAULT NULL,
PRIMARY KEY ("id")
);
I attached the EXPLAIN output and the create tables
4
This should optimize the lookup for the specific userId, and the sorting by createDate, and the reference to bookId for the join:
ALTER TABLE user_book_history
ADD INDEX (userId, createDate);
This does not achieve the covering-index optimization, because your query references other columns.
books.id is the primary key for that table. Your EXPLAIN shows that the join is optimized already as an eq_ref access. There is nothing more you need to do for that table.
After applying this index, I tested with EXPLAIN and I see this:
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------+
| 1 | SIMPLE | ubh | NULL | ref | userId | userId | 5 | const | 1 | 100.00 | Using where; Backward index scan |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.ubh.bookId | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+----------------------------------+
This shows the new index on (userId, createdDate) is used, which turns type: ALL into type: ref (lookup on a non-unique index), and the “Using filesort” is eliminated.
The access to the books table is still by primary key, which is as good as you can get.
4