Jim Bayers wrote:
>
> 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.
Check the manual chaper about optimizations, especially about EXPLAIN
command. This will help you on finding right indexes now and in future.
Result vary on content (size) if tables, so it is not so easy to tell it
from now.
>
> 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
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Tonu Samuel <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia
<___/
---------------------------------------------------------------------
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
