Mysql – Extremely slow DB query even after increasing CPU and RAM of Droplet

MySQLoptimizationperformancequery-performance

I have a single database query which runs when a user searches a product on my site.
I have increased the Digital Ocean Droplet size from 1GB to 4GB package but still the restuls are not satisfactory.

While doing the tests with BlazeMeter and running myTop i came up with the below analysis.

1GB Ram

2GB Ram

4GB Ram

8GB Ram

The error rate is decreased but still the average respose is too slow.
Below is the query which i managed to get using myTop command. Can some one help me top optimize it ? Thanks in advance .

SELECT  *
FROM    (
        SELECT  p.*,
                m.logo as market_logo,
                m.slug as market_slug,
                c.id as cat_id,
                c.slug as cat_slug,
                c.name as category_name,
                c.name_ar as category_name_ar,
                sub.slug as sub_slug,
                sub.id as subcat_id,
                sub.name as subcategory_name,
                sub.name_ar as subcategory_name_ar,
                f.desc as descp_en,
                f.desc_ar as descp_ar
        FROM    category as c,
                sub_category as sub,
                promotion AS p,
                flyer AS f,
                market as m
        WHERE   c.id=p.category AND
                sub.id=p.sub_category AND
                f.market_id=m.id AND
                f.id=p.flyer_id AND
                f.active=1 AND 1 AND 1 AND 1 AND 1 AND
                p.product_name_en LIKE '%a%'
        ORDER BY
                p.price ASC
        ) DUMMY_ALIAS1
UNION
SELECT  *
FROM    (
        SELECT  p.*,
                m.logo as market_logo,
                m.slug as market_slug,
                c.id as cat_id,
                c.slug as cat_slug,
                c.name as category_name,
                c.name_ar as category_name_ar,
                sub.slug as sub_slug,
                sub.id as subcat_id,
                sub.name as subcategory_name,
                sub.name_ar as subcategory_name_ar,
                f.desc as descp_en,
                f.desc_ar as descp_ar
        FROM    category as c,
                sub_category as sub,
                promotion AS p,
                flyer AS f,
                market as m
        WHERE   c.id=p.category AND
                sub.id=p.sub_category AND
                f.market_id=m.id AND
                f.id=p.flyer_id AND
                f.active=1 AND 1 AND 1 AND 1 AND 1 AND
                sub.name LIKE '%a%'
        ORDER BY
                p.price ASC
        ) DUMMY_ALIAS2
UNION
SELECT  *
FROM    (
        SELECT  p.*,
                m.logo as market_logo,
                m.slug as market_slug,
                c.id as cat_id,
                c.slug as cat_slug,
                c.name as category_name,
                c.name_ar as category_name_ar,
                sub.slug as sub_slug,
                sub.id as subcat_id,
                sub.name as subcategory_name,
                sub.name_ar as subcategory_name_ar,
                f.desc as descp_en,
                f.desc_ar as descp_ar
        FROM    category as c,
                sub_category as sub,
                promotion AS p,
                flyer AS f,
                market as m
        WHERE   c.id=p.category AND
                sub.id=p.sub_category AND
                f.market_id=m.id AND
                f.id=p.flyer_id AND
                f.active=1 AND 1 AND 1 AND 1 AND 1 AND
                p.product SOUNDS LIKE '%a%'
        ORDER BY
                p.price ASC
        ) DUMMY_ALIAS3

This is the output of EXPLAIN:

*** row 1 ***
          table:  <derived2>
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  4525
          Extra:  NULL
*** row 2 ***
          table:  p
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  240072
          Extra:  Using where; Using filesort
*** row 3 ***
          table:  sub
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.sub_category
           rows:  1
          Extra:  Using where
*** row 4 ***
          table:  f
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.flyer_id
           rows:  1
          Extra:  Using where
*** row 5 ***
          table:  m
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.f.market_id
           rows:  1
          Extra:  NULL
