[Solved] Small database size but very slow performance. What can I do?

EverSQL Database Performance Knowledge Base

Small database size but very slow performance. What can I do?

Database type:

We have this opencart installation with a database size of roughly 120 MB, 90MB of which is MyISAM and other is InnoDB.

Most of the queries like :

SELECT  DISTINCT *, a.*, vd.seo, p.image, p2s.price, p.product_id,
        p.image, pd.name, 
      ( SELECT  price
            FROM  product_special ps
            WHERE  ps.product_id = p.product_id
              AND  ps.customer_group_id = '1'
              AND  ((ps.date_start = '0000-00-00'
                              OR  ps.date_start < NOW())
                      AND  (ps.date_end = '0000-00-00'
                              OR  ps.date_end > NOW()
                          )
                      AND  ps.store_id = '0' 
                   )
            ORDER BY  ps.priority ASC, ps.price ASC
            LIMIT  1
      ) AS special
    FROM  product_variation a, variation_description vd, product_to_category pc,
        variation v, product_description pd, product_to_store_alter p2s,
        product p
    WHERE  v.variation_group_id='18'
      AND  p2s.product_id = p.product_id
      AND  p2s.active = '1'
      AND  p2s.store_id = '0'
      AND  pc.product_id = p.product_id
      AND  a.variation_id = v.variation_id
      AND  vd.variation_id = v.variation_id
      AND  vd.variation_id = a.variation_id
      AND  v.variation_id = '1418'
      AND  p.product_id = pd.product_id
      AND  a.product_id = pd.product_id
      AND  p2s.quantity > '0'
      AND  p.product_id != '17230'
      AND  p.status = '1'
      AND  p.date_available <= NOW()
    GROUP BY  p.product_id
    ORDER BY  a.product_id DESC
    LIMIT  8;

The above query takes around 2 secs to execute. MySQL Server 5.5, key_buffer is 256MB and innodb_buffer_pool has 128M. Sort and join buffers have 8M each.

Anything where I can improve, cause such queries run multiple times for a single page. Help would be appreciated!

UPDATE : Explain statement

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY v   const   PRIMARY,variation_group_id  PRIMARY 8   const,const 1   Using temporary; Using filesort
1   PRIMARY vd  ref PRIMARY PRIMARY 4   const   1   Using where
1   PRIMARY p   ref PRIMARY,status  status  1   const   13711   Using where
1   PRIMARY pd  ref PRIMARY PRIMARY 4   ninec.p.product_id  1
1   PRIMARY p2s eq_ref  PRIMARY PRIMARY 8   ninec.p.product_id,const    1   Using where
1   PRIMARY a   ref product_id,variation_id product_id  4   ninec.p2s.product_id    14  Using where
1   PRIMARY pc  ref product_id  product_id  4   ninec.p.product_id  3   Using index
2   DEPENDENT SUBQUERY  ps  ref customer_group_id,store_id,product_id   customer_group_id   4   const   1   Using where; Using filesort

UPDATE

Adding CREATE TABLE statements for above tables :

CREATE TABLE variation (
  variation_id int(11) NOT NULL AUTO_INCREMENT,
  variation_group_id int(11) NOT NULL,
  sort_order int(3) NOT NULL,
  PRIMARY KEY (variation_id),
  KEY variation_group_id (variation_group_id)
) ENGINE=MyISAM

CREATE TABLE variation_description (
  variation_id int(11) NOT NULL,
  language_id int(11) NOT NULL,
  name varchar(64) COLLATE utf8_bin NOT NULL,
  description varchar(255) COLLATE utf8_bin NOT NULL,
  seo varchar(255) COLLATE utf8_bin NOT NULL,
  image varchar(255) COLLATE utf8_bin NOT NULL,
  is_moq tinyint(1) NOT NULL,
  url_link varchar(255) COLLATE utf8_bin NOT NULL,
  video text COLLATE utf8_bin NOT NULL,
  download_link varchar(255) COLLATE utf8_bin NOT NULL,
  launch_date date NOT NULL,
  PRIMARY KEY (variation_id,language_id),
  KEY seo (seo),
  KEY language_id (language_id)
) ENGINE=MyISAM

