Oscommerce Question posted in
The official documentation can be found here

MySQL explain shows that main table's indexes are not used at production server – Oscommerce

I am trying to optimize the following modified MySQL query from OsCommerce:

select distinct p.products_id, pd.products_name, m.manufacturers_name, s.specials_new_products_price from products p 
inner join products_description pd on p.products_id = pd.products_id
inner join products_to_categories p2c on p.products_id = p2c.products_id 
left join manufacturers m on p.manufacturers_id = m.manufacturers_id 
left join specials s on p.products_id = s.products_id and s.specials_b2bgroup =0 
where p.products_status = '1' and p.products_model not like '%_VIP' and pd.language_id = '4' and p2c.categories_id = '1574' 
order by p.products_ordernum, p.products_model

Running explain on production server it seems that no indexes are used for table products at join:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  p   ALL     PRIMARY     NULL        NULL NULL   6729    Using where; Using temporary; Using filesort
1   SIMPLE  m   eq_ref  PRIMARY     PRIMARY     4   p.manufacturers_id  1    
1   SIMPLE  s   ref     products_id products_id 4   p.products_id   2    
1   SIMPLE  pd  eq_ref  PRIMARY     PRIMARY     8   p.products_id,const     1    
1   SIMPLE  p2c eq_ref  PRIMARY     PRIMARY     8   pd.products_id,const    1   Using where; Using index; Distinct

The schema for table products is the following:

CREATE TABLE IF NOT EXISTS `products` (
  `products_id` int(11) NOT NULL auto_increment,
  `products_model` varchar(50) default NULL,
  `products_image` varchar(250) 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 NULL,
  `products_date_available` datetime default NULL,
  `products_weight` decimal(5,2) NOT NULL default '0.00',
  `products_status` tinyint(1) NOT NULL default '0',
  `products_showprod` tinyint(1) NOT NULL default '0',
  `products_showprice` tinyint(1) NOT NULL default '0',
  `products_ordernum` int(6) NOT NULL default '100',
  `products_tax_class_id` int(11) NOT NULL default '0',
  `manufacturers_id` int(11) default NULL,
  PRIMARY KEY  (`products_id`),
  KEY `idx_products_model` (`products_model`),
) ENGINE=MyISAM  DEFAULT CHARSET=greek AUTO_INCREMENT=1;

My server’s MySQL version is 5.0.92. Any thoughts on where to look for a solution are realy welcome!

Tags:

2

Answers


  1. 0

    products is the leading (outermost) table in the nested loops, so the indexes used to access this table have nothing to do with the joins.

    This condition:

    p.products_model not like '%_VIP'

    is not sargable.

    You could try creating an index on products (status), if it’s selective enough (i. e. there are few values with status = 1)

    Login or Signup to reply.
  2. 0

    There are only two constraints in that query on the products table, which you have declared as the “master” table (because everything else is JOIN ON): products_status (not indexed) and products_model. But NOT LIKE '%...' is not an indexable constraint, so it is faster to do a simple scan.

    The index would be useful if the % came in the middle or end of the LIKE pattern. Even so, the NOT might still make the linear scan faster.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search