CODEWITHSUNDEEP
mysqlsqlmariadb
Lynob
Lynob

Reputation: 5327

How to optimize the following SELECT query

We have the following table

id  # primary key

device_id_fk 

auth # there's an index on it

old_auth  # there's an index on it

And the following query.

$select_user = $this->db->prepare("
        SELECT device_id_fk 
        FROM wtb_device_auths AS dv 
        WHERE (dv.auth= :auth OR dv.old_auth= :auth) 
        LIMIT 1
");

explain, I can't reach the server of the main client, but here's another client with fewer data

enter image description here

Since there's a lot of other updates queries on auth, update queries start getting written to the slow query log and the cpu spikes

If you remove the index from auth, then the select query gets written to the slow query log, but not the update, if you add an index to device_id_fk, it makes no difference.

I tried rewriting the query using union instead of or, but I was told that there was still cpu spike and the select query gets written to the slow query log still

$select_user = $this->db->prepare("
    (SELECT device_id_fk 
     FROM wtb_device_auths 
     AS dv WHERE dv.auth= :auth) 
    UNION ALL
    (SELECT device_id_fk 
     FROM wtb_device_auths AS dv 
     WHERE dv.old_auth= :auth)
     LIMIT 1"
    );
");

Explain

enter image description here

Most often, this is the only query in the slow query log. Is there a more optimal way to write the query? Is there a more optimal way to add indexes? The client is using an old MariaDB version, the equivalent of MYSQL 5.5, on a centos 6 server running LAMP

Additional info

enter image description here

enter image description here

The update query that gets logged to the slow query log whenever an index is added to auth is

$update_device_auth = $this->db->prepare("UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk");

Upvotes: 2

Views: 121

Answers (2)

Rick James
Rick James

Reputation: 142298

OR usually leads to a slow, full-table scan. This UNION trick, together with appropriate INDEXes is much faster:

( SELECT  device_id_fk
    FROM  wtb_device_auths AS dv
    WHERE  dv.auth= :auth
    LIMIT  1 )
UNION ALL
( SELECT  device_id_fk
    FROM  wtb_device_auths AS dv
    WHERE  dv.old_auth= :auth
    LIMIT  1 )
LIMIT 1

And have these "composite" indexes:

INDEX(auth, device_id)
INDEX(old_auth, device_id)

These indexes can replace the existing indexes with the same first column.

Notice that I had 3 LIMITs; you had only 1.

That UNION ALL involves a temp table. You should upgrade to 5.7 (at least); that version optimizes away the temp table.

A LIMIT without an ORDER BY gives a random row; is that OK?

Please provide the entire text of the slowlog entry for this one query -- it has info that might be useful. If "Rows_examined" is more than 2 (or maybe 3), then something strange is going on.

Upvotes: 0

Schwern
Schwern

Reputation: 164809

Your few indexes should not be slowing down your updates.

You need two indexes to make both your update and select perform well. My best guess is you never had both at the same time.

UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk

You need an index on device_id_fk for this update to perform well. And regardless of its index it should be declared a foreign key.

    SELECT device_id_fk 
    FROM wtb_device_auths AS dv 
    WHERE (dv.auth= :auth OR dv.old_auth= :auth) 
    LIMIT 1

You need a single combined index on auth, old_auth for this query to perform well.

Separate auth and old_auth indexes should also work well assuming there's no too many duplicates. MySQL will merge the results from the indexes and that merge should be fast... unless a lot of rows match.

If you also search for old_auth alone, add an index on old_auth.

And, as others have pointed out, the select query could return one of several matching devices with a matching auth or old_auth. This is probably bad. If auth and old_auth are intended to identify a device, add a unique constraint.

Alternatively, you need to restructure your data. Multiple columns holding the same value is a red flag. It can result in a proliferation of indexes, as you're experiencing, and also limit how many versions you can store. Instead, have just one auth per row and allow each device to have multiple rows.

create table wtb_device_auths (
  id serial primary key,
  device_id bigint not null references wtb_devices(id),
  auth text not null,
  created_at datetime not null default current_timestamp,

  index(auth)
);

Now you only need to search one column.

select device_id from wtb_device_auths where auth = ?

Now one device can have many wtb_device_auths rows. If you want the current auth for a device, search for the newest one.

select device_id
from wtb_device_auths
where device_id = ?
order by created_at desc
limit 1

Since each device will only have a few auths, this is likely to be plenty fast with the device_id index alone; sorting the handful of rows for a device will be fast.

If not, you might need an additional combined index like created_at, device_id. This covers searching and sorting by created_at alone as well as queries searching and sorting by both created_at and device_id.

Upvotes: 1

Related Questions