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?