MySQL 8 slow with descending composite index

Question

I have the following query:

SELECT 
      shows.id, 
      shows.title, 
      shows.thumbnail, 
      shows.views, 
      shows.likes, 
      shows.dislikes, 
      shows.duration, 
      shows.hd, 
      shows.origin, 
      UNIX_TIMESTAMP(shows.upload_date) as upload_date 
   FROM 
      shows 
   WHERE 
          (shows.views, shows.id) < (0, 272990) 
      and shows.visible = 1 
   ORDER BY 
      shows.views DESC, 
      shows.id DESC 
   LIMIT 
      32

It takes around 0.8 seconds in MySQL 8.0.15.

I have tried indexes like this:

CREATE INDEX views_desc_id_desc_visible ON shows (views desc, id desc, visible)

As well as ones like this:

CREATE INDEX views_desc_id_desc ON shows (views desc, id desc)

And many other permutations. I've deleted them and redid it from scratch many times. Visible does have an index on itself.

When I do expain I see it only doing "Using where" while using a key called views_desc_id_desc_visible. Removing visible from the query doesn't change it's performance. Removing the two desc (so: order by views, id) does make it 0.0008 seconds.

Why isn't this going faster?

Update (copied from Comment)

SELECT  shows.id, shows.title, shows.thumbnail, shows.views, shows.likes,
        shows.dislikes, shows.duration, shows.hd, shows.origin,
        UNIX_TIMESTAMP(shows.upload_date) as upload_date
    FROM  shows
    WHERE      (shows.views <= 0)
      and  not (shows.views  = 0 and shows.id >= 272990)
      and  visible = 1
    ORDER BY  shows.views DESC, shows.id DESC
    LIMIT  32

fixes it. I just don't know why using that otherway ignores indexes.

Answer 1

visible must come first in the index.

INDEX(visible, views DESC, id DESC)

The general rule for building the optimal index is:

1. Include all columns in the WHERE that are tested with = constant.
2. Then other columns.

More details: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

There is a possible issue with the "row constructor" you are using with WHERE (shows.views, shows.id) < (0, 272990). That was essentially not optimized at all until 5.7.

The workaround (pre-5.7) was that the complicated expression you mentioned in your Comment was optimized. It is probably still optimized:

WHERE      (shows.views <= 0)
  and  not (shows.views  = 0 and shows.id >= 272990)

8.0 introduced honoring DESC in index declarations.

I am worried that the Optimizer has failed to put those two enhancements together to do what you are looking for.

Please show us EXPLAIN SELECT... for your attempts. Look especially at Key_len.

Even without 8.0, I would recommend

INDEX(visible, views, id)

In this case, the index can be traversed backwards to handle

WHERE visible = 0
  AND ...
ORDER BY views DESC, id DESC

That is assuming that the row constructor issues are resolved.