You should name your aliases in such a way that looking at them tells you something about their meaning.
pm tell nothing to the next programmer who will have to maintain your code. It's better to type a few extra letters and add clarity, like
SELECT * is not very efficient as SQL has to go back and look up every column's DDL in your information schema. Only
SELECT the columns you actually need for faster query.
You have this:
FROM products p, productmedia pm
WHERE p.userId = '$userId'
AND p.id = pm.productId
AND pm.sortOrder = '0'
This is bad practice and will likely become deprecated eventually. Instead, use an explicit type of join. I will use
INNER JOIN in this case.
Numbers stored as text?
isActive = '1' and
prMedia.sortOrder = '0'. It's better to use
INT for integers and
BOOLEAN for true/false fields.
The modified script
I eliminated your subquery by working it into the
prMedia.filename AS thumbnail,
COUNT(prod.id) AS liked
FROM products AS prod
INNER JOIN productmedia AS prMedia
ON prod.userId = '$userId'
AND prod.id = prMedia.productId
WHERE prod.userId = '$viewingUserId'
AND prMedia.sortOrder = '0'
AND prod.isActive = '1'
ORDER BY prod.timestamp DESC