0

I'm using Neor Profile SQL to check some bottlenecks in my DB execution queries and trying to profile a single one to understand how to improve it.

The query

SELECT COUNT(DISTINCT e.entity_id)
FROM `catalog_product_entity` AS `e`
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND
                       price_index.customer_group_id = 0
         INNER JOIN `catalog_category_product_index` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = '1' AND
                       cat_index.visibility IN (3, 4) AND cat_index.category_id = '2'
WHERE (`e`.`entity_id` IN
       (78661, 78664, 78668, 78659, 78658, 78657, 78654, 78655, 78653, 78650, 78651, 78649, 78647, 78645, 78644, 78643,
        78642, 78641, 78638, 78640, 78637, 78635, 78633, 78627, 78626, 78619, 78603, 78600, 78599, 78598, 78596, 78594,
        78590, 78589, 78588, 78587, 78586, 78585, 78582, 78581, 78580, 78579, 78577, 78576, 78575, 78574, 78565, 78566,
        78562, 78551, 78550, 78546, 78542, 78538, 78535, 78532, 78530, 78529, 78527, 78526, 78524, 78518, 78508, 78510,
        78507, 78506, 78505, 78504, 78503, 78502, 78501, 78499, 78500, 78498, 78496, 78495, 78494, 78493, 78492, 78490,
        78491, 78489, 78480, 78485, 78477, 78475, 78473, 78470, 78468, 78460, 78465, 78456, 78455, 78448, 78441, 78445,
        78439, 78438, 78421, 78422, 78420, 78419, 78418, 78415, 78417, 78414, 78413, 78412, 78411, 78410, 78409, 78408,
        78405, 78404, 78403, 78398, 78392, 78391, 78390, 78386, 78376, 78374, 78361, 78360, 78355, 78352, 78347, 78345,
        78346, 78343, 78339, 78342, 78335, 78329, 78328, 78319, 78317, 78310, 78308, 78302, 78297, 78294, 78293, 78292,
        78284, 78278, 78275, 78274, 78272, 78271, 78269, 78267, 78265, 78263, 78254, 78253, 78251, 78250, 78249, 78247,
        78240, 78239, 78231, 78228, 78222, 78220, ... quite a few more ids like the above));

The explain

+--+-----------+-----------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-------+----------------------------------------------+----+------------------------+
|id|select_type|table      |type  |possible_keys                                                                                                                                           |key                             |key_len|ref                                           |rows|Extra                   |
+--+-----------+-----------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-------+----------------------------------------------+----+------------------------+
|1 |SIMPLE     |cat_index  |ref   |PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC                                                        |15D3C269665C74C2219037D534F4B0DC|6      |const,const                                   |25  |Using where; Using index|
|1 |SIMPLE     |e          |eq_ref|PRIMARY                                                                                                                                                 |PRIMARY                         |4      |db_easyoffers.cat_index.product_id            |1   |Using index             |
|1 |SIMPLE     |price_index|eq_ref|PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE|PRIMARY                         |8      |db_easyoffers.cat_index.product_id,const,const|1   |Using index             |
+--+-----------+-----------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-------+----------------------------------------------+----+------------------------+


The profile

| Status               | Duration  |
| -------------------- | --------- |
| starting             | 0,001545  |
| checking permissions | 0,000005  |
| checking permissions | 0,000002  |
| checking permissions | 0,000004  |
| Opening tables       | 0,000027  |
| After opening tables | 0,000005  |
| System lock          | 0,000004  |
| Table lock           | 0,000005  |
| init                 | 0,000784  |
| optimizing           | 0,00054   |
| statistics           | 13,078536 |
| preparing            | 0,000251  |
| executing            | 0,000003  |
| Sending data         | 0,009422  |
| end                  | 0,000056  |
| removing tmp table   | 0,000004  |
| end                  | 0,000003  |
| query end            | 0,000006  |
| closing tables       | 0,000002  |
| Unlocking tables     | 0,000006  |
| freeing items        | 0,000033  |
| updating status      | 0,00004   |
| cleaning up          | 0,000085  |

So what exactly is the row statistics and how can I see why it's taking so long so I can troubleshoot further?

Your Answer

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

Browse other questions tagged or ask your own question.