mysql query times out after certain periodof inactivity - query cache issue i think

using latest mysql server,

after some period of inactivity my website sqls times out (only some not all) these queries are not newly written existing queries.

Testing results

1. when execute a simple sql like (select count(*) from products) this works fine all the time.

2. when execute below sql

   SELECT products.pid
   FROM
       products INNER JOIN
       catalog ON products.cid=catalog.cid
   WHERE
       products.is_visible = 'Yes' AND (
           products.inventory_control = 'No' OR
           products.stock > 0 OR
           products.inventory_rule = 'OutOfStock' OR (
               products.inventory_control = 'AttrRuleInc' AND
               products.stock >= 0
           )
       ) AND products.is_home = 'Yes'
   GROUP BY products.pid
   

it times out ( Note it will not timeout all the time, this happens after 1 hour or 2 hour of inactivity)

1. The very first execution takes 30+ seconds and it times out and after that the above executing SQL 2 to 3 times the 4th time onwards it executes fast in 3 to 5 seconds

after 1 or 2 hour inactivity this pattern repeats.

I did not do any changes in settings.

i have 2 sites on this server.

1st server has 20,000 rows of in database ( this one works fine ) 2nd site has 150,000 rows in the databse ( this one is having this issue)

So this is something "query time out"

i do not think this is something on SQL settings, if it is then i should see this behavior on both sites

**** here is the table structure for which the issue with timeout / taking 40 seconds

#this structure has 2 additional keys we created 
#  KEY `product_id` (`product_id`),
#  KEY `product_no` (`product_no`)

# this one is having issues


--
-- Table structure for table `products` on lpbatt database server
--

DROP TABLE IF EXISTS `products`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `products` (
  `pid` int(10) unsigned NOT NULL auto_increment,
  `cid` int(10) unsigned NOT NULL default '0',
  `manufacturer_id` int(10) unsigned NOT NULL default '0',
  `is_visible` enum('Yes','开发者_运维知识库No') NOT NULL default 'Yes',
  `is_hotdeal` enum('Yes','No') NOT NULL default 'No',
  `is_home` enum('Yes','No') NOT NULL default 'No',
  `is_taxable` enum('Yes','No') NOT NULL default 'Yes',
  `is_dollar_days` enum('Yes','No') NOT NULL default 'No',
  `is_google_co` enum('Yes','No') NOT NULL default 'Yes',
  `is_doba` enum('Yes','No') NOT NULL default 'No',
  `is_locked` enum('Yes','No') NOT NULL default 'No',
  `inventory_control` enum('Yes','AttrRuleExc','AttrRuleInc','No') NOT NULL default 'No',
  `inventory_rule` enum('Hide','OutOfStock') NOT NULL default 'Hide',
  `stock` int(10) NOT NULL default '0',
  `stock_warning` int(10) NOT NULL default '0',
  `weight` decimal(10,2) unsigned NOT NULL default '0.00',
  `free_shipping` enum('Yes','No') NOT NULL default 'No',
  `digital_product` enum('Yes','No') NOT NULL default 'No',
  `digital_product_file` varchar(255) NOT NULL default '',
  `cost` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price2` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price_level_1` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price_level_2` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price_level_3` decimal(20,5) unsigned NOT NULL default '0.00000',
  `shipping_price` decimal(20,5) unsigned NOT NULL default '0.00000',
  `tax_class_id` int(10) unsigned NOT NULL default '0',
  `tax_rate` decimal(20,5) NOT NULL default '-1.00000',
  `call_for_price` enum('Yes','No') NOT NULL default 'No',
  `priority` int(11) NOT NULL default '0',
  `attributes_count` int(11) NOT NULL default '0',
  `min_order` int(10) NOT NULL default '1',
  `max_order` int(10) unsigned NOT NULL default '0',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_location_id` int(10) unsigned NOT NULL default '0',
 `url_hash` varchar(32) NOT NULL default '',
  `url_default` varchar(128) NOT NULL default '',
  `url_custom` varchar(128) NOT NULL default '',
  `product_id` int(64) NOT NULL default '0',
  `product_sku` varchar(64) NOT NULL default '',
  `product_upc` varchar(64) NOT NULL default '',
  `case_pack` int(11) NOT NULL default '-1',
  `inter_pack` int(11) NOT NULL default '-1',
  `gift_quantity` int(10) unsigned NOT NULL default '0',
  `dimension_width` decimal(10,2) NOT NULL default '0.00',
  `dimension_length` decimal(10,2) NOT NULL default '0.00',
  `dimension_height` decimal(10,2) NOT NULL default '0.00',
  `image_location` enum('Local','Web') NOT NULL default 'Local',
  `image_url` varchar(255) NOT NULL default '',
  `image_alt_text` varchar(255) NOT NULL default '',
  `tmp_manufacturer` varchar(30) default NULL,
  `tmp_family` varchar(30) default NULL,
  `tmp_series` varchar(30) default NULL,
  `tmp_model` varchar(30) default NULL,
  `tmp_ptype` varchar(30) default NULL,
  `product_no` varchar(40) default NULL,
  `part_no` varchar(300) default NULL,
  `spec_1` varchar(7) default NULL,
  `spec_2` varchar(7) default NULL,
  `spec_3` varchar(7) default NULL,
  `spec_4` varchar(40) default NULL,
  `title` varchar(255) NOT NULL default '',
  `meta_keywords` text NOT NULL,
  `meta_title` text NOT NULL,
  `meta_description` text NOT NULL,
  `overview` text,
  `description` text,
  `zoom_option` enum('global','none','zoom','magnify','magicthumb','imagelayover') NOT NULL default 'global',
  PRIMARY KEY  (`pid`),
  KEY `cid` (`cid`),
  KEY `is_visible` (`is_visible`),
  KEY `url_hash` (`url_hash`),
  KEY `product_id` (`product_id`),
  KEY `product_no` (`product_no`)
) ENGINE=MyISAM AUTO_INCREMENT=1630746530 DEFAULT CHARSET=utf8;
SET character_set_client = @saved_cs_client;

below is the table structure which is running on another server but working fine

#see the products table structure and catalog table structure 
#this is fine on this server 


# --------------------------------------------------------
# Host:                         laptopnbparts.com
# Database:                     laptopnbpartscom
# Server version:               5.0.77
# Server OS:                    redhat-linux-gnu
# HeidiSQL version:             5.0.0.3222
# Date/time:                    2010-06-25 18:13:33
# --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

# Dumping structure for table laptopnbpartscom.catalog
CREATE TABLE IF NOT EXISTS `catalog` (
  `cid` int(10) unsigned NOT NULL auto_increment,
  `parent` int(10) unsigned NOT NULL default '0',
  `level` int(10) unsigned NOT NULL default '0',
  `priority` smallint(5) unsigned NOT NULL default '5',
  `is_visible` enum('Yes','No') NOT NULL default 'Yes',
  `list_subcats` enum('Yes','No') NOT NULL default 'No',
  `url_hash` varchar(32) NOT NULL default '',
  `url_default` varchar(128) NOT NULL default '',
  `url_custom` varchar(128) NOT NULL default '',
  `key_name` varchar(255) NOT NULL default '',
  `meta_keywords` text,
  `meta_title` text,
  `meta_description` text,
  `category_header` varchar(255) NOT NULL default '',
  `name` varchar(255) NOT NULL default '',
  `description` text,
  `description_bottom` text,
  `category_path` text,
  PRIMARY KEY  (`cid`),
  KEY `parent` (`parent`),
  KEY `level` (`level`),
  KEY `priority` (`priority`),
  KEY `url_hash` (`url_hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Data exporting was unselected.


# Dumping structure for table laptopnbpartscom.products
CREATE TABLE IF NOT EXISTS `products` (
  `pid` int(10) unsigned NOT NULL auto_increment,
  `cid` int(10) unsigned NOT NULL default '0',
  `manufacturer_id` int(10) unsigned NOT NULL default '0',
  `is_visible` enum('Yes','No') NOT NULL default 'Yes',
  `is_hotdeal` enum('Yes','No') NOT NULL default 'No',
  `is_home` enum('Yes','No') NOT NULL default 'No',
  `is_taxable` enum('Yes','No') NOT NULL default 'Yes',
  `is_dollar_days` enum('Yes','No') NOT NULL default 'No',
  `is_google_co` enum('Yes','No') NOT NULL default 'Yes',
  `is_doba` enum('Yes','No') NOT NULL default 'No',
  `is_locked` enum('Yes','No') NOT NULL default 'No',
  `inventory_control` enum('Yes','AttrRuleExc','AttrRuleInc','No') NOT NULL default 'No',
  `inventory_rule` enum('Hide','OutOfStock') NOT NULL default 'Hide',
  `stock` int(10) NOT NULL default '0',
  `stock_warning` int(10) NOT NULL default '0',
  `weight` decimal(10,2) unsigned NOT NULL default '0.00',
  `free_shipping` enum('Yes','No') NOT NULL default 'No',
  `digital_product` enum('Yes','No') NOT NULL default 'No',
  `digital_product_file` varchar(255) NOT NULL default '',
  `cost` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price2` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price_level_1` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price_level_2` decimal(20,5) unsigned NOT NULL default '0.00000',
  `price_level_3` decimal(20,5) unsigned NOT NULL default '0.00000',
  `shipping_price` decimal(20,5) unsigned NOT NULL default '0.00000',
  `tax_class_id` int(10) unsigned NOT NULL default '0',
  `tax_rate` decimal(20,5) NOT NULL default '-1.00000',
  `call_for_price` enum('Yes','No') NOT NULL default 'No',
  `priority` int(11) NOT NULL default '0',
  `attributes_count` int(11) NOT NULL default '0',
  `min_order` int(10) NOT NULL default '1',
  `max_order` int(10) unsigned NOT NULL default '0',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_location_id` int(10) unsigned NOT NULL default '0',
  `url_hash` varchar(32) NOT NULL default '',
  `url_default` varchar(128) NOT NULL default '',
  `url_custom` varchar(128) NOT NULL default '',
  `product_id` varchar(64) NOT NULL default '',
  `product_sku` varchar(64) NOT NULL default '',
  `product_upc` varchar(64) NOT NULL default '',
  `case_pack` int(11) NOT NULL default '-1',
  `inter_pack` int(11) NOT NULL default '-1',
  `gift_quantity` int(10) unsigned NOT NULL default '0',
  `dimension_width` decimal(10,2) NOT NULL default '0.00',
  `dimension_length` decimal(10,2) NOT NULL default '0.00',
  `dimension_height` decimal(10,2) NOT NULL default '0.00',
  `image_location` enum('Local','Web') NOT NULL default 'Local',
  `image_url` varchar(255) NOT NULL default '',
  `image_alt_text` varchar(255) NOT NULL default '',
  `tmp_manufacturer` varchar(30) default NULL,
  `tmp_series` varchar(30) default NULL,
  `tmp_model` varchar(30) default NULL,
  `tmp_ptype` varchar(30) default NULL,
  `product_no` varchar(60) default NULL,
  `part_no` varchar(60) default NULL,
  `watt_volt_amp` varchar(100) default NULL,
  `title` varchar(255) NOT NULL default '',
  `meta_keywords` text NOT NULL,
  `meta_title` text NOT NULL,
  `meta_description` text NOT NULL,
  `overview` text,
  `description` text,
  `zoom_option` enum('global','none','zoom','magnify','magicthumb','imagelayover') NOT NULL default 'global',
  PRIMARY KEY  (`pid`),
  KEY `cid` (`cid`),
  KEY `is_visible` (`is_visible`),
  KEY `url_hash` (`url_hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Data exporting was unselected.
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Try using CASE WHEN instead of the multiple OR statements in the WHERE clause and evaluate on the client side. OR is a well-known performance killer as MySQL cannot usually apply INDEXes and must check every single statement in order to filter out rows.