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`),
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; 2 2043
In article <11*********************@z14g2000cwz.googlegroups. com>,
"Jasper Bryant-Greene" <ja******@gmail.com> wrote: 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`), 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;
what does the EXPLAIN <query> tell you? It should show the strategy
used by optimizer. You may have to do some rephrasing. Have you posted
this in a mysql group: http://groups-beta.google.com/group/alt.php.sql http://groups-beta.google.com/group/...database.mysql
--
DeeDee, don't press that button! DeeDee! NO! Dee...
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marcus |
last post by:
Quick question on how I setup my mysql database(s)...
If my setup is such that I have multiple clients, and each client gets
10 tables to store their data, is it better for performance if I put...
|
by: aum |
last post by:
Hi,
I'm looking for an object-relational layer, which can wrap a MySQL
database into highly pythonic objects.
There seem to be dozens of packages which provide some of this
functionality, but...
|
by: Lenz Grimmer |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
MySQL 4.0.14, a new version of the popular Open Source/Free Software
Database, has been released. It is now available in source and binary...
|
by: Brian |
last post by:
Hello
I am using version 4.0.12-nt of MySQL and when I hit the enter key
rapidly I can't connect to the database. The result is a message is
returned to me from mysql that says I can't connect...
|
by: Jasper Bryant-Greene |
last post by:
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...
|
by: Andreas Lauffer |
last post by:
I changed from Access97 to AccessXP and I have immense performance
problems.
Details:
- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC
I used...
|
by: Sai Hertz And Control Systems |
last post by:
Dear all,
Their was a huge rore about MySQL recently for something in java functions
now theirs one more
http://www.mysql.com/doc/en/News-5.0.x.html
Does this concern anyone.
What I...
|
by: Mairhtin O'Feannag |
last post by:
Hello,
I have a client (customer) who asked the question : "Why would I buy and
use UDB, when MySql is free?"
I had to say I was stunned. I have no experience with MySql, so I was
left sort...
|
by: Atli |
last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users.
Anyone should be able to get...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: erikbower65 |
last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps:
1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal.
2. Connect to...
|
by: linyimin |
last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |