I'm using sqlalchemy and trying to do a query on an m2m table. I have a tag and want find all the events that match that tag:
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;
This is taking 2-3 seconds on my web server. On my laptop, it's faster(though my laptop is more powerful and has fewer rows.) The web server has about 300,000 rows in this table.
Here is an explain:
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
| 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 |
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
I have a feeling this is simple, but my google-fu is failing me.
Create Table Syntax:
| 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 |
edited: my bad, it was missing the "from events" I guess I'm wondering where I would want an index, I would think on the "id" column, which should have an index.
Best Answer
Lots of simplification and speedup:
events_tags
smells like a many:many mapping table. It can possibly be improved by following these tips.