Can Anyone Help With This Slow Query? 1

[Tama]

Hi - I'm running a website on MySQL (PHPNuke/ phpBB/ Coppermine etc. etc.) - one of the queries generated by a modification of phpBB is taking a really long time to execute. I've recorded it taking up to 12 seconds to run - which is slowing down the website no end.

Here is an example of the query:

'SELECT a.post_id, d.* FROM nuke_bbattachments a, nuke_bbattachments_desc d WHERE ( a.post_id IN (134922, 134926, 134930, 134935, 134952)) AND (a.attach_id = d.attach_id) ORDER BY d.filetime ASC'

And here is a structure dump from the two tables involved:

CREATE TABLE `nuke_bbattachments` (
  `attach_id` mediumint(8) unsigned NOT NULL default '0',
  `post_id` mediumint(8) unsigned NOT NULL default '0',
  `privmsgs_id` mediumint(8) unsigned NOT NULL default '0',
  `user_id_1` mediumint(8) NOT NULL default '0',
  `user_id_2` mediumint(8) NOT NULL default '0',
  KEY `attach_id_post_id` (`attach_id`,`post_id`),
  KEY `attach_id_privmsgs_id` (`attach_id`,`privmsgs_id`)
) TYPE=MyISAM;

CREATE TABLE `nuke_bbattachments_desc` (
  `attach_id` mediumint(8) unsigned NOT NULL auto_increment,
  `physical_filename` varchar(255) NOT NULL default '',
  `real_filename` varchar(255) NOT NULL default '',
  `download_count` mediumint(8) unsigned NOT NULL default '0',
  `comment` varchar(255) default NULL,
  `extension` varchar(100) default NULL,
  `mimetype` varchar(100) default NULL,
  `filesize` int(20) NOT NULL default '0',
  `filetime` int(11) NOT NULL default '0',
  `thumbnail` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`attach_id`),
  KEY `filetime` (`filetime`),
  KEY `physical_filename` (`physical_filename`(10)),
  KEY `filesize` (`filesize`),
  KEY `attach_id` (`attach_id`)
) TYPE=MyISAM;

Does anyone have any suggestions on either a better syntax for the SQL query - or changes to the two tables (adding indexes maybe?)

Any suggestions or comments would be greatly appreciated as this query is crippling my website.

Cheers
Tama

 

[vanekl]

Have you run 'EXPLAIN' on this SELECT query?
You may want to post the results of the EXPLAIN if it isn't clear.

You are creating two indexes on attach_id in the nuke_*_desc
table. Should delete the second index (KEY `attach_id`).

Have you tuned the server parameters? Can you change any of them?
What are the following variables set to?
sort_buffer_size
read_buffer_size
key_buffer_size
And how much RAM do you have?

I'm guessing MySQL is ignoring the 'attach_id_post_id' index
and is doing a full table scan instead. If that is the case
(the EXPLAIN will say for sure), then you will have to create
a new index just on nuke_bbattachments.post_id.

You may want to try the SELECT query w/o the ORDER BY clause
and see how much of a speed-up you get. Many times it is
faster to do the sort outside of the database.

 

[Tama]

You Rock Vanekl!

I think I understand what you're saying (and had never used EXPLAIN before.) From the looks of things it is searching the whole nuke_*_desc table.

I made the changes you suggested:

* Deleted the second attach_id Index from nuke_*_desc
* Deleted the combined indexes on nuke_attachment
* Created indexes on attach_id, post_id and prvmsgs_id on nuke_attachment

Did an EXPLAIN on the query now and it's screaming along searching 14 rows rather than 6000+

And just in case - here's the MySQL variables you requested from my DB:

join buffer size  131072  
key buffer size  16773120  
myisam sort buffer size  8388608

My server has 512meg RAM
Is that looking good?

Thank you again - your excellent advice is greatly appreciated.
Tama

 

[vanekl]

Glad to hear you worked it out.
Your key_buffer_size is too small for a machine that has 512MB.
Should be at least 100M instead of 16M.
This should be set in your my.ini/my.cnf file and the server restarted.

set-variable = key_buffer_size=100M

This setting will allow MySQL to store more indexes in RAM.
It becomes really important the larger your database grows.