1

I'm by no means a DBA expert. Rather, a Magento developer having to troubleshoot some slow queries. I'll jump right in -- here's my attempt to EXPLAIN the troublesome query:

  EXPLAIN
  SELECT `s`.`query_id`,
         `s`.`product_id`,
         SUM(weight) AS `weight`
  FROM
    (SELECT `w1`.`query_id`,
            `w1`.`product_id`,
            SUM(weight) AS `weight`,
            GROUP_CONCAT(data_index) AS `data_index`
     FROM
       (SELECT 1189 AS `query_id`,
               `i`.`entity_id` AS `product_id`,
               `i`.`weight`,
               `i`.`data_index`
        FROM `activo_advancedsearch_weighted_search` AS `i`
        WHERE (i.store_id = 1)
          AND (`i`.`data_index` LIKE '%southern%')
        UNION ALL SELECT 1189 AS `query_id`,
                         `i`.`entity_id` AS `product_id`,
                         `i`.`weight`,
                         `i`.`data_index`
        FROM `activo_advancedsearch_weighted_search` AS `i`
        WHERE (i.store_id = 1)
          AND (`i`.`data_index` LIKE '%proper%')) AS `w1`
     GROUP BY `product_id`) AS `s`
  WHERE (((`s`.`data_index` LIKE '%southern%'
           OR `s`.`data_index` LIKE '%Southern%')
          AND (`s`.`data_index` LIKE '%proper%')))
  GROUP BY `product_id`
  ORDER BY `weight` DESC;

This table has anywhere between 4 and 5 million rows. The server is a dedicated DB server running on MySQL 5.5.42-37.1 and all the tables here will be running under InnoDB storage.

And the explained result:

+----+--------------+------------+------+----------------------------------------------------+----------------------------------------------------+---------+------+---------+----------------------------------------------+
| id | select_type  | table      | type | possible_keys                                      | key                                                | key_len | ref  | rows    | Extra                                        |
+----+--------------+------------+------+----------------------------------------------------+----------------------------------------------------+---------+------+---------+----------------------------------------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL                                               | NULL                                               | NULL    | NULL |      46 | Using where; Using temporary; Using filesort |
|  2 | DERIVED      | <derived3> | ALL  | NULL                                               | NULL                                               | NULL    | NULL |  428075 | Using filesort                               |
|  3 | DERIVED      | i          | ref  | IDX_ACTIVO_ADVANCEDSEARCH_WEIGHTED_SEARCH_STORE_ID | IDX_ACTIVO_ADVANCEDSEARCH_WEIGHTED_SEARCH_STORE_ID | 2       |      | 2663774 | Using where                                  |
|  4 | UNION        | i          | ref  | IDX_ACTIVO_ADVANCEDSEARCH_WEIGHTED_SEARCH_STORE_ID | IDX_ACTIVO_ADVANCEDSEARCH_WEIGHTED_SEARCH_STORE_ID | 2       |      | 2663774 | Using where                                  |
| NULL | UNION RESULT | <union3,4> | ALL  | NULL                                               | NULL                                               | NULL    | NULL |    NULL |                                              |
+----+--------------+------------+------+----------------------------------------------------+----------------------------------------------------+---------+------+---------+----------------------------------------------+

Problems I can identify:

  • Using a temporary table
  • Using filesort
  • The number of rows being read

Update: Also, if it helps, here's the table schema.

