Re[2]: [PHP-DB] Optimising LIMITs - alter table order by...

Hello Pavel, Tuesday, March 2, 2004, 12:33:17 PM, you wrote: PL> PL> http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#ALTER_TABLE PL> ORDER BY allows you to create the new table with the rows in a specific PL> order. Note that the table will not remain in this order after inserts PL> and deletes. In some cases, it might make sorting easier for MySQL if Figured as much, thanks for the reference. I hope one day MySQL will produce a manual that isn't one horrendous long document per section. PL> You need to split the table and move "post messages" to another table if PL> you want to get higher perfomance for (2). With regard to disk seeking, here is my table structure: CREATE TABLE thread ( threadid int(10) unsigned NOT NULL auto_increment, site_user_id varchar(32) NOT NULL default '', boardid tinyint(3) unsigned NOT NULL default '0', subject varchar(200) NOT NULL default '', modified timestamp(14) NOT NULL, created timestamp(14) NOT NULL, status enum('L','P','H','D') NOT NULL default 'L', PRIMARY KEY (threadid), FULLTEXT KEY subject (subject), KEY boardid (boardid), KEY site_user_id (site_user_id), KEY created (created), KEY status (status) ) TYPE=MyISAM; As well as moving the subject field to another table (because it's the only non-defined length field), would another way of speeding up the disk seek be to turn it from a varchar(200) into a char(200)? I know it means a larger table size, but MySQL should then be able to calculate exactly where to jump to in the file? PL> All MySQL functions should be very optimized, but there may be some PL> exceptions. BENCHMARK(loop_count,expression) is a great tool to find out PL> if this is a problem with your query. I will look at this now. I had been toying with the idea of creating a cache table that held the 200 most recent threads, pre-sequenced so I just bring back that instead of having to query the database unless they go beyond that 200 limit. Your comments (and MySQL manual posts) have been very useful, thank you. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html