index's

[marcus]

Not sure what post_key is but can you put an index on it safely? just one of your sql statements is a select * from xyz where post_key = 'the post key' and this is obviously an expensive sql statement.

[Millar]

Not sure what post_key is but can you put an index on it safely? just one of your sql statements is a select * from xyz where post_key = 'the post key' and this is obviously an expensive sql statement.

You can put a normal (not unique) key on most columns and be safe. :ermm:

[bfarber]

There should already be an index on that column. Did you check the index checker in your acp? Do you have the up to date mysql_tables.php file under the install folder?

  pid int(10) NOT NULL auto_increment,  append_edit tinyint(1) default '0',  edit_time int(10) default NULL,  author_id mediumint(8) NOT NULL default '0',  author_name varchar(32) default NULL,  use_sig tinyint(1) NOT NULL default '0',  use_emo tinyint(1) NOT NULL default '0',  ip_address varchar(16) NOT NULL default '',  post_date int(10) default NULL,  icon_id smallint(3) default NULL,  post mediumtext,  queued tinyint(1) NOT NULL default '0',  topic_id int(10) NOT NULL default '0',  post_title varchar(255) default NULL,  new_topic tinyint(1) default '0',  edit_name varchar(255) default NULL,  post_key varchar(32) NOT NULL default '0',  post_parent int(10) NOT NULL default '0',  post_htmlstate smallint(1) NOT NULL default '0',  post_edit_reason varchar(255) NOT NULL default '',  PRIMARY KEY  (pid),  KEY topic_id (topic_id,queued,pid,post_date),  KEY author_id (author_id,topic_id),  KEY post_date (post_date),  KEY ip_address (ip_address),  KEY post_key (post_key),  FULLTEXT KEY post (post)) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE ibf_posts (

[marcus]

There should already be an index on that column. Did you check the index checker in your acp? Do you have the up to date mysql_tables.php file under the install folder?

CREATE TABLE ibf_posts (
  pid int(10) NOT NULL auto_increment,
  append_edit tinyint(1) default '0',
  edit_time int(10) default NULL,
  author_id mediumint(8) NOT NULL default '0',
  author_name varchar(32) default NULL,
  use_sig tinyint(1) NOT NULL default '0',
  use_emo tinyint(1) NOT NULL default '0',
  ip_address varchar(16) NOT NULL default '',
  post_date int(10) default NULL,
  icon_id smallint(3) default NULL,
  post mediumtext,
  queued tinyint(1) NOT NULL default '0',
  topic_id int(10) NOT NULL default '0',
  post_title varchar(255) default NULL,
  new_topic tinyint(1) default '0',
  edit_name varchar(255) default NULL,
  post_key varchar(32) NOT NULL default '0',
  post_parent int(10) NOT NULL default '0',
  post_htmlstate smallint(1) NOT NULL default '0',
  post_edit_reason varchar(255) NOT NULL default '',
  PRIMARY KEY  (pid),
  KEY topic_id (topic_id,queued,pid,post_date),
  KEY author_id (author_id,topic_id),
  KEY post_date (post_date),
  KEY ip_address (ip_address),
  KEY post_key (post_key),
  FULLTEXT KEY post (post)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

It's proberbly been missed from an upgrade, i'll do an index check now. It was proberbly only that one that got missed as i never noticed any others taking up a lot of time. Cheers for that :) *goes in corner with dunce cap.

[Ueland]

FYI, i just upgraded a production board, there is no index on "post_key"...

[FrostedPopTart]

_posts pid

_posts topic_id

_posts author_id

_posts post_date

_posts ip_address

_posts post_key

This is what I see on a fresh install. (Removed my SQL DB prefix for security purposes.) Maybe it's an upgrade issue?

[atomicknight]

From what I've seen, upgrades tend to remove a lot of important indexes, which can be a huge pain as you can imagine.

[FrostedPopTart]

If they do, then maybe it could be a bug and should be fixed. As I said, my board is a fresh install so I am not 100% sure; but I will monitor.