Well, I run an online comic strip at http://www.monkeydyne.com/rmcs/
I've done so for three years now, and this is the first time I've been
stumped enough to ask the mysql list for help.
On the site, users can submit their own comics and they can vote on
other people's comics. When a particular user logs in, I want them to
be able to see which
comics they voted for. Currently, the query looks like this:
SELECT comics.tagline,votes.score FROM comics,users LEFT OUTER JOIN
votes ON comics._rowid=votes.comic AND votes.voter=30 WHERE
users._rowid=comics.author;
A query for an active user is quick: 1/4 second. The problem here is
that if voter 30 hasn't voted on many comics, the query takes a LONG
time: 50 seconds. Something is seriously slowing down the query, but I
can't tell what it is.
======================================================
Mysql 3.22.32
CREATE TABLE comics (
char1 varchar(16),
char2 varchar(16),
tagline varchar(70),
d1a text,
d1b text,
d2a text,
d2b text,
d3a text,
d3b text,
score float(10,2) DEFAULT '0.00' NOT NULL,
_rowid int(11) DEFAULT '0' NOT NULL auto_increment,
author int(11) DEFAULT '0' NOT NULL,
deviation float(10,2),
numvoters int(11),
createdate datetime,
KEY score_idx (score),
PRIMARY KEY (_rowid)
);
CREATE TABLE users (
name varchar(50) DEFAULT '' NOT NULL,
address varchar(255) DEFAULT '' NOT NULL,
_rowid int(11) DEFAULT '0' NOT NULL auto_increment,
password text,
score float(10,2) DEFAULT '0.00' NOT NULL,
lastlogin datetime,
PRIMARY KEY (_rowid),
KEY score_key (score)
);
CREATE TABLE votes (
voter int(11) DEFAULT '0' NOT NULL,
comic int(11) DEFAULT '0' NOT NULL,
score float(10,2) DEFAULT '0.00' NOT NULL,
comment varchar(80),
KEY voter_key (voter),
KEY comic_key (comic),
KEY vc_idx (voter,comic),
PRIMARY KEY (voter,comic)
);
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
