search

Home  >  Q&A  >  body text

mysql optimization - How to design the index when there are order by and sum in mysql multi-table joint query?

If there is currently a SQL statement that requires a joint query between two tables and uses group by and order by, how to design an index better?
Examples are as follows:

SELECT
    a.*, b.sum (money)
FROM
    a
JOIN b ON a.id = b.u_id
GROUP BY
    b. STATUS
ORDER BY
    b.time
    

In this example, there is a joint query of tables a and b, a sum() function on the fields of table b, and grouping and order by of query results. How to design an index to optimize such a query? ,Thank you everyone.

为情所困为情所困2720 days ago1439

reply all(1)I'll reply

  • 高洛峰

    高洛峰2017-06-15 09:23:07

    This query seems to have no practical meaning. If you look up table b alone, how would you write sql? All I can write is:

    SELECT SUM(money), status GROUP BY status
    

    Due to aggregation, the contents of u_id, time and other columns are uncertain, so I don’t know what your purpose is for sorting and correlating them.

    reply
    0
  • Cancelreply