I have a multilingual website with an implemented game search and sorting function (I deleted the last question because there was a lot of unnecessary information in it; and yes, instead of "books" now "games" because I created a new test database). The search is carried out by the following query:
SELECT games.id AS id
FROM games
LEFT JOIN games_titles ON games.id = games_titles.game_id
WHERE 1
AND EXISTS (SELECT games_devs.game_id
FROM games_devs
WHERE games_devs.game_id = games.id
AND games_devs.dev IN (...))
AND EXISTS (SELECT games_publishers.game_id
FROM games_publishers
WHERE games_publishers.game_id = games.id
AND games_publishers.publisher IN (...))
AND games_titles.lang=1
ORDER BY games_titles.title
The request is triggered for more than 2.5 seconds, without ORDER BY - more than 1 second. There may be several more parameters in the search condition, because I accept them from the post request and convert them into a string. But the essence is the same. Now there are 100,000 records in the database, filters work very slowly. The operation is performed quickly only when there are no subqueries and joins. Here is more detailed information (all fields that are used in ON and WHERE are indexed using BTREE):
EXPLAIN (without alphabetical sorting):
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| 1 | PRIMARY | games_titles | ref | game_id_index,lang_index | lang_index | 4 | const | 98995 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| 1 | PRIMARY | games | eq_ref | PRIMARY | PRIMARY | 4 | test_db.games_titles.game_id | 1 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| 2 | MATERIALIZED | games_devs | ALL | game_id_index | NULL | NULL | NULL | 98437 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
| 3 | MATERIALIZED | games_publishers | ALL | game_id_index | NULL | NULL | NULL | 98437 |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+
profiling (without alphabetical sorting):
+----+------------------------+--------+
| 1 | Starting | 168 µs |
+----+------------------------+--------+
| 2 | Checking Permissions | 21 µs |
+----+------------------------+--------+
| 3 | Opening Tables | 81 µs |
+----+------------------------+--------+
| 4 | After Opening Tables | 25 µs |
+----+------------------------+--------+
| 5 | System Lock | 15 µs |
+----+------------------------+--------+
| 6 | Table Lock | 17 µs |
+----+------------------------+--------+
| 7 | Init | 78 µs |
+----+------------------------+--------+
| 8 | Optimizing | 93 µs |
+----+------------------------+--------+
| 9 | Statistics | 174 µs |
+----+------------------------+--------+
| 10 | Preparing | 132 µs |
+----+------------------------+--------+
| 11 | Executing | 15 µs |
+----+------------------------+--------+
| 12 | Sending Data | 1,4 s |
+----+------------------------+--------+
| 13 | End Of Update Loop | 26 µs |
+----+------------------------+--------+
| 14 | Removing Tmp Table | 15 µs |
+----+------------------------+--------+
| 15 | End Of Update Loop | 9 µs |
+----+------------------------+--------+
| 16 | Removing Tmp Table | 11 µs |
+----+------------------------+--------+
| 17 | End Of Update Loop | 10 µs |
+----+------------------------+--------+
| 18 | Query End | 9 µs |
+----+------------------------+--------+
| 19 | Commit | 11 µs |
+----+------------------------+--------+
| 20 | Closing Tables | 10 µs |
+----+------------------------+--------+
| 21 | Unlocking Tables | 9 µs |
+----+------------------------+--------+
| 22 | Closing Tables | 18 µs |
+----+------------------------+--------+
| 23 | Starting Cleanup | 9 µs |
+----+------------------------+--------+
| 24 | Freeing Items | 21 µs |
+----+------------------------+--------+
| 25 | Updating Status | 69 µs |
+----+------------------------+--------+
| 26 | Reset For Next Command | 19 µs |
+----+------------------------+--------+
EXPLAIN (with sorting):
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| 1 | PRIMARY | games_titles | ref | game_id_index,lang_index | lang_index | 4 | const | 98995 | Using where; Using filesort |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| 1 | PRIMARY | games | eq_ref | PRIMARY | PRIMARY | 4 | test_db.games_titles.game_id | 1 | Using where; Using index |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | Using where |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | Using where |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| 2 | MATERIALIZED | games_devs | ALL | game_id_index | NULL | NULL | NULL | 98437 | Using where |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
| 3 | MATERIALIZED | games_publishers | ALL | game_id_index | NULL | NULL | NULL | 98437 | Using where |
+----+--------------+------------------+--------+--------------------------+--------------+---------+------------------------------------+-------+-----------------------------+
profiling (with sorting):
+----+------------------------+--------+
| 1 | Starting | 199 µs |
+----+------------------------+--------+
| 2 | Checking Permissions | 101 µs |
+----+------------------------+--------+
| 3 | Opening Tables | 56 µs |
+----+------------------------+--------+
| 4 | After Opening Tables | 18 µs |
+----+------------------------+--------+
| 5 | System Lock | 27 µs |
+----+------------------------+--------+
| 6 | Table Lock | 18 µs |
+----+------------------------+--------+
| 7 | Init | 94 µs |
+----+------------------------+--------+
| 8 | Optimizing | 211 µs |
+----+------------------------+--------+
| 9 | Statistics | 417 µs |
+----+------------------------+--------+
| 10 | Preparing | 138 µs |
+----+------------------------+--------+
| 11 | Sorting Result | 35 µs |
+----+------------------------+--------+
| 12 | Executing | 13 µs |
+----+------------------------+--------+
| 13 | Sending Data | 18 µs |
+----+------------------------+--------+
| 14 | Creating Sort Index | 2,4 s |
+----+------------------------+--------+
| 15 | End Of Update Loop | 35 µs |
+----+------------------------+--------+
| 16 | Removing Tmp Table | 23 µs |
+----+------------------------+--------+
| 17 | End Of Update Loop | 12 µs |
+----+------------------------+--------+
| 18 | Removing Tmp Table | 15 µs |
+----+------------------------+--------+
| 19 | End Of Update Loop | 14 µs |
+----+------------------------+--------+
| 20 | Query End | 12 µs |
+----+------------------------+--------+
| 21 | Commit | 15 µs |
+----+------------------------+--------+
| 22 | Closing Tables | 14 µs |
+----+------------------------+--------+
| 23 | Unlocking Tables | 12 µs |
+----+------------------------+--------+
| 24 | Closing Tables | 30 µs |
+----+------------------------+--------+
| 25 | Starting Cleanup | 17 µs |
+----+------------------------+--------+
| 26 | Freeing Items | 32 µs |
+----+------------------------+--------+
| 27 | Updating Status | 185 µs |
+----+------------------------+--------+
| 28 | Reset For Next Command | 48 µs |
+----+------------------------+--------+
CREATE TABLE `games` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`rating` double NOT NULL DEFAULT 0,
`date` date DEFAULT NULL,
`date_type` int(1) NOT NULL DEFAULT 1,
`img` varchar(500) DEFAULT NULL,
`img_type` int(10) NOT NULL,
`url` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `rating_index` (`rating`) USING BTREE,
KEY `date_index` (`date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=326678 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
CREATE TABLE `games_titles` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`title` varchar(150) DEFAULT NULL,
`lang` int(10) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
KEY `game_id_index` (`game_id`) USING BTREE,
KEY `lang_index` (`lang`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=652865 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
CREATE TABLE `games_descriptions` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`description` varchar(5000) DEFAULT NULL,
`lang` int(10) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
KEY `desc_index` (`description`(1024)) USING BTREE,
KEY `lang_index` (`lang`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=253401 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
CREATE TABLE `games_devs` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`dev` varchar(150) NOT NULL,
PRIMARY KEY (`id`),
KEY `game_id_index` (`game_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126699 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
CREATE TABLE `games_publishers` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`publisher` varchar(150) NOT NULL,
PRIMARY KEY (`id`),
KEY `game_id_index` (`game_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126696 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
CREATE TABLE `games_genres` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`genre_id` int(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `game_id_index` (`game_id`) USING BTREE,
KEY `genre_id_index` (`genre_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=121601 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
CREATE TABLE `games_times` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`time_id` int(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `game_id_index` (`game_id`) USING BTREE,
KEY `time_id_index` (`time_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126684 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
CREATE TABLE `games_statuses` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`status_id` int(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `game_id_index` (`game_id`) USING BTREE,
KEY `status_id_index` (`status_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=326387 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
I tried to make an auxiliary table "games_search", in which there were columns game_id, attributes (a text record of all attributes of the game to search for it via LIKE), but this option also did not fit, because there was no gain in speed, and alphabetical sorting is still needed, so without combining tables here definitely not enough. Please tell me what is the best way for me to do / which architecture is better to use to get rid of poor performance? I'm afraid to imagine what will happen on 1 million records
CREATE TABLE `games_devs` (
`id` int(50) NOT NULL AUTO_INCREMENT,
`game_id` int(50) NOT NULL,
`dev` varchar(150) NOT NULL,
PRIMARY KEY (`id`),
KEY `game_id_index` (`game_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=126699 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
-->
CREATE TABLE `NEW_games_devs` (
`game_id` int(50) NOT NULL,
`dev` varchar(150) NOT NULL,
PRIMARY KEY (game_id, dev),
KEY (dev, `game_id`)
) ENGINE=InnoDB AUTO_INCREMENT=126699 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
And here's a way to achieve the conversion:
INSERT INTO NEW_games_devs
SELECT game_id, dev
FROM games_devs;
RENAME TABLE games_devs TO OLD_games_devs,
NEW_games_devs TO games_devs ;
DROP TABLE OLD_games_devs;