status table join slows down query by 10 seconds
A PRIMARY KEY is a UNIQUE KEY and is an INDEX.  Don't re-declare id as UNIQUE.

Don't start an index with the PRIMARY KEY; it is likely never to be used.

Don't index flags; they are unlikely to be used.

So, I have just argued against all 6 secondary indexes, leaving only PRIMARY KEY(id)

There are only 3 rows in assessment_status, correct?  It is actually faster to do a table scan than to use an index.

Your first query seems to need to find the one row with completed = 1, then find the rows in au with au.idStatus=3, correct?

The EXPLAIN that you provide does not match the SELECT.  There are 2 tables in the select, but only one row in the explain??

Ditto for the second EXPLAIN.  But, there I see id=5 and id=3, implying that there are even more tables!  It could be that the order of tackling the tables was drastically changed; this could be a cause or clue of the timing differences.

How big is au?  The missing line in the EXPLAINs probably says that it will be a table scan, and gives an estimate of the number of rows.

Was the 12-second run tried twice?  I ask because the first run may have spent a lot of time fetching data from disk and the second would be running entirely from cached data in RAM.  This could also explain why the other query took only 0.6 sec.

If you are going to have the Question focus on a portion of the real query, then provide a shrunken test case that demonstrates it.  Else it may not be a valid shrinking, and we cannot necessarily help your real query.

Still, I will guess at what is going on.  The first query (I guess) had only one thing in the WHERE clause, a reference to as_s.  This tricked the Optimizer into deciding to start with that table.  But because of stuff not in evidence, some other table would be better to start with.  The second query seems to have taken that path; it could not start with as_s anyway.  A guess: there is no WHERE in the second query?