I have a table called foo with these fields:
- id
- type
- parentId
I want to select a list of parent IDS, in the descending order of their COUNT(*) of how many times they appear in the table. Something like this:
SELECT DISTINCT parentId FROM `foo`
ORDER BY (COUNT(parentId) DESC where parentId = parentId)
How can this be done in the most efficient way and putting the least load on the server?
There can be thousands-hundreds of thousands of records in the table, so manually going through each record is not acceptable..
Simply by applying a GROUP BY clause, and assuming you have an index , FOREIGN KEY, or PRIMARY KEY on parentId, the performance should be quite good. (parentId looks like it is likely a FORIEGN KEY, so be sure to define the constraint to enforce indexing).
SELECT `parentId`
FROM `foo`
GROUP BY `parentId`
ORDER BY COUNT(*) DESC
How can this be done in the most efficient way and putting the least load on the server?
The key is the the most efficient way.
Not a Count() for sure, but most efficient is... to read a field, which you are storing the Count result. You can update it with a trigger or after insert.
Especially when
There can be thousands-hundreds of thousands of records in the table