CREATE TABLE product (
  product_id int(11) NOT NULL AUTO_INCREMENT,
  model varchar(64) COLLATE utf8_bin NOT NULL,
  sku varchar(64) COLLATE utf8_bin NOT NULL,
  upc varchar(12) COLLATE utf8_bin NOT NULL,
  location varchar(128) COLLATE utf8_bin NOT NULL,
  quantity int(4) NOT NULL DEFAULT '0',
  rs_quantity int(4) NOT NULL,
  moq_quantity int(4) NOT NULL,
  stock_status_id int(11) NOT NULL,
  image varchar(255) COLLATE utf8_bin DEFAULT NULL,
  watermark_pos int(1) NOT NULL,
  watermark_colour int(1) NOT NULL,
  manufacturer_id int(11) NOT NULL,
  vendor_id varchar(25) COLLATE utf8_bin NOT NULL,
  shipping tinyint(1) NOT NULL DEFAULT '1',
  mrp_price decimal(15,2) NOT NULL,
  price decimal(15,4) NOT NULL DEFAULT '0.0000',
  org_price decimal(15,0) NOT NULL,
  moq_price decimal(15,0) NOT NULL,
  points int(8) NOT NULL DEFAULT '0',
  tax_class_id int(11) NOT NULL,
  date_available date NOT NULL,
  weight decimal(15,8) NOT NULL DEFAULT '0.00000000',
  weight_class_id int(11) NOT NULL DEFAULT '0',
  length decimal(15,8) NOT NULL DEFAULT '0.00000000',
  width decimal(15,8) NOT NULL DEFAULT '0.00000000',
  height decimal(15,8) NOT NULL DEFAULT '0.00000000',
  length_total float NOT NULL,
  length_class_id int(11) NOT NULL DEFAULT '0',
  subtract tinyint(1) NOT NULL DEFAULT '1',
  minimum int(11) NOT NULL DEFAULT '1',
  sort_order int(11) NOT NULL DEFAULT '0',
  best_seller int(1) NOT NULL,
  new_arrival int(1) NOT NULL,
  readytoship int(1) NOT NULL,
  exclusive int(1) NOT NULL,
  sale int(1) NOT NULL,
  is_readymade tinyint(1) NOT NULL,
  status tinyint(1) NOT NULL DEFAULT '0',
  date_added datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  date_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  viewed int(5) NOT NULL DEFAULT '0',
  time_to_ship varchar(25) COLLATE utf8_bin NOT NULL DEFAULT '5',
  wash_care varchar(255) COLLATE utf8_bin NOT NULL,
  measure_group_id int(11) NOT NULL,
  wishlist int(11) NOT NULL,
  shoppingcart int(11) NOT NULL,
  barcode varchar(99) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (product_id),
  KEY sort_order (sort_order),
  KEY best_seller (best_seller),
  KEY new_arrival (new_arrival),
  KEY readytoship (readytoship),
  KEY exclusive (exclusive),
  KEY sale (sale),
  KEY status (status),
  KEY viewed (viewed),
  KEY time_to_ship (time_to_ship),
  KEY model (model)
) ENGINE=InnoDB


CREATE TABLE product_description (
  product_id int(11) NOT NULL AUTO_INCREMENT,
  language_id int(11) NOT NULL,
  name varchar(255) COLLATE utf8_bin NOT NULL,
  description text COLLATE utf8_bin NOT NULL,
  meta_description varchar(255) COLLATE utf8_bin NOT NULL,
  meta_keyword varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (product_id,language_id),
  KEY name (name),
  KEY language_id (language_id)
) ENGINE=MyISAM

CREATE TABLE product_special (
  product_special_id int(11) NOT NULL AUTO_INCREMENT,
  product_id int(11) NOT NULL,
  customer_group_id int(11) NOT NULL,
  priority int(5) NOT NULL DEFAULT '1',
  price decimal(15,4) NOT NULL DEFAULT '0.0000',
  date_start date NOT NULL DEFAULT '0000-00-00',
  date_end date NOT NULL DEFAULT '0000-00-00',
  store_id int(11) NOT NULL,
  PRIMARY KEY (product_special_id),
  KEY product_id (product_id),
  KEY customer_group_id (customer_group_id),
  KEY store_id (store_id)
) ENGINE=MyISAM

CREATE TABLE product_to_category (
  product_id int(11) NOT NULL,
  category_id int(11) NOT NULL,
  KEY product_id (product_id,category_id),
  KEY category_id (category_id,product_id)
) ENGINE=InnoDB

CREATE TABLE product_to_store_alter (
  product_id int(11) NOT NULL,
  store_id int(11) NOT NULL DEFAULT '0',
  location varchar(128) NOT NULL,
  quantity int(10) NOT NULL DEFAULT '0',
  price decimal(15,4) NOT NULL DEFAULT '0.0000',
  substract tinyint(1) NOT NULL DEFAULT '1',
  minimum int(11) NOT NULL DEFAULT '1',
  active tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (product_id,store_id)
) ENGINE=MyISAM

