Optimizing order by with a limit in MySql

Question

I have a 3 million records table called "transactions" .

CREATE TABLE transactions(
  id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  lookupAId int(6) NOT NULL,
  .....
  updateDate TIMESTAMP
)

In the worst case the user will specify no filters and the query would looks like this :

select * from transactions
   join lookupA on (well indexed columns) 
   .. ( 12 lookup table joins) 
order by updateDate limit 500

Without the order by clause the query runs in milliseconds, but with the order by it takes about a minute. The table is projected to grow to 12-15 million records.

1. My SLA is to get results in under a second, is it possible in MySql ?
2. How can I optimize the order by clause to make this perform.

I run MySql 5.7 in xLarge memory optimized RDS instance in AWS

UPDATE 1 updateDate has a time component and is indexed (B-tree, non-unique)

Update 2 This worked , although I don't know why

SELECT * FROM (select * from transactions order by updateDate) transactions
   join lookupA on (well indexed columns) 
   .. ( 12 lookup table joins) 
   limit 500

Answer 1

MySQL is probably doing a lot of work on the query before limiting the query size with limit. This seems to be a known weakness of MySQL.

Try doing the select from transactions in a subquery to limit the result set size before doing the joins.

SELECT * FROM (select * from transactions order by updateDate limit 500) transactions
   join lookupA on (well indexed columns) 
   .. ( 12 lookup table joins)