I have a non-profit site that uses mysql. I need to optimize two queries so
they won't put such a load on the mysql server. If I don't lighten the
load, they've treatened to kick us off.
What columns are best to index?
SELECT messages.ID as ID, title, text, user_id,
username, email
FROM messages,user
WHERE messages.user_id=user.ID
AND (parent_id=$ARGV[1] OR messages.ID=$ARGV[1])
ORDER BY messages.time"
SELECT * FROM pc, links
WHERE pc.ID = links.pc_id
AND links.user_id = $id
AND links.board_id = $ARGV[0]
DROP TABLE IF EXISTS links;
CREATE TABLE links (
board_id int(11) DEFAULT '0' NOT NULL,
user_id int(11) DEFAULT '0' NOT NULL,
pc_id int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (board_id,user_id,pc_id),
KEY iboard (board_id),
KEY iuser (user_id),
KEY ipc (pc_id)
);
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
board_id int(11) DEFAULT '0' NOT NULL,
user_id int(11) DEFAULT '0' NOT NULL,
parent_id int(11) DEFAULT '0' NOT NULL,
title varchar(30),
text text,
time int(11),
ID int(11) DEFAULT '0' NOT NULL auto_increment,
PRIMARY KEY (ID),
KEY iboard (board_id),
KEY iuser (user_id),
KEY iparent (parent_id)
);
DROP TABLE IF EXISTS pc;
CREATE TABLE pc (
name varchar(15),
picture varchar(15),
text text,
time int(11),
ID int(11) DEFAULT '0' NOT NULL auto_increment,
PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS user;
CREATE TABLE user (
username varchar(15) DEFAULT '' NOT NULL,
password varchar(15) DEFAULT '' NOT NULL,
email varchar(30),
junk tinyint(4),
time int(11),
ID int(11) DEFAULT '0' NOT NULL auto_increment,
PRIMARY KEY (ID),
KEY iusername (username),
KEY ipassword (password)
);
---------------------------------------------------------------------
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
