Optimising MySQL queries against huge databases?

[Jasper Bryant-Greene]

I have a database of movie titles, with about 78,000 records, and a
database of related people (directors, writers, actors/actresses etc.)
with about 141,000 records. I display a random movie out of this
database on each hit to my website's homepage.

This worked fine when I had only a couple thousand movies, but now that
the DB has grown, it seems to be taking a bit longer to process the
page.

My DB schema for each table:

CREATE TABLE `movies` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(200) NOT NULL default '',
`uri` varchar(100) NOT NULL default '',
`year` year(4) NOT NULL default '0000',
`released` date NOT NULL default '0000-00-00',
`imdb` int(11) NOT NULL default '0',
`allmovies` varchar(25) NOT NULL default '',
`length` int(11) NOT NULL default '0',
`colour` enum('c','b') NOT NULL default 'c',
`sound` varchar(25) NOT NULL default '',
`director` int(11) NOT NULL default '0',
`writer` int(11) NOT NULL default '0',
`asin` varchar(25) NOT NULL default '',
`image` blob NOT NULL,
`genre` varchar(25) NOT NULL default '',
`fatso` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `imdb` (`imdb`),
KEY `name` (`name`),
KEY `director` (`director`),
FULLTEXT KEY `name_2` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=78483 ;

CREATE TABLE `people` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`born` date NOT NULL default '0000-00-00',
`died` date NOT NULL default '0000-00-00',
`imdb` int(11) NOT NULL default '0',
`allmusic` varchar(25) NOT NULL default '',
`allmovies` varchar(25) NOT NULL default '',
`uri` varchar(100) NOT NULL default '',
`image` blob NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `imdb` (`imdb`),
KEY `name` (`name`),
FULLTEXT KEY `name_2` (`name`)
) TYPE=MyISAM AUTO_INCREMENT=141623 ;

The SQL query I am using to fetch a random movie is:

SELECT movies.id, movies.name, movies.asin, movies.fatso, people.id AS
directorid, people.name AS director
FROM movies, people
WHERE movies.image<>'' AND people.id=movies.director
ORDER BY RAND() LIMIT 1;

[Bill Karwin]

Jasper Bryant-Greene wrote:

SELECT movies.id, movies.name, movies.asin, movies.fatso, people.id AS
directorid, people.name AS director
FROM movies, people
WHERE movies.image<>'' AND people.id=movies.director
ORDER BY RAND() LIMIT 1;

I notice this query actually chooses a random movie/director
combination, which is a bit different than choosing a random movie.
A given movie might have multiple directors, or have no director listed.
The above query doesn't return any movies that have no director.

If you only want any random movie, even if it has no director, then try
this:

SELECT movies.id, movies.name, movies.asin, movies.fatso, people.id AS
directorid, people.name AS director
FROM movies LEFT OUTER JOIN people ON people.id=movies.director
WHERE movies.image<>''
AND movies.id = (SELECT MAX(m2.id) * RAND() FROM movies m2)
LIMIT 1;

This uses a subquery, so you need to upgrade to MySQL 4.1 for this to
work. If you can't upgrade, then run the inner query separately, and
then feed the value as a constant parameter to the outer query.

Regards,
Bill K.