First let me explain what I am trying to do. I want to archive many many messages into a mysql database. The format is pretty simple... either the message is a parent, or it is a reply. The problem is that some of the queries are taking 5-10 seconds, which is not acceptable for my application. The size of the table is always going to be 250k+ rows (about 2k of text per row). Below is the query that I need to make faster, and the table layout. Does anyone have any ideas on how I could make this query preform better? Query (with 250k rows, this is taking between 5 and 20 seconds): SELECT m.messageid,catid,m.userid,subject,postdate FROM messages AS m LEFT JOIN users AS u ON (m.userid = u.userid) WHERE m.catid = 1 AND m.parentmsgid = 0 ORDER BY postdate LIMIT 50; This query selects all the messages within category specified by the 'catid' field. The 'parentmsgid' field is 0 if it is a parent, and if it is a reply it contains the messageid of its parent. Here is the layout of the tables. I made sure that the two fields in the 'message' table that I will be using in queries is a KEY in that table. However I suspect that mysql is using the index to find the location of all the messages with catid, and parentmsgid = 0, but is using a lot of time sorting these records (each category can have 20-30k messages). Would adding a key (catid, parentmsgid, postdate) help? Does adding a key for what you sort on help? CREATE TABLE category ( catid int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL default '', PRIMARY KEY (groupid) ); CREATE TABLE messages ( messageid int(10) unsigned NOT NULL auto_increment, catid int(10) unsigned NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', parentmsgid int(10) unsigned NOT NULL default '0', subject text NOT NULL, body text NOT NULL, postdate int(10) unsigned NOT NULL default '0', replies int(10) unsigned NOT NULL default '0', PRIMARY KEY (messageid), KEY groupid(groupid), KEY parentmsgid(parentmsgid) ) TYPE=MyISAM; CREATE TABLE users ( userid int(10) unsigned NOT NULL auto_increment, username varchar(30) NOT NULL default '', password varchar(30) NOT NULL default '', email varchar(255) NOT NULL default '', PRIMARY KEY (userid), KEY username(username,password), KEY email(email) ) TYPE=MyISAM; I am open to any type of suggest, even if it means restructuring my layout. How can I make this query faster? Thanks, ryan --------------------------------------------------------------------- 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
