MySQL – Using ‘OR’ Against Indexed Columns Doesn’t Use Indexes

indexmariadbmariadb-10.1MySQL

In the query below, from and tid are indexes of the replies table.

SELECT * FROM `replies`
WHERE `from`="<userId>"
OR `tid` IN (SELECT `tid` FROM `posts` WHERE `from`="<userId>")

By using "OR", it seems that it does a full table scan (~3 million rows).
The EXPLAIN says that a possible key would be from, but then it doesn't use any.

However, in the query below, frid_lt and frid_gt are indexed. The two columns are in a complex index (frid_lt, frid_gt), but frid_gt has also its own index.

SELECT `mid` FROM `messages`
WHERE `frid_lt`="<userId>" OR `frid_gt`="<userId>"

And this query DOES use two indexes. The EXPLAIN says "index_merge" and "Using sort_union(frid_lt,frid_gt); Using where".

Why does the first query not use an index merge?
Is there any improvement I can make to make the engine use an index merge as well?

Best Answer

OR does not optimize well. A common workaround is to use UNION:

( SELECT * FROM replies WHERE `from` = "..." )
UNION ALL   -- or UNION DISTINCT if you know there are no dups
( SELECT r.* FROM replies AS r
    JOIN posts AS p  ON p.tid = r.tid
    WHERE p.from = "..." )

Notice that I also avoided the usually-inefficient IN ( SELECT ... )

For further performance, have these indexes:

replies:  INDEX(`from`)
posts:    INDEX(`from`, tid)  -- in this order
replies:  INDEX(tid)

(And note that the PRIMARY KEY is an index, so don't add a redundant index.)

In your second example, the "index merge" that you experienced may or may not be faster than a UNION.

Oh, it's an UPDATE

To optimize UPDATE, do two separate UPDATEs (no UNION, no OR). One straightforwardly checks from. The other is a "multi-table UPDATE" (see the manual) similar to the second select above.

Related Solutions

Mysql – Simple Slow Query Not Using Indexes

Your query actually is using the idx_ksours index.

It seems like a pretty sensible assumption that Using index in the Extra field means the query is using the index, and then when that isn't shown in Extra then the index isn't being used... but that's actually not what that means.

Indexes, as you likely know, contain copies of the indexed column(s) data, in sorted order, along with a copy of the primary key of the row so the row referenced by the index can be retrieved.

When all of the data the query needs to examine in a table is available inside one index, the optimizer realizes that reading the actual row data is unnecessary -- everything needed is actually contained in the index, and will be read from the index -- eliminating the extra lookup to retrieve the associated row from the full table. This is what Using index means.

On the other hand, if the optimizer will using an index to optimize the query, the name of that index is shown in key column of EXPLAIN SELECT.

+------------+
| key        |
+------------+
| idx_ksours |
| postid     |
+------------+

So, you're actually using idx_ksours.

That's the good news. The bad news, of course, is that this index doesn't offer enough of an optimization for your entire query.

Note that the second column in idx_ksours is visible, which is not evaluated by anything in the join condition or where clause, which means none of the other columns in that index are not used for this query -- it's only helping you find rows WHERE post.threadid = 562942.

The comment from @dezso addresses this -- if you had an index with all-relevant columns, in a helpful order, that index would provide more benefit for the query.

MySQL Performance – Indexes Not Used When Joining Tables

What about this this should remove the need for an "Using temporary; Using filesort" because the data is in the right sort already.

You need to know the trick why MySQL needs "Using temporary; Using filesort" to remove that need.

See second sqlfriddle for an explain about removing the need

SELECT
      *
    FROM base_article

    STRAIGHT_JOIN 
      mag_article
    ON
      (mag_article.basearticle_ptr_id = base_article.id)

    WHERE
      base_article.is_published = 1

    ORDER BY
      base_article.date_published DESC

see http://sqlfiddle.com/#!2/302710/2

Works pretty good i needed this also some time ago for Country / city tables see demo here with example data http://sqlfiddle.com/#!2/b34870/41

Edited you also may want to analyse this answer if base_article.is_published = 1 always returns 1 record like your explain explained an INNER JOIN deliverd table may give better performance like the queries in the answer below

https://stackoverflow.com/questions/18738483/mysql-slow-query-using-filesort/18774937#18774937

Best Answer

Related Solutions

Mysql – Simple Slow Query Not Using Indexes

MySQL Performance – Indexes Not Used When Joining Tables

Related Question