Re: calculating total products in each category

[Rick James]

SELECT  parent.ctgid, COUNT(1) total
    FROM  category AS node,
          category AS parent,
          product
    WHERE  node.lft BETWEEN parent.lft AND  parent.rgt
      AND  node.ctgid = product.category
      AND  product.active = 'Y'
    GROUP BY  parent.ctgid
    ORDER BY  node.lft

This will be a mess to optimize:
node.lft BETWEEN parent.lft AND parent.rgt
because it effectively needs to do a lot of work comparing fields in the two tables. Yet you have no index to help it; see if adding
INDEX(lft)
helps.

Better yet, use this instead:
INDEX(lft, rgt, ctgid)
That might make it "Using index".

Having to filter on product.active = 'Y' is a nuisance. Is it reasonable to remove them from that table? (Cannot predict how much this might help.)

Remove
ORDER BY node.lft
It is fetched (except for the WHERE), so has no meaning.