How can I get mysql to use indexes with a simple AND + OR query?

Question

This is using MySQL 5.5. I cannot seem to convince MySQL to use indexes for these queries and they are taking anywhere from 2-10 seconds to run on a table with 1.1 million rows.

Table:

CREATE TABLE `notifiable_events` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`key` varchar(255) NOT NULL,
`trigger_profile_id` int(10) unsigned DEFAULT NULL,
`model1` varchar(25) NOT NULL,
`model1_id` varchar(36) NOT NULL,
`model2` varchar(25) NOT NULL DEFAULT '',
`model2_id` varchar(36) NOT NULL DEFAULT '',
`event_data` text,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `key` (`key`),
KEY `notifiable_events__trigger_profile` (`trigger_profile_id`),
KEY `deleted` (`deleted`),
KEY `noti_evnts__m2` (`model2`),
KEY `noti_evnts__m1` (`model1`),
CONSTRAINT `notifiable_events__trigger_profile` FOREIGN KEY (`trigger_profile_id`) REFERENCES `profiles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1177918 DEFAULT CHARSET=utf8

QUERY:

  SELECT * 
    FROM notifiable_events 
    WHERE (`model1` = 'page' AND `model1_id` = '54321') 
       OR (`model2` = 'page' AND `model2_id` = '12345');

EXPLAIN(S):

mysql> EXPLAIN EXTENDED SELECT * FROM notifiable_events WHERE (`model1` = 'page' AND `model1_id` = '922645') OR (`model2` = 'page' AND `model2_id` = '922645')\G

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: notifiable_events
             type: ALL
    possible_keys: noti_evnts__m2,noti_evnts__m1,noti_evnts__m1_m2
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 1033088
         filtered: 100.00
            Extra: Using where
    1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT * FROM notifiable_events WHERE (`model1` = 'page' AND `model1_id` = '922645') OR (`model1` = 'page' AND `model1_id` = '922645')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: notifiable_events
         type: ref
possible_keys: noti_evnts__m1,noti_evnts__m1_m2
          key: noti_evnts__m1
      key_len: 77
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN EXTENDED SELECT * FROM notifiable_events WHERE (`model2` = 'page' AND `model2_id` = '922645') OR (`model2` = 'page' AND `model2_id` = '922645')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: notifiable_events
         type: ref
possible_keys: noti_evnts__m2
          key: noti_evnts__m2
      key_len: 77
          ref: const
         rows: 428920
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

You can see that if I only use model1 or only use model2 then it will use the index, however, as soon as I try to use both of them together it gives up on the index entirely and does a full table scan. I have already tried FORCE INDEX and I have tried a combination of multi-key indexes (and I left one in place for this table as an example). I have also tried rearranging the order of elements in the query but that doesn't seem to have any effect either.

UPDATE: I forgot to mention that I already tried ANALYZE and OPTIMIZE (multiple times each, no change). I also already tried an index on the *_id (the cardinality is very bad and those columns are mostly unique entries) and a multi index with all 4 columns being used in the query. No improvement or use of the index in either of those cases either.

It seems like it should be really easy to use an index to limit the rows being checked here so I hope I am just missing something.

If seems that the `x_id` columns should be indexed (instead)? In any case, what if the table statistics are updated? — , Jul 12 '12 at 01:50

O. Jones · Accepted Answer · 2012-07-12T11:32:40.597

4

or clauses goof up query optimization sometimes.

You could try a union, something like this. It may reactivate the indexes.

SELECT * 
  FROM notifiable_events 
 WHERE  `model1` = 'page' AND `model1_id` = '54321'
 UNION
SELECT * 
  FROM notifiable_events 
 WHERE `model2` = 'page' AND `model2_id` = '12345'

Edit, to answer the question in the comment

If you're trying to update records using this kind of selection scheme, you can try this:

UPDATE notifiable_events
   SET what=ever,
       what=ever,
       what=else 
 WHERE id IN (
   SELECT id 
     FROM notifiable_events 
    WHERE  `model1` = 'page' AND `model1_id` = '54321'
    UNION
   SELECT id 
     FROM notifiable_events 
    WHERE `model2` = 'page' AND `model2_id` = '12345'
 )

(Note that it's helpful when you're using Stackoverflow to explain what you're actually trying to do. It is of course OK to reduce a complex problem to a simpler one if you can, but saying you're doing a SELECT when you're actually doing an UPDATE is, umm, an oversimplification.)

edited Jul 12 '12 at 11:32

answered Jul 12 '12 at 01:52

O. Jones

103,626
17
118
172

This would require some DISTINCT-ish to make the results identical .. (or the same record could be selected multiple times) – Jul 12 '12 at 01:53
2

Documentation says: The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements." – O. Jones Jul 12 '12 at 01:56
1

@pst `union` sorts and removes duplicates. `union all` leaves all rows intact (in the order coded) – Bohemian Jul 12 '12 at 01:58
In this case, UNION will exactly mimic "OR" – Bohemian Jul 12 '12 at 02:11
Is it possible to use union with an UPDATE statement? Most of these queries are actually updates but since MySQL doesn't support explain on update I posted the results of SELECT (even though we do not actually have a select like this) – Aaron Zeckoski Jul 12 '12 at 10:57

How can I get mysql to use indexes with a simple AND + OR query?

1 Answers1