0

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.

  • Add an index on (tag_id, event_id) and remove the join to tags from the subquery completely. But please, do as others have already suggested: add the CREATE TABLE for all 3 tables in the question. – ypercubeᵀᴹ Nov 29 '16 at 16:08
0

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 possible be improved by following these tips.

| improve this answer | |
0

just as one more variant, for common case when need extract not only events, but for example tag name:

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;

In some cases MySQL do not want use index, so we little help him (if we sure we request small part of whole table):

FROM 
    events FORCE INDEX (PRIMARY)

and plan will be:

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  

indexes - must be, all comparable columns must be same type, such as events.id bigint(20) == events_tags.event_id bigint(20)

| improve this answer | |
  • Yeah, this query is really weird. I ended up test out a different subquery: SELECT * FROM events WHERE events.id IN (SELECT event_id FROM events_tags, tags WHERE events.id = events_tags.event_id AND tags.id = events_tags.tag_id AND tags.id = 617) LIMIT 50; and that works in .02 seconds. The challenge here is I'm using SQLAlchemy. The query I included came from the SQLAlchemy query log. I guess the next step is either to use a manual query or see if I can file a bug report with SQLAlchemy – mikkergp Nov 30 '16 at 5:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.