Will this query use index?

Question

I've got a huge table that looks like this:

CREATE TABLE `images` (
  `image_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `data` mediumblob,
  PRIMARY KEY (`user_id`,`image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

on which I have to run a query which compresses the blob field. Will MySQL be able to use index with the following query:

UPDATE images SET data = COMPRESS(data) WHERE (user_id = ? AND image_id = ?) OR (user_id = ? AND image_id = ?) OR (...) OR (...);

I have to do it like this since there's no way I can update the whole table in a single query and I can't update by only using user_id.

EDIT: explain and update doesn't work, you guys know that, right?

Take a look at the `explain plan` for the update query and see whether it uses index scan or not. — Rahul, May 28 '12 at 20:14
Totally unrelated to whether the index will be used or not: do you know you can write the condition as: `WHERE (user_id,image_id) IN ( (?,?), (?,?), ..., (?,?) )` — ypercubeᵀᴹ, May 28 '12 at 20:38
@Rahul: It's only possible to see `EXPLAIN` plans for `UPDATE` queries in MySQL >= 5.6 — eggyal, May 28 '12 at 20:44
@ypercube: this one looks nice but won't use the index (at least in `5.5` and below) — Quassnoi, May 28 '12 at 20:45
You can see `EXPLAIN` plan for `UPDATE` by replacing UPDATE with SELECT - it will use the same indexes as the select — Maxim Krizhanovsky, May 28 '12 at 20:45

score 2 · Answer 1 · answered May 28 '12 at 20:17

2

Yes, your update will use the indexes on the table, since the only columns referred after the WHERE are the ones your primary key stands of.

If you are unsure of what a query will use, feel free to use the EXPLAIN command: http://dev.mysql.com/doc/refman/5.0/en/explain.html

answered May 28 '12 at 20:17

hpityu

891
10
21

`EXPLAIN` is only possible for `UPDATE` in MySQL >= 5.6 – eggyal May 28 '12 at 20:45
True, but still the query can be transformed easily into a SELECT statement, where the selected values are the same. You can assume that the two statements will use a similar plan. – hpityu May 29 '12 at 11:38

score 2 · Answer 2 · answered May 28 '12 at 20:34

2

Use

EXPLAIN SELECT data FROM images
  WHERE (user_id = ? AND image_id = ?)
    OR (user_id = ? AND image_id = ?)
    OR (...)
    OR (...);

answered May 28 '12 at 20:34

Neil

54,642
8
60
72

score 1 · Answer 3 · answered May 28 '12 at 20:16

1

In your example, I'd expect MySQL to use the primary key as the clustered index. That means the index stores all columns, and is actually the only version of the data on disk.

So yes, it will use the index. With a condition including ors, I'd expect MySQL to scan the index (not seek.)

answered May 28 '12 at 20:16

Andomar

232,371
49
380
404

Is scans something to be afraid of? My first attempt used only `image_id = ?` which kind of took down the DB machine. – jim May 28 '12 at 20:26
Scans can cause a lot of disk I/O. A good way to avoid them is to run the query for only one image at a time, without `or` in the `where` clause. – Andomar May 28 '12 at 20:29
Thank you Andomar. Update one row per query was my backup plan -- I think I'll do that instead. – jim May 28 '12 at 20:32
1

@jim image_id only can't use the index, as the left-most part of the index is not used. So the index can be used on queries that use only user_id, but not on those that use only image_id – Maxim Krizhanovsky May 28 '12 at 20:50
Darhazer yes I know. I ran that query without digging in to the tables indexes. I just assumed that image_id was a sole primary. Stupid, I know. – jim May 28 '12 at 20:57

score 1 · Answer 4 · answered May 28 '12 at 20:20

1

This is better to check using EXPLAIN than to speculate about too much.

Before even running EXPLAIN, issue ANALYZE TABLE to make sure that the query optimizer has the best chances to find an optimum query plan.

answered May 28 '12 at 20:20

Jirka Hanika

13,301
3
46
75

`EXPLAIN` is only possible for `UPDATE` in MySQL >= 5.6 – eggyal May 28 '12 at 20:45
Good point. I'm sometimes using a `SELECT` modification as the first approximation when researching variants of a long taking DML statement, but that's just another kind of speculation. – Jirka Hanika May 28 '12 at 20:56

score 1 · Answer 5 · answered May 28 '12 at 20:44

1

Yes, it will most probably use the index (unless your conditions have really low cardinality).

MySQL also supports this syntax:

(user_id, image_id) IN ((user1, image1), (user2, image2), (user3, image3))

but this one will not use the index (this is just an implementation flaw).

You can also use this query:

UPDATE  (
        SELECT  user1 AS user_id, image1 AS image_id
        UNION ALL
        SELECT  user2 AS user_id, image2 AS image_id
        UNION ALL
        SELECT  user3 AS user_id, image3 AS image_id
        ) q
JOIN    images i
ON      (i.user_id, i.image_id) = (q.user_id, q.image_id)
SET     i.data = COMPRESS(i.data)

which will also use the index.

answered May 28 '12 at 20:44

Quassnoi

413,100
91
616
614

I don't suppose you've already blogged (or can readily locate an article elsewhere) about that implementation flaw? I'd love to know more (I also wonder whether it is responsible for the problem I faced [here](http://stackoverflow.com/questions/10211029/optimising-mysql-queries-across-hierarchical-data)). – eggyal May 28 '12 at 20:49
@eggyal: uhh, I haven't been blogging for a while so I don't remember, honestly :) Just create a table with composite index and run `EXPLAIN` over this query, you'll see a fullscan even with a `FORCE INDEX`. – Quassnoi May 28 '12 at 20:50
@Quassnoi: Does the flaw affect inequality conditions, like this one, too?: `(a,b) <= (1,2)` – ypercubeᵀᴹ May 28 '12 at 20:53
@ypercube: yes, it does. Haven't checked `5.6` yet, but in `5.5` only equalities yield an index seek. – Quassnoi May 28 '12 at 20:54

Will this query use index?

5 Answers5