CREATE TABLE product_variation (
  product_id int(11) NOT NULL,
  variation_id varchar(255) COLLATE utf8_bin NOT NULL,
  language_id int(11) NOT NULL,
  KEY product_id (product_id),
  KEY variation_id (variation_id)
) ENGINE=MyISAM

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Use Numeric Column Types For Numeric Values (query line: 40): Referencing a numeric value (e.g. 18) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  4. Use Numeric Column Types For Numeric Values (query line: 42): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  5. Use Numeric Column Types For Numeric Values (query line: 43): Referencing a numeric value (e.g. 0) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  6. Use Numeric Column Types For Numeric Values (query line: 48): Referencing a numeric value (e.g. 1418) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  7. Use Numeric Column Types For Numeric Values (query line: 51): Referencing a numeric value (e.g. 0) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  8. Use Numeric Column Types For Numeric Values (query line: 52): Referencing a numeric value (e.g. 17230) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  9. Use Numeric Column Types For Numeric Values (query line: 53): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  10. Use Numeric Column Types For Numeric Values (query line: 16): Referencing a numeric value (e.g. 1) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  11. Use Numeric Column Types For Numeric Values (query line: 26): Referencing a numeric value (e.g. 0) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
ALTER TABLE `product` ADD INDEX `product_idx_status_product_id_date_avail` (`status`,`product_id`,`date_available`);
ALTER TABLE `product` ADD INDEX `product_idx_product_id` (`product_id`);
ALTER TABLE `product_description` ADD INDEX `product_descriptio_idx_product_id` (`product_id`);
ALTER TABLE `product_special` ADD INDEX `product_special_idx_custo_date_date_store_produ_prior` (`customer_group_id`,`date_start`,`date_end`,`store_id`,`product_id`,`priority`);
ALTER TABLE `product_special` ADD INDEX `product_special_idx_priority_price` (`priority`,`price`);
ALTER TABLE `product_to_category` ADD INDEX `product_category_idx_product_id` (`product_id`);
ALTER TABLE `product_to_store_alter` ADD INDEX `product_store_idx_active_store_product_quantit` (`active`,`store_id`,`product_id`,`quantity`);
ALTER TABLE `product_variation` ADD INDEX `product_variation_idx_variation_id_product_id` (`variation_id`,`product_id`);
ALTER TABLE `variation` ADD INDEX `variation_idx_variation_id_variation_id` (`variation_group_id`,`variation_id`);
ALTER TABLE `variation_description` ADD INDEX `variation_descript_idx_variation_id` (`variation_id`);
The optimized query:
SELECT
        DISTINCT *,
        a.*,
        vd.seo,
        p.image,
        p2s.price,
        p.product_id,
        p.image,
        pd.name,
        (SELECT
            ps.price 
        FROM
            product_special ps 
        WHERE
            ps.product_id = p.product_id 
            AND ps.customer_group_id = '1' 
            AND (
                (
                    ps.date_start = '0000-00-00' 
                    OR ps.date_start < NOW()
                ) 
                AND (
                    ps.date_end = '0000-00-00' 
                    OR ps.date_end > NOW()
                ) 
                AND ps.store_id = '0'
            ) 
        ORDER BY
            ps.priority ASC,
            ps.price ASC LIMIT 1) AS special 
    FROM
        product_variation a,
        variation_description vd,
        product_to_category pc,
        variation v,
        product_description pd,
        product_to_store_alter p2s,
        product p 
    WHERE
        v.variation_group_id = '18' 
        AND p2s.product_id = p.product_id 
        AND p2s.active = '1' 
        AND p2s.store_id = '0' 
        AND pc.product_id = p.product_id 
        AND a.variation_id = v.variation_id 
        AND vd.variation_id = v.variation_id 
        AND vd.variation_id = a.variation_id 
        AND v.variation_id = '1418' 
        AND p.product_id = pd.product_id 
        AND a.product_id = pd.product_id 
        AND p2s.quantity > '0' 
        AND p.product_id != '17230' 
        AND p.status = '1' 
        AND p.date_available <= NOW() 
    GROUP BY
        p.product_id 
    ORDER BY
        a.product_id DESC LIMIT 8

Related Articles



* original question posted on StackOverflow here.