What does USE INDEX() without an index_list do?

Question

I had a query that was taking a very long time to process (>60 secs):

                SELECT
                    r.name,
                    GROUP_CONCAT(DISTINCT s.name
                        SEPARATOR ',') AS other_s,
                    COUNT(DISTINCT sr.pk) AS total_u,
                    r.distance,
                    r.pk as 'index'
                FROM
                    st.r r
                        LEFT JOIN
                    st.rs rs ON (r.pk = rs.rfk)
                        INNER JOIN
                    st.srr srr ON (srr.rfk = r.pk)
                        LEFT JOIN
                    st.s s ON (rs.sfk = s.pk) INNER JOIN
                    st.sr sr ON (srr.srfk = sr.pk)
                WHERE r.delete_date IS NULL AND
                      r.pk IN (SELECT
                            route_fk
                        FROM
                            st.srr
                        WHERE
                            srfk = 3)
                GROUP BY r.name , r.distance

I was advised to add in use index() to this line:

                FROM
                    st.r r use index()

And, almost magically, the query now takes around 15 secs to process.

According to: https://dev.mysql.com/doc/refman/8.0/en/index-hints.html#:~:text=The%20USE%20INDEX%20(%20index_list%20)%20hint,some%20particular%20index%20or%20indexes

It is syntactically valid to omit index_list for USE INDEX, which means “use no indexes.” Omitting index_list for FORCE INDEX or IGNORE INDEX is a syntax error.

My question is, how does omitting index_list and therefore using no indexes, actually speed up this query?

Answer 1

• Using an index means looking through the index (which might be efficient), then jumping over to the data. Note that the INDEX and the data are stored in separate BTrees.
• Using no indexes avoids the back-and-forth.
• Sometimes the optimizer makes the wrong decision.

Your query probably suffers from "explode-implode":

1. The JOINs create a big temporary table with the combinations of info from the tables.
2. The GROUP BY shrinks the info back down.

Meanwhile, you had to do DISTINCT to avoid getting duplicate concatenated values and inflated COUNTs.

Try to rewrite the query by getting the GROUP_CONCAT and the COUNT with as simple a query as possible. Then do the rest of the JOINs to get the rest of the info. The will probably run a lot faster and avoid the confusion over what, if any, index to use.

Mixing LEFT JOIN and INNER JOIN makes my head spin, so I don't want to try to suggest a rewrite.