MySQL - getting a count from the same table

Question

I'm using the following query:

SELECT tProducts.intProductId, tProducts.strSKU, tProducts.strProductName, (SELECT count(*) FROM tProducts as tProductOptions WHERE tProductOptions.bolDeleted = 0 AND tProductOptions.intOptionId = tProducts.intProductId) FROM tProducts WHERE bolDeleted = 0 AND intOptionId = 0

The table has intProductId as an integer, strSKU & strProductName as varchar and intProductId as an integer that relates back to intProductId if the row represents an option of the product. (This isn't my schema!)

I'm trying to get each of the products and the number of options.

It works and gives the results I expect, but it's slow when there is a lot of data.

I have added relevant indexes, but I'm sure there must be a better way of doing this... I just can't see it!

(Additionally, I'm trying to put this query into Laravel Fluent - but the best option seems to be raw. Am I missing something here too?) Thanks

would you post the table structure and what you want to retrieve as result — Satish Sharma
– Satish Sharma, Commented Nov 29, 2013 at 12:07

JezB · Accepted Answer · 2013-12-03 19:05:15Z

Try to rewrite the query using JOIN instead of dependent subquery.
It might be faster a bit (but there is no guarantee).

SELECT t.intProductId, t.strSKU, t.strProductName, q.cnt FROM tProducts t LEFT JOIN ( SELECT intOptionId, count(*) cnt FROM tProducts as tProductOptions WHERE tProductOptions.bolDeleted = 0 GROUP BY intOptionId ) q ON t.intProductId = q.intOptionId WHERE t.bolDeleted = 0 AND t.intOptionId = 0

Thanks kordirko - but I'm afraid it's not quicker. When I tested, my query was 0.862 seconds for 43847 vs yours at 1.012 seconds on the same data. But, I hadn't thought of doing it that way, so thanks...

Collectives™ on Stack Overflow

MySQL - getting a count from the same table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related