How do we optimize a transaction-log VIEW in MySQL that will use a LIMIT when executed?

Question

Problem / Observation

A query that performs well outside of a view (with a limit 20), uses a suboptimal query plan when placed inside of a view if the limit 20 is not included in the view definition but added to the view execution.

To be clear, if limit 20 is inside the view, the execution plan looks very good. If we leave the limit 20 out of the view definition, but use it when we query the view, it uses a derived table that looks to me to be suboptimal.

Background

We have a transaction log that will grow very large. For diagnostic purposes we periodically query the transaction log.

To facilitate the query, we've created a view that returns results in a more human-readable form with useful information.

• Sometimes we just want 20 rows. Sometimes we want 100 rows.
• We always want the results in descending order by translog_id.

Question

What techniques can we use to have a variable limit on the view results but still have it perform well?

EXAMPLE Demonstrating the Problem

-- Simple translog query with LIMIT 20:
explain
select translog.translog_id,
    translog.transtype_code, 
    translog.target_id as target_id,
    translog.date_posted,
    translog.user_id appuser_userid,
    translog.details,
    GROUP_CONCAT( concat( auditlog.audit_col, "(", auditlog.oldval, "->", auditlog.newval, ")") SEPARATOR ", ") as audit_log
from translog 
left join auditlog on auditlog.translog_id = translog.translog_id
group by translog_id
order by translog_id desc
limit 20

RESULT:

Now, let's create a VIEW of that identical query, but leaving off the LIMIT 20 which we'll use later when querying the view.

-- VIEW doing the same thing but without the LIMIT 20 which will be applied at query execution
create or replace view opt as
select translog.translog_id,
    translog.transtype_code, 
    translog.target_id as target_id,
    translog.date_posted,
    translog.user_id appuser_userid,
    translog.details,
    GROUP_CONCAT( concat( auditlog.audit_col, "(", auditlog.oldval, "->", auditlog.newval, ")") SEPARATOR ", ") as audit_log
from translog 
left join auditlog on auditlog.translog_id = translog.translog_id
group by translog_id
order by translog_id desc;
-- limit 20

Let's look at the query execution plan when executing that view with LIMIT 20 added on

explain select * from opt limit 20

Note Not relevant, but wanted to add, I've simplified the query for this post -- our actual query joins a few more tables, but this here demonstrates the crux of my question/issue.

Executing the query returns the 20 rows:

Answer 1

I see that joining the view to a subset of key values appears to avoid the full table scan. This could be a workaround.. but shouldn't be necessary if the optimizer worked as I think it ought.

e.g.

-- the following grabs the last 15 IDs and joins that to the view
-- essentially doing the same thing as "limit 15" with desc order
select * from opt myresults 
join
  ( select translog_id 
    from translog 
    order by translog_id desc limit 15) last15 
  on myresults.translog_id = last15.translog_id