Hello Pavel,

Tuesday, March 2, 2004, 12:33:17 PM, you wrote:

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`)

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

Your comments (and MySQL manual posts) have been very useful, thank

Best regards,
 Richard Davey

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to