MySQL: Proper sorting of a column

Databases
21

i say again, the subquery ORDER BY and main query GROUP BY are useless – they may appear to work, but they are simply masking an underlying inefficiency

22

Okay,

So basically there is a better way of writing for the same output the Order By and Group By are giving me?

I’ve tried a few different queries without the group by and am at a loss. This was the only one that outputted exactly what I wanted.

I’m thinking about just running a query grabbing top three films. And then, while looping that in PHP, just running individual queries to grab the top video for each film. so, in essence four queries. More efficient?

Cheers
Ryan

23

Way more efficient.

I just ran the query with the films, and then looped 3 separate queries in.

To run the query, the loop, and the other three queries it took 1 millisecond.

The single query I had posted above took 29 milliseconds. A bit more coding now, but works great.

I even added a function to check trailer existence in the looped queries, so I can load a backup video (like a clip) if one does not exist. Still only 1 millisecond to complete the job.

Cheers
Ryan