Hey,

One important optimization you can use and it's often missed out (and it's going to be needed more as we normalize more tables) is join decomposition. It basically means you don't join and query but do two (or several) queries separately in your code. This might seem counter intuitive but it's pretty useful for several reasons (like MySQL will cache the normalizing table and answer faster or you yourself can cache some parts). You can read more about those in "High Performance MySQL" book.

HTH

On Mon, Jun 3, 2019 at 3:43 AM Jesse Plamondon-Willard <pathoschild@gmail.com> wrote:

Hello,

Some Toolforge SQL queries have much worse performance when updated for the new comment table.

For example, see one previous SQL query and its updated version which just adds a join on the comment table. The change adds ten dependent subqueries and increases Stalktoy load times from ≈15 seconds to ≈245 seconds, and the same change for another query was enough to have my tools temporarily rate-limited.

I haven't found a way to update those queries efficiently. Is there an optimisation I'm missing? Why does the comment view need subqueries on ten other tables, and is there an alternate version without the subqueries for cases where we just need to join by ID?

--
Jesse Plamondon-Willard (Pathoschild)
_______________________________________________
Wikimedia Cloud Services mailing list
Cloud@lists.wikimedia.org (formerly labs-l@lists.wikimedia.org)
https://lists.wikimedia.org/mailman/listinfo/cloud