Mysql – This simple thesql query is taking 2-3 seconds

MySQLperformancequery-performance

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:

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 smells like a many:many mapping table. It can possibly be improved by following these tips.