*** row 6 ***
          table:  c
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.category
           rows:  1
          Extra:  Using where
*** row 7 ***
          table:  <derived4>
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  2925
          Extra:  NULL
*** row 8 ***
          table:  p
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  240072
          Extra:  Using filesort
*** row 9 ***
          table:  sub
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.sub_category
           rows:  1
          Extra:  Using where
*** row 10 ***
          table:  f
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.flyer_id
           rows:  1
          Extra:  Using where
*** row 11 ***
          table:  m
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.f.market_id
           rows:  1
          Extra:  NULL
*** row 12 ***
          table:  c
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.category
           rows:  1
          Extra:  Using where
*** row 13 ***
          table:  <derived6>
           type:  system
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  NULL
          Extra:  const row not found
*** row 14 ***
          table:  p
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  240072
          Extra:  Using where; Using filesort
*** row 15 ***
          table:  sub
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.sub_category
           rows:  1
          Extra:  Using where
*** row 16 ***
          table:  f
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.flyer_id
           rows:  1
          Extra:  Using where
*** row 17 ***
          table:  m
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.f.market_id
           rows:  1
          Extra:  NULL
*** row 18 ***
          table:  c
           type:  eq_ref
  possible_keys:  PRIMARY
            key:  PRIMARY
        key_len:  4
            ref:  mywebsite.p.category
           rows:  1
          Extra:  Using where
*** row 19 ***
          table:  <union1,3,5>
           type:  ALL
  possible_keys:  NULL
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  NULL
          Extra:  NULL

Best Answer

You need to be able to index your queries otherwise it's always going to be slow and scaling the server will not really help much due to the amount of data it has to read on every full table scan.

Option #1

I would suggest (if you want to keep everything in MySQL) to build a term index:

1) you need two more tables

CREATE TABLE `term` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`term` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `term` (`term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `promotion_term` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`term_id` int(10) unsigned NOT NULL,
`promotion_id` int(10) unsigned NOT NULL,
`num` int(10) unsigned NOT NULL DEFAULT '1',
-- the position of the first occurrence of the term in the promotion (for example: 'apple yellow apple' -> 0)
`position` smallint unsigned NOT NULL DEFAULT 0, 
PRIMARY KEY (`id`),
UNIQUE KEY `promotion_term_unique` (`term_id`,`promotion_id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2) Populate your data and keep it up to date

This can be done from your application, cronjob or trigger in mysql. Whichever you feel safe with.

3) Change your query to something like this

SELECT  p.*, ...
FROM promotion AS p
JOIN product_term AS pt ON pt.promotion_id = p.id
JOIN term AS t on t.id = pt.term_id
JOIN ...
WHERE t.term LIKE 'a%'  
-- Mind the prefix search instead of %a% (this take advantage of indexes)
...
GROUP BY p.id
ORDER BY pt.position, -- Order by position brings promotions to top which has apple as the first word, then comes where it is the second and so on
    p.price

You can also include sum(num) which will give you the possibility to order by relevance.

To read more about this you can take a look on a benchmark I did with MySQL Full text search alternatives: http://charlesnagy.info/it/mysql/full-text-search-in-mysql

Option #2

You can use some search engines which provides great full text search capabilities out of the box like Solr or Elasticsearch.

A very simple solution for what you want is to use the facet and facet filter with parameters like this:

/solr/select?q=*&facet=on&facet.prefix=a&facet.field=text&wt=json&omitHeader=true&facet.limit=5&rows=0&facet.mincount=1'

More on this and how to set it up with Nginx: Typeahead and autosuggest with pure Solr and Nginx

Option #3

You can use also redis for prefix match using ZRANGEBYLEX. That is quite limited compared to the previous options but also a way to implement autosuggest.

Best Answer

Related Solutions

Mysql – High Disk IO, How to mitigate

MySQL Indexing VarChar

Related Question