Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted June 10, 200718 yr 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.
June 10, 200718 yr 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:
June 11, 200718 yr 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 (
June 11, 200718 yr 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.
October 10, 200717 yr _posts pid _posts topic_id _posts author_id _posts post_date _posts ip_address _posts post_keyThis is what I see on a fresh install. (Removed my SQL DB prefix for security purposes.) Maybe it's an upgrade issue?
October 10, 200717 yr From what I've seen, upgrades tend to remove a lot of important indexes, which can be a huge pain as you can imagine.
October 10, 200717 yr 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.
Archived
This topic is now archived and is closed to further replies.