CREATE TABLE `activo_advancedsearch_weighted_search` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store Id',
  `weight` int(10) unsigned NOT NULL DEFAULT '1' COMMENT 'Weight',
  `data_index` text COMMENT 'Data',
  PRIMARY KEY (`id`),
  KEY `IDX_ACTIVO_ADVANCEDSEARCH_WEIGHTED_SEARCH_ENTITY_ID` (`entity_id`),
  KEY `IDX_ACTIVO_ADVANCEDSEARCH_WEIGHTED_SEARCH_STORE_ID` (`store_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17872647 DEFAULT CHARSET=utf8 COMMENT='Advanced Search Weighted Attribute Index Table'

I've read articles about index optimizations, but to be honest the textbook scenarios did little to help me in the view of a real world example like the above.

I'm not asking you to tell me what indexes I should use (though I won't stop you!). But at least, please take this example to teach me a little bit about how the optimization process would play out in this case.

9
  • what RDBMS are you using ? – Kin Shah Jun 4 '15 at 0:43
  • 1
    Are you allowed to change the query? Or only add indexes? Please also tell us the version of MySQL (or MariaDB) you are using. – ypercubeᵀᴹ Jun 4 '15 at 1:04
  • 1
    Get rid of the derived/sub selects and make them temporary tables. MySQL cannot use indexes on these tables. SQL-Server and other do use indexes on the derived tables. I know there is a item logged in MySQL to get this feature added. – Namphibian Jun 4 '15 at 2:56
  • 1
    Unfortunately mysql 5.5 has no Index condition pushdown AFAIK - that would be really good to have together with index over (store_id, data_index).. You might do one "group by product_id" in unioned subqueries and use index (store_id, product_id) for it. But thats not so small rewrite. With query like this you probably have not much chance to get rid of tmp table or filesort. Would "LIKE 'southern%'" return the same rows (without leading %) or do you need to match in the middle of the string? – jkavalik Jun 4 '15 at 6:59
  • 1
    @RickBuczynski There might be possible speed up by using covering index - that means creating index which covers all columns used in query - that way instead scanning entire table only index is scanned, which can be much smaller and is usually beter organized for the needs of that query, in your case probably (store_id, data_index, entity_id, weight) unless I missed something. – jkavalik Jun 4 '15 at 18:18
2

The query has a lot of bloat:

  • Two group by, the second is completely unnecessary.
  • Three levels of nesting, the last is also completely unnecessary.
  • It uses GROUP_CONCAT() to find all the product_id that have rows with 'southern' and rows with 'proper'. Not the best way in my opinion.
  • The 1189 AS query_id seems to return redundant information (the same value in all the rows of the result.)
  • Using UNION ALL instead of OR may be ok for efficiency but the group by in the next step is probably the performance killer (grouping 400K rows without the use of any index).

Things that are not a problem:

  • Using where; Using temporary; Using filesort This looks bad but sorting 46 rows is not going to be an issue. using filesort in MySQL Explain does not mean that a file is used (not a very good naming choice indeed).

One thing you can do without changing the query:

  • add an index on (store_id, entity_id, data_index, weight).
    This will basically create a copy of the entire table (but a bit narrower as the primary index in InnoDB tables uses some extra hidden space for internal purposes). The query will be able to use the index, doing a partial index scan (only the rows with store_id = 1) and then get all the info needed from the index (no table scan at all). Unfortunately, as the the query is written now, it will still need 2 partial index scans (and then all the unnecessary bloat that follows).

If you can change the query though, there are various options to try:

  • First, we could replace UNION ALL with OR so we only do one table scan (or one index scan if we have the index suggested above) and not two. Also remove the the levels of nesting and only have one, and only one GROUP BY by altering the query logic (removing the GROUP_CONCAT() and using HAVING with counts and sums):

    SELECT 1189 AS query_id,
           i.entiity_id AS product_id,
           SUM(CASE WHEN i.data_index LIKE '%southern%' THEN i.weight ELSE 0 END) 
         + SUM(CASE WHEN i.data_index LIKE '%proper%' THEN i.weight ELSE 0 END)
             AS weight
    FROM activo_advancedsearch_weighted_search AS i
    WHERE i.store_id = 1
      AND ( i.data_index LIKE '%southern%'
         OR i.data_index LIKE '%proper%'
          )
    GROUP BY i.entity_id
    HAVING SUM(i.data_index LIKE '%southern%') > 0
       AND SUM(i.data_index LIKE '%proper%') > 0
    ORDER BY weight DESC;
    
1

First thing I would do is to combine the inner UNION ALL queries to a single query with the two 'like' conditions 'OR'd together and bracketed away from the other filter. This means it would only pass over that table once instead of twice. You can't add an index to help the 'like' filter as you have a '%' at the beginning.

Sorry for brevity of reply, on my phone... :))

1

Let's walk through the steps. This will make some of the suggestions so far not significant.

  1. Scan 2.6 million rows out of 4-5M. Please verify by finding out how many rows have store_id = 1.
  2. Scan 2.6 million rows out of 4-5M again. (Yes, a single pass with an OR would be better since there is no way to make use of INDEX(store_id, data_index), which is because of the leading wildcard in the LIKE.)
  3. UNION ALL -- You now have a tmp table with 5.2M rows, some of which might be duplicate if a row can have both "southern" and "proper". (Possibly a 'bug'?)
  4. GROUP BY -- Scan the 5.2M rows to boil it down to 428K rows (into another tmp table).
  5. Apply a WHERE which might do nothing useful. What is the COLLATION for data_index? If it is case-insensitive (_ci), then 'Southern' = 'southern'. (Another 'bug'?)
  6. I am unclear on what step drops you from 428K rows to 46.

I'm confused by 'southern' and 'proper'. The UNION is like OR, but the outer WHERE seems to want both, that is, AND. Perhaps making the inner WHEREs like the outer one will help (some).

The bulk of the time is spent in steps 1 and 2. Merging them would cut the time nearly in half. But still it is too long. So, the final suggestion:

See if a FULLTEXT(data_index) can be used. If so, modify the query (change LIKE to MATCH, etc). Then query (with the UNION) will run immensely faster.

It smells like Magento is getting in the way.

1
  • Thanks for the walkthrough. You are right that Magento "gets in the way," in that the query is generated through an abstraction layer of its framework. I have a lot of work to do to test all of these suggestions, so it will be a while to get back to everyone. – Rick Buczynski Jun 11 '15 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.