这个简单的mysql查询需要2-3秒。

Question

我使用sqlalchemy并试图对m2m表进行查询。我有一个标记，希望找到与该标记匹配的所有事件：

SELECT * FROM events 
WHERE EXISTS ( SELECT 1 FROM events_tags, tags 
               WHERE events.id = events_tags.event_id 
               AND tags.id = events_tags.tag_id 
               AND tags.id = 617)  
LIMIT 50;

这在我的网络服务器上需要2-3秒。在我的笔记本电脑上，它更快(尽管我的笔记本更强大，行更少)。web服务器在这个表中有大约30万行。

这里有一个解释：

+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
| id   | select_type        | table       | type   | possible_keys  | key     | key_len | ref                      | rows   | Extra       |
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
|    1 | PRIMARY            | events      | ALL    | NULL           | NULL    | NULL    | NULL                     | 310172 | Using where |
|    2 | DEPENDENT SUBQUERY | tags        | const  | PRIMARY        | PRIMARY | 8       | const                    |      1 | Using index |
|    2 | DEPENDENT SUBQUERY | events_tags | eq_ref | PRIMARY,tag_id | PRIMARY | 16      | timeline.events.id,const |      1 | Using index |
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+

我有一种感觉，这很简单，但是我的google-fu让我失望了。

创建表语法：

 | events | CREATE TABLE `events` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `title` varchar(255) DEFAULT NULL,
   `context` varchar(255) DEFAULT NULL,
   `text` mediumtext,
   `wikidata_id` bigint(20) DEFAULT NULL,
   `start_day` smallint(6) DEFAULT NULL,
   `start_month` smallint(6) DEFAULT NULL,
   `start_year` bigint(20) DEFAULT NULL,
   `end_day` smallint(6) DEFAULT NULL,
   `end_month` smallint(6) DEFAULT NULL,
   `end_year` bigint(20) DEFAULT NULL,
   `is_number` tinyint(1) DEFAULT NULL,
   `version` int(11) DEFAULT NULL,
   `number` bigint(20) DEFAULT NULL,
   `start_name` varchar(255) DEFAULT NULL,
   `end_name` varchar(255) DEFAULT NULL,
   `subject_title` varchar(255) DEFAULT NULL,
   `object_title` varchar(255) DEFAULT NULL,
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=327132 DEFAULT CHARSET=utf8 |

编辑:我的错，它错过了“从事件”-我想我想知道我会想要一个索引，我会想在"id“列，其中应该有一个索引。

Answer 1

许多简化和加速：

SELECT e.*
    FROM ( SELECT DISTINCT event_id
             FROM events_tags
             WHERE tag_id = 617 ) AS et
    JOIN events  AS e  ON e.id = et.event_id
ORDER BY ???  -- else get random 50??
LIMIT 50;

events_tags闻起来有很多种:很多映射表。可以通过遵循这些提示来改进它。

Answer 2

就像另一个变体一样，对于需要提取事件的常见情况，例如标记名称：

SELECT 
    events.*, 
    tags.tag_name 
FROM 
    events FORCE INDEX (PRIMARY)
INNER JOIN events_tags ON events.id = events_tags.event_id
INNER JOIN tags ON tags.id = events_tags.tag_id
WHERE tags.id = 617

LIMIT 50;

在某些情况下，MySQL不希望使用索引，因此我们很少帮助他(如果我们确定请求整个表的一小部分)：

FROM 
    events FORCE INDEX (PRIMARY)

计划如下：

1   SIMPLE  tags            const   PRIMARY PRIMARY     4   const   1   100.00  
1   SIMPLE  events_tags     ref event_id,tag_id tag_id  5   const   3   100.00  Using where
1   SIMPLE  events          eq_ref  PRIMARY PRIMARY     4   test_db.events_tags.event_id    1   100.00  

索引-必须是，所有可比较的列都必须是相同的类型，例如events.id bigint(20) == events_tags.event_id bigint(20)