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

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

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)

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 */;

ansaurus

tags:

views:

answers:

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

related questions