Announcement

Announcement Module
Collapse
No announcement yet.

RAND() Efficiency on well indexed columns

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • RAND() Efficiency on well indexed columns

    I plan on having a large table, large as in lots of rows with contain a small amount of data.

    The table holds integers acting as pointers.

    Let's say the table has 10 Mil rows to start with.

    The following query would be efficient:

    SELECT id FROM pointers WHERE user_id=1 LIMIT 5

    Out of the 10Mil rows, this user "1" has 20 stored pointers, so the PRIMARY index should be efficient at extracting the resultset.

    To make sure each pointer id gets a fair amount of audience I would like to randomise the results.

    How efficient is "ORDER BY RAND()" on such a query?

    My guess is quite efficient, MySQL would maybe create a temp table fast with the 20 results, then randomise them?

    The table gets lots of simultaneous read/writes and is innodb type engine.

  • #2
    Right.

    If you have 20 rows matching WHERE clause only these rows would be sorted so it would not be that bad.

    Comment


    • #3
      Hi,

      here you can find good solutions to MySQL problems.
      http://www.fixya.com/support/t883221-mysql_efficiency

      Ciao,
      Shila.

      Comment

      Working...
      X