The join MySQL request becomes slow when sorting by datetime, not slow sorting by ID

advertisements

I have a MySQL (actually MariaDB) query which is running much slower and falling back on filesort when I try sorting by a DATETIME field (sighted) instead of the primary key. I have an index on sighted, but for some reason it does not seem to be using it. How can I index this table so that it can be sorted by the DATETIME field efficiently?

select
    user_id , t.name, f.value, y.name
    from posts p
        left join taxon_sightings t on (t.post_id = p.id)
        left join fields f on (f.foreign_id = t.id)
        left join fieldtypes y on (y.id = f.fieldtype_id)
    group by p.id
    order by p.sighted desc
    limit 10;

When I sort by the sighted DATETIME field:

MariaDB [dbase]> explain [yadda yadda, as above]
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref                            | rows | Extra                           |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL    | NULL          | NULL       | NULL    | NULL                           | 1759 | Using temporary; Using filesort |
|  1 | SIMPLE      | t     | ref    | post_id       | post_id    | 4       | dataportal_test.p.id           |   12 |                                 |
|  1 | SIMPLE      | f     | ref    | foreign_id    | foreign_id | 4       | dataportal_test.t.id           |    6 | Using where                     |
|  1 | SIMPLE      | y     | eq_ref | PRIMARY       | PRIMARY    | 4       | dataportal_test.f.fieldtype_id |    1 | Using where                     |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+---------------------------------+

Compare to when I sort by ID:

+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref                            | rows | Extra       |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | p     | index  | NULL          | PRIMARY    | 4       | NULL                           |    1 |             |
|  1 | SIMPLE      | t     | ref    | post_id       | post_id    | 4       | dataportal_test.p.id           |   12 |             |
|  1 | SIMPLE      | f     | ref    | foreign_id    | foreign_id | 4       | dataportal_test.t.id           |    6 | Using where |
|  1 | SIMPLE      | y     | eq_ref | PRIMARY       | PRIMARY    | 4       | dataportal_test.f.fieldtype_id |    1 | Using where |
+----+-------------+-------+--------+---------------+------------+---------+--------------------------------+------+-------------+


I don't think you can. The only trick that I can think of is to try this:

select user_id, t.name, f.value, y.name
from posts p left join
     taxon_sightings t
     on (t.post_id = p.id) left join
     fields f
     on (f.foreign_id = t.id) left join
     fieldtypes y
     on (y.id = f.fieldtype_id)
group by p.sighted, p.id
---------^ very important
order by p.sighted desc
limit 10;

Then include an index on posts(sighted, id). The group by with the order by might take advantage of the index. If you are lucky . . .