How can I optimize this query?

Discussion in 'Databases' started by Joseph S, Feb 10, 2009.

0
  1. #1
    I have this query

    SELECT DISTINCT `test` FROM `testdb` order by rand() limit 10
    Code (markup):
    The problem is that it is using A LOT of resources.
     
    Joseph S, Feb 10, 2009 IP
  2. zealus

    zealus Active Member

    Messages:
    70
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    93
    #2
    SELECT COUNT(*) AS cnt FROM `testdb` OR SELECT MAX(id) AS cnt FROM `testdb`
    generate random number between 0 and cnt-1 in code/SQL then
    SELECT `test` FROM `testdb` LIMIT $generated_number, 1
     
    zealus, Feb 10, 2009 IP
    Halobitt likes this.
  3. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Depending on your requirements you could pregenerate a column with random numbers then you could query like this :
    SELECT DISTINCT ' test' FROM ' testdb' ORDER BY columnrandom LIMIT 10.
    If needed you can use windows scheduled tasks or Unix cronjobs to create a new random order at certain intervals.
    If this is usable for you depends on your requirements.
     
    chisara, Feb 12, 2009 IP