[SOLVED] Very slow query

[frankyw]

Hello everyone,

 

If anyone could suggest how to create indexes for the following queries I would be very appreciative, the load on our server is through the roof right now due to a marketing e-mail.

 

Server version: 5.0.45-log

 

Raw SQL statement:

# Query_time: 6  Lock_time: 0  Rows_sent: 9  Rows_examined: 744192
select p.products_image, pd.products_name,  p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, if(s.status = '1' and s.stores_id = '3', s.specials_new_products_price, null) as specials_new_products_price,
if(s.status = '1' and s.stores_id = '3', s.specials_new_products_price, p.products_price) as final_price from products_description pd, products p
left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c
left join specials s on p2c.products_id = s.products_id and s.stores_id = '3' inner join products_to_stores p2s on p2c.products_id = p2s.products_id
where p2s.stores_id = '3' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and (p2c.categories_id = '140') order by p.products_sort_order , pd.products_name limit 45, 9;

 

Explain output:

 

+----+-------------+-------+--------+---------------------------+-------------------+---------+--------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys             | key               | key_len | ref                                  | rows | Extra                                        |
+----+-------------+-------+--------+---------------------------+-------------------+---------+--------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | p2c   | ref    | PRIMARY,idx_categories_id | idx_categories_id | 4       | const                                |  444 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | p     | eq_ref | PRIMARY                   | PRIMARY           | 4       | cre_dev.p2c.products_id     |    1 | Using where                                  | 
|  1 | SIMPLE      | m     | eq_ref | PRIMARY                   | PRIMARY           | 4       | cre_dev.p.manufacturers_id  |    1 | Using index                                  | 
|  1 | SIMPLE      | pd    | eq_ref | PRIMARY                   | PRIMARY           | 8       | cre_dev.p.products_id,const |    1 | Using where                                  | 
|  1 | SIMPLE      | s     | ALL    | NULL                      | NULL              | NULL    | NULL                                 | 1687 |                                              | 
|  1 | SIMPLE      | p2s   | eq_ref | PRIMARY                   | PRIMARY           | 8       | cre_dev.p.products_id,const |    1 | Using where; Using index                     | 
+----+-------------+-------+--------+---------------------------+-------------------+---------+--------------------------------------+------+----------------------------------------------+
6 rows in set (0.02 sec)

 

Show create tables:

 

       Table: products_description
Create Table: CREATE TABLE `products_description` (
  `products_id` int(11) NOT NULL auto_increment,
  `language_id` int(11) NOT NULL default '1',
  `products_name` varchar(64) collate latin1_general_ci NOT NULL default '',
  `products_description` text collate latin1_general_ci,
  `products_url` varchar(255) collate latin1_general_ci default NULL,
  `products_viewed` int(5) default '0',
  `products_head_title_tag` varchar(80) collate latin1_general_ci default NULL,
  `products_head_desc_tag` longtext collate latin1_general_ci NOT NULL,
  `products_head_keywords_tag` longtext collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`products_id`,`language_id`),
  KEY `products_name` (`products_name`)
) ENGINE=MyISAM AUTO_INCREMENT=2492 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

       Table: products
Create Table: CREATE TABLE `products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_quantity` int(15) NOT NULL default '0',
  `products_model` varchar(25) collate latin1_general_ci default NULL,
  `products_image` varchar(64) collate latin1_general_ci default NULL,
  `products_image_med` varchar(64) collate latin1_general_ci default NULL,
  `products_image_lrg` varchar(64) collate latin1_general_ci default NULL,
  `products_image_sm_1` varchar(64) collate latin1_general_ci default NULL,
  `products_image_xl_1` varchar(64) collate latin1_general_ci default NULL,
  `products_image_sm_2` varchar(64) collate latin1_general_ci default NULL,
  `products_image_xl_2` varchar(64) collate latin1_general_ci default NULL,
  `products_image_sm_3` varchar(64) collate latin1_general_ci default NULL,
  `products_image_xl_3` varchar(64) collate latin1_general_ci default NULL,
  `products_image_sm_4` varchar(64) collate latin1_general_ci default NULL,
  `products_image_xl_4` varchar(64) collate latin1_general_ci default NULL,
  `products_image_sm_5` varchar(64) collate latin1_general_ci default NULL,
  `products_image_xl_5` varchar(64) collate latin1_general_ci default NULL,
  `products_image_sm_6` varchar(64) collate latin1_general_ci default NULL,
  `products_image_xl_6` varchar(64) collate latin1_general_ci default NULL,
  `products_price` decimal(15,4) NOT NULL default '0.0000',
  `products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
  `products_last_modified` datetime default '0000-00-00 00:00:00',
  `products_date_available` datetime default '0000-00-00 00:00:00',
  `products_weight` decimal(5,2) NOT NULL default '0.00',
  `products_status` tinyint(1) NOT NULL default '0',
  `products_tax_class_id` int(11) NOT NULL default '0',
  `manufacturers_id` int(11) default NULL,
  `distributors_id` int(11) NOT NULL default '0',
  `products_ordered` int(11) NOT NULL default '0',
  `products_parent_id` int(11) NOT NULL default '0',
  `products_price1` decimal(15,4) NOT NULL default '0.0000',
  `products_price2` decimal(15,4) NOT NULL default '0.0000',
  `products_price3` decimal(15,4) NOT NULL default '0.0000',
  `products_price4` decimal(15,4) NOT NULL default '0.0000',
  `products_price5` decimal(15,4) NOT NULL default '0.0000',
  `products_price6` decimal(15,4) NOT NULL default '0.0000',
  `products_price7` decimal(15,4) NOT NULL default '0.0000',
  `products_price8` decimal(15,4) NOT NULL default '0.0000',
  `products_price9` decimal(15,4) NOT NULL default '0.0000',
  `products_price10` decimal(15,4) NOT NULL default '0.0000',
  `products_price11` decimal(15,4) NOT NULL default '0.0000',
  `products_price1_qty` int(11) NOT NULL default '0',
  `products_price2_qty` int(11) NOT NULL default '0',
  `products_price3_qty` int(11) NOT NULL default '0',
  `products_price4_qty` int(11) NOT NULL default '0',
  `products_price5_qty` int(11) NOT NULL default '0',
  `products_price6_qty` int(11) NOT NULL default '0',
  `products_price7_qty` int(11) NOT NULL default '0',
  `products_price8_qty` int(11) NOT NULL default '0',
  `products_price9_qty` int(11) NOT NULL default '0',
  `products_price10_qty` int(11) NOT NULL default '0',
  `products_price11_qty` int(11) NOT NULL default '0',
  `products_qty_blocks` int(11) NOT NULL default '1',
  `products_cog1` varchar(255) collate latin1_general_ci default NULL,
  `products_cog2` varchar(255) collate latin1_general_ci default NULL,
  `products_sort_order` int(4) NOT NULL default '0',
  `in_store_only` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`)
) ENGINE=MyISAM AUTO_INCREMENT=2492 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

       Table: manufacturers
Create Table: CREATE TABLE `manufacturers` (
  `manufacturers_id` int(11) NOT NULL auto_increment,
  `manufacturers_name` varchar(32) collate latin1_general_ci NOT NULL default '',
  `manufacturers_image` varchar(64) collate latin1_general_ci default NULL,
  `date_added` datetime default '0000-00-00 00:00:00',
  `last_modified` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`manufacturers_id`),
  KEY `IDX_MANUFACTURERS_NAME` (`manufacturers_name`)
) ENGINE=MyISAM AUTO_INCREMENT=202 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

       Table: products_to_categories
Create Table: CREATE TABLE `products_to_categories` (
  `products_id` int(11) NOT NULL default '0',
  `categories_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`products_id`,`categories_id`),
  KEY `idx_categories_id` (`categories_id`,`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

       Table: specials
Create Table: CREATE TABLE `specials` (
  `specials_id` int(11) NOT NULL auto_increment,
  `products_id` int(11) NOT NULL default '0',
  `specials_new_products_price` decimal(15,4) NOT NULL default '0.0000',
  `specials_date_added` datetime default '0000-00-00 00:00:00',
  `specials_last_modified` datetime default '0000-00-00 00:00:00',
  `expires_date` datetime default '0000-00-00 00:00:00',
  `date_status_change` datetime default '0000-00-00 00:00:00',
  `status` int(1) NOT NULL default '1',
  `stores_id` int(11) NOT NULL default '0',
  `customers_group_id` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`specials_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2204 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

       Table: products_to_stores
Create Table: CREATE TABLE `products_to_stores` (
  `products_id` int(11) NOT NULL default '0',
  `stores_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`products_id`,`stores_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

 

Yes, it's true, we are scanning 700K rows and I don't know how to add the indexes to fix this

 

Thanks a lot for your help.

[hitman6003]

Create an index on specials.product_id.  That should eliminate the full table scan on that table, which should reduce the number of rows examined significantly.

[frankyw]

Wow hitman that did the trick, load dropped from over 4 to under 0.5 and pages load much faster.  Thanks for the help mate.