#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171

    #1235-This version of MySQL doesn't yet support it. INDEX ORDER BY!!


    Hello;
    I want to get random average price of 300 products but guess what, I can't

    1 - First ORDER BY RAND does not use the index. EXPLAIN told me it searches 11 million records. That wouldn't be sane would it? So what is the right way of doing this?

    2 - I got busted again! I made attempt to be creative and run a fancy query but
    Code:
    #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
    Code:
    SELECT AVG(price)
            FROM products WHERE id IN
    	(SELECT id
    		FROM products_table
    		ORDER BY RAND( ) 
    		LIMIT 300
            )
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Try this:
    Code:
    select avg(price)
    from (
      select price
      from products_table
      order by rand() 
      limit 300
    ) t
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by shammat
    Try this
    won't that still bring up #1235 ?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by r937
    won't that still bring up #1235 ?
    No, because the limit/order by is inside a derived table, not a sub-select.

    See here: http://sqlfiddle.com/#!2/dd08d/2
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Hi
    Showing rows 0 - 0 (~11 total, Query took 15.6656 sec)
    Too long though!
    What would you do to get reasonable fast results? Cache query in application?
  10. #6
  11. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    I thought you might want to see what the EXPLAIN looks like. Still reading all the rows.
    Last edited by zxcvbnm; May 1st, 2013 at 06:59 PM.
  12. #7
  13. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    As far as I know, having "order by rand()" will always force MySQL to read all of the rows in the table. In order for an index to be used, you would have to be ordering by the index column.

    Can you explain the use-case for computing the average of 300 random records out of a table of 11 million records?

    Pulling a few random randoms out of a very large table is not an unusual programming problem and has various fast solutions, but pulling hundreds of random rows out of a very large table is an unusual problem.
    Last edited by E-Oreo; May 1st, 2013 at 09:34 PM.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  14. #8
  15. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,653
    Rep Power
    171
    Originally Posted by E-Oreo
    As far as I know, having "order by rand()" will always force MySQL to read all of the rows in the table. In order for an index to be used, you would have to be ordering by the index column.

    Can you explain the use-case for computing the average of 300 random records out of a table of 11 million records?

    Pulling a few random randoms out of a very large table is not an unusual programming problem and has various fast solutions, but pulling hundreds of random rows out of a very large table is an unusual problem.
    I will have to start working on a very large database. I am thinking of different scenarios they are gonna ask me to build for them. So if you have tips on "Pulling a few random randoms out of a very large table " please share
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Originally Posted by zxcvbnm
    So if you have tips on "Pulling a few random randoms out of a very large table " please share
    i'm sure you can find plenty of articles to describe various techniques

    one that i've used with a table that had an auto_increment is to generate random key values beforehand (i.e. random integers between 1 and n, where n is the max id value) -- no guarantee that all generated values are still going to exist (due to deletions) so you generate a few more than you need...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo