Re: Optimising MySQL queries against huge databases?

From: Henk Verhoeven (news_at_phppeanutsREMOVE-THIS.org)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 17:59:14 +0100

Hi Jasper,

I agree with Michael about the explain, but i did not try that, so the
following are just guesses:
- there is no index on movies.image so mySQL must sequentially search
throuhg all movies that have a director (probably all or close to all 141000
- there are probably MANY movies that have an image and a director.
Unless MySQL has a special optimization for ORDER BY RAND() LIMIT ..
it may have to random-sort all of them. That will take quite some time,
i guess. So if an index on movies.image does not help, you may have to
add AND id = RAND() to the SELECT condition, with the RAND function
parameterized or multiplied and rounded or floored to get whole numbers
between the lowest and highest id in the database. And then repeat the
query until it does return a row. (if many rows have been removed you
may have to pack the id's of movies)

Success,

Henk Verhoeven,
www.phpPeanuts.org.

Relevant Pages

  • JDBC/mysql error...
    ... when i tried to connect mysql with java. ... create database movies; ... System.out.println("Cannot register the driver"); ...
    (comp.lang.java.databases)
  • Re: rand()
    ... dass zwei Abfragen mit ORDER BY RAND() das selbe ... dass der Zufallsgenerator von MySQL ... FROM tabelle ORDER BY RAND). ...
    (de.comp.datenbanken.mysql)
  • ORDER BY RAND()....
    ... I have been working on a simple PHP script that called to a mysql ... SELECT url FROM sponsors ORDER BY RAND(); ...
    (php.general)
  • Re: [PHP] how to display photos of the day?
    ... MySQL has a rand() function, so you could bomb that off as a select once ... All Email originating from UWC is covered by disclaimer ...
    (php.general)
  • Re: [PHP] ORDER BY RAND()....
    ... > I have been working on a simple PHP script that called to a mysql ... SELECT url FROM sponsors ORDER BY RAND() LIMIT 1; ...
    (php.general)