1

For my http://culturepics.org site I have a reasonably large table (>30,000 records and growing) which stores basic information about image files, most notably their width and height (px). When a user inputs their chosen dimensions I display images that match (or closely match) that aspect ratio. Currently this is the first '$n' results where $n is higher for smaller images, but I would like to return more results (e.g. 50 matches) and then display $n images randomly chosen from this results set.

My query is currently

"SELECT *, abs(((width/height)-$ratio)) as diff FROM `imagedata` ORDER BY diff ASC LIMIT 0,$n"

where $ratio is calculated from the user's input.

Given the size of my table I have read that anything using rand() in MYSQLI will be inefficient. Do I need to look at returning 50 results in my query and then selecting random rows using PHP, and if so can anyone suggest the most suitable method? I'm also a little concerned that calculating diff in the query may be inefficient but it seems to be working fine right now. However, I could always create a new indexed column and populate that when I store image data, if that sounds like a good idea. Thanks

jamesinealing
  • 570
  • 8
  • 20
  • 2
    possible duplicate of [MySQL: what's the most efficient way to select multiple random rows](http://stackoverflow.com/questions/10054836/mysql-whats-the-most-efficient-way-to-select-multiple-random-rows) – Marc B Feb 10 '14 at 21:43
  • I saw that one and wondered myself if it might be useful, but from what I can see from the accepted answer the only way it is limiting the original dataset is by finding the max value of 'id' and then creating an array of random values of 'id' to query with. That said, the second suggestion (from @peter-taylor) looks like it might be useful here, so I'll give that a go. Thanks for prompting me to revisit it. – jamesinealing Feb 10 '14 at 22:42

0 Answers0