What could be causing strange query timeouts between PHP and MySQL?

I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On one particular instance of the software, the PHP code to query the list of category names is timing out when the customer has more than 29 categories. I know this makes no sense; there is nothing special about the number 30 that would break this and other customers have a lot more than 30 categories, however, the problem is 100% reproducible when this one installation has 30 or more categories and goes away when there's less than 30 categories.

The table in question is:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  `title` varchar(128) NOT NULL,
  `parent` int(10) unsigned NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` enum('Active','Inactive','_Deleted','_New') NOT NULL default 'Active',
  `style` enum('_Unknown') default NULL COMMENT 'Autoenum;',
  `order` smallint(5) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  `modified_at` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `parent` (`parent`),
  KEY `created_at` (`created_at`),
  KEY `modified_at` (`modified_at`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='R2' AUTO_INCREMENT=33 ;

The code in question recursively queries the table to fetch all the categories. It issues a

SELECT * FROM `categories` WHERE `parent`=0 ORDER BY `order`,`name`

And then repeats this query for each row returned, but using WHERE parent=$category_id each time. (I am sure this procedure could be improved, but that's probably another question)

As far as I can tell, the following query is hanging forever:

SELECT * FROM `categories` WHERE `parent`=22 ORDER BY `order`,`name`

I can execute this query in the mysql client on the server

For general profiling of what exactly is going on in the query plan, you can try PROFILING

It will basically help you determine where the hangup is.

Of course, it only works if you've compiled MySQL with enable-profiling.

StackExchange Database Administrators Q#7218, answer score: 5