Marek Kaliszuk Marek Kaliszuk - 26 days ago 10
MySQL Question

Slow query due sql count - how to improve?

i've got quite a problem with sql query - it's getting slower due more rows to check. Db is growing up fast... In slowlog from sql i get warning:


Time: 161113 15:55:55



User@Host: ....



Query_time: 13.133105 Lock_time: 0.000487 Rows_sent: 1 Rows_examined: 91729




Query uses count and looks like below:

SELECT COUNT(*) AS `records_found` FROM `product_to_features` AS `product_to_feature` WHERE `id_feature_value` = 0 AND `id_product` IN (0, '20924', '20950', '20951', '21006', '21007', '21008', '21009', '21010', '21017', '21029', '21030', '21045', '21046', '21057', '21058', '21059', '21079', '21080', '21103', '21104', '21105', '21106', '21107', '21117', '21123', '21244', '21463', '21464', '21466', '21465', '21503', '21504', '21505', '21506', '21518', '21536', '21537', '21538', '21539', '21161', '21162', '21191', '21192', '21215', '21227', '21243', '21273', '21274', '21293', '21294', '21295', '21357', '21358', '21447', '21479', '21480', '21566', '21567', '21678', '21682', '21589', '21592', '21596', '21597', '21680', '21685', '21653', '21648', '21650', '21757', '21756', '21758', '21759', '21798', '21802', '21810', '21812', '21813', '21815', '21817', '21819', '21820', '21874', '21898', '21903', '21904', '21906', '21909', '21879', '21882', '21895', '21901', '21967', '21969', '21980', '21965', '21995', '22010', '22013', '22016', '22022', '22025', '22030', '22033', '22036', '22067', '22107', '22102', '22100', '22099', '22098', '22097', '22096', '22095', '22094', '22092', '22090', '22101', '22083', '22087', '22089', '22188', '22201', '22205', '22189', '22202', '22206', '22216', '22217', '22219', '22164', '22276', '22006', '22253', '22093', '22298', '22313', '22314', '22315', '22330', '22385', '22386', '22405', '22406', '22412', '22413', '22461', '22462', '22476', '22477', '22480', '22503', '22504', '22506', '22505', '22507', '22565', '22541', '22542', '22543', '22600', '22601', '22602', '22656', '22657', '22658', '22659', '22660', '22628', '22629', '22630', '22558', '22560', '22561', '22562', '22564', '22648', '23033', '23038', '23039', '23040', '23255', '23256', '23257', '23258', '23259', '23260', '23261', '23262', '23263', '23340', '23142', '23143', '23144', '23145', '23151', '23153', '23158', '23160', '23155', '23166', '23185', '23186', '23188', '23189', '23197', '23198', '23199', '23209', '23174', '23175', '23231', '23232', '23233', '23244', '23271', '23272', '23273', '23274', '23275', '23276', '23279', '23287', '23288', '23289', '23285', '23286', '23308', '23321', '23322', '23327', '23307', '23310', '23309', '23167', '23047', '23051', '23058', '23059', '23029', '23337', '23338', '23963', '23968', '23977', '23978', '23987', '23962', '24006', '24007', '24102', '24106', '24107', '24108', '24126', '24131', '24132', '24134', '24390', '24393', '24394', '24320', '24319', '24317', '24318', '24301', '24302', '24304', '24285', '24286', '24293', '24465', '24466', '24469', '24471', '24473', '24474', '24475', '24224', '24225', '24226', '24227', '24332', '24420', '24430', '24499', '24500', '24502', '24503', '24504', '24612', '24613', '24515', '24518', '24524', '24631', '24645', '24646', '24647', '24648', '25043', '25052', '25058', '25060', '25061', '25062', '25068', '25067', '25069', '25081', '25082', '25096', '25097', '25098', '25099', '25110', '25111', '25124', '25123', '25136', '25139', '25140', '25152', '25165', '25172', '25173', '25174', '25183', '25192', '25166', '25194', '25204', '25205', '25218', '25226', '25255', '25260', '25269', '25270', '25271', '25220', '25314', '25330', '25329', '25351', '25352', '25359', '25360', '25367', '25368', '25313', '25371', '25377', '25379', '25443', '25445', '25459', '25460', '25466', '25467', '25469', '25507', '25510', '25516', '25532', '25533', '25534', '25535', '25552', '25564', '25565', '25583', '25584', '25585', '25586', '25698', '25808', '25809', '25821', '25822', '25823', '25837', '25839', '25840', '25856', '25890', '25891', '25892', '25893', '25911', '25912', '25914', '25915', '25916', '25932', '25933', '25951', '25952', '25975', '25977', '25978', '25979', '25980', '25395', '24022', '24019', '24027', '24018', '25410', '25411', '25439', '24051', '24033', '24032', '24044', '24041', '24045', '24192', '24188', '24190', '24204', '24179', '24209', '24150', '24149', '24151', '24143', '24247', '24240', '24256', '24245', '24242', '24268', '24250', '26059', '26060', '26082', '26083', '26084', '26096', '26097', '26042', '26044', '26045', '26127', '26128', '26129', '26130', '26131', '26135', '26136', '26137', '26143', '26144', '26160', '26161', '26175', '26177', '26179', '26197', '26198', '26208', '26209', '26210', '26225', '26226', '26227', '26228', '26115', '26116', '26117', '26126', '26257', '26258', '26259', '26271', '26272', '26294', '26295', '26296', '26297', '26327', '26328', '26329', '26330', '26331', '26365', '26367', '26368', '26369', '26385', '26386', '26387', '26400', '26403', '26404', '26406', '26407', '26525', '26527', '26528', '20705', '20706', '20709', '20708', '20707', '24020', '24122', '26556', '26578', '26579', '26580', '26581', '26582', '26600', '26602', '26603', '26604', '26605', '26612', '26614', '26615', '26616', '26617', '26633', '26634', '26635', '26636', '26637', '26654', '26656', '26658', '26676', '26677', '26678', '26680', '26681', '26682', '26683', '26684', '26685', '26755', '26767', '26768', '26769', '26770', '26705', '26706', '26808', '26809', '26817', '26818', '26819', '26835', '26786', '26865', '26876', '26874', '26881', '26892', '26915', '26922', '26930', '26931', '20749', '20750', '20751', '20795', '20796', '20797', '20798', '20799', '20881', '20882', '26979', '26996', '26997', '27016', '27027', '27032', '27036', '27043', '27044', '27052', '27053', '27066', '27067', '20816', '20817', '20818', '20819', '20820', '20821', '20822', '20823', '20898', '20899', '20900', '20901', '27083', '27100', '27101', '27102', '20921', '20938', '20962', '20963', '20964', '20965', '20966', '20967', '20968', '20969', '27117', '27118', '27119', '27137', '27138', '27149', '27151', '27153', '27148', '27150', '27152', '27181', '27182', '27183', '27197', '27209', '27210', '27211', '27222', '27223', '27224', '27266', '26732', '26731', '26537', '27461', '27470', '27475', '27476', '28171', '28177', '28183', '28189', '28193', '28199', '28204', '28209', '28217', '28286', '28352', '29027', '29028', '29036', '29037', '29055', '29047', '29052', '29048', '29064', '29065', '29073', '26875', '26843', '26734', '26733', '26730', '26538', '29081', '29082', '29090', '29100', '29101', '29108', '29109', '29123', '29124', '29133', '29134', '29151', '29159', '29160', '29165', '29178', '29179', '29180', '29197', '29200', '29212', '29220', '29221', '29222', '29234', '29247', '29248', '29249', '29250', '29251', '29252', '29270', '29271', '29296', '29297', '29302', '29305', '29317', '29329', '29330', '29336', '29338', '29343', '29356', '29357', '29359', '26901', '26844', '22068', '29411', '29412', '29430', '29431', '29439', '29440', '29417', '29418', '29424', '29425', '29444', '29462', '29463', '29468', '29467', '29476', '29477', '29483', '29492', '29541', '29542', '29498', '29499', '29500', '29501', '29502', '29505', '29508', '29509', '29510', '29511', '29513', '29514', '29515', '29516', '29512', '29518', '29519', '29520', '24279', '26539', '26842', '26873', '29614', '29615', '29616', '29618', '29642', '29644', '29643', '29652', '29692', '29694', '29695', '29697', '29698', '29699', '29700', '29712', '29731', '29736', '30163', '30247', '30102', '30007', '30382', '29896', '29911', '30489', '30177', '29863', '24271', '26540', '26889', '30578', '30579', '30580', '30606', '30647', '30633', '30634', '30640', '30641', '30654', '30655', '30668', '30667', '30685', '30686', '30687', '30723', '30709', '30754', '30755', '30790', '30776', '30777', '30796', '30797', '30798', '30799', '30809', '30841', '30853', '30852', '30851', '30880', '30898', '30909', '30920', '30958', '30959', '30975', '30976', '31002', '31003', '31030', '31021', '31037', '31038', '31081', '31080', '31091', '31090', '31108', '31107', '31106', '31129', '31128', '31127', '31126', '31125', '31124', '31123', '31122', '31163', '31162', '31161', '31160', '31159', '31158', '31157', '31156', '31180', '31179', '31178', '31183', '31184', '31203', '31199', '31198', '31221', '31219', '31233', '31232', '31231', '31230', '31229', '31250', '31255', '31254', '31278', '31274', '31282', '31283', '31284', '31285', '26888', '31311', '31312', '31366', '31367', '31373', '31374', '31382', '31383', '31403', '31411', '31420', '31425', '31435', '31451', '31473', '31474', '31489', '31500', '31526', '31536', '31544', '31556', '31578', '31650', '31654', '31655', '31672', '31673', '31675', '31689', '31693', '31700', '31701', '31713', '31727', '31729', '26659', '26787', '26785', '26788', '26789', '31808', '31809', '31810', '26816', '26854', '25370', '32048', '25424', '25423', '32434', '32443', '26864', '32451', '32445', '32458', '32477', '32487', '32570', '32569', '32492', '32568', '32567', '32505', '32418', '32571', '32044', '32045', '32581', '32584', '32590', '32594', '32595', '32707', '32723', '32724', '32736', '32749', '32751', '32750', '32752', '32755', '32777', '32778', '32779', '32787', '32788', '32789', '32790', '32800', '32801', '32802', '32894', '32895', '32896', '32897', '32898', '32901', '32902', '32903', '32904', '32963', '32965', '32979', '32986', '29487');


Table has 4 indexes: id, id_feature_value, id_product, id_feature;

Query is quite simple - get all products features that match query - "if client looks for yellow products - show them".

Question is - How to speed up query?

Answer

Other answers to this question suggest that the cause is because of the large IN clause. I do not think this is the case. Instead, I think it is a data type issue which is preventing SQL from calculating an efficient query plan.

In your code, the first value of the IN clause shown is not quoted and so will be considered an INT value, the others are quoted and so will be treated as strings. Therefore I think SQL is doing an IMPLICIT conversion of your column 'id_product' and this then wrecks the sargability of the WHERE clause. I think adding quotes to the first value in your list of constants will improve performance significantly.

Converting the IN clause to a temporary table will solve the issue, by accident, because it corrects the mismatch in the data types.

This question has tags for MYSQL and SQL Server. My investigation was on SQL Server 2016 and so may not apply to other environments.