Мне кажется, что это немного похоже на вопрос SQL для новичка, но начнем. Вот что я пытаюсь сделать:
. Я описал задействованные таблицы в конце этого сообщения, но вот запрос, который я выполняю
SELECT p.*, GROUP_CONCAT(pt.name)
FROM products p
LEFT JOIN product_tags_for_products pt4p ON (pt4p.product_id = p.id)
LEFT JOIN product_tags pt ON (pt.id = pt4p.product_tag_id)
GROUP BY p.id
ORDER BY p.created
LIMIT 30;
. Их около 280 000 продуктов, 130 тегов, 524 000 связанных записей, и я АНАЛИЗИЛ таблицы. Проблема в том, что для работы требуется более 80 секунд (на приличном оборудовании), что мне кажется неправильным.
Вот результаты EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p index NULL created 4 NULL 30 "Using temporary"
1 SIMPLE pt4p ref idx_product_tags_for_products idx_product_tags_for_products 3 s.id 1 "Using index"
1 SIMPLE pt eq_ref PRIMARY PRIMARY 4 pt4p.product_tag_id 1
Я думаю, что он делает что-то в неправильном порядке, т. Е. Упорядочивает результаты после соединения, используя большую временную таблицу, а затем ограничивает. План запроса в моей голове выглядел бы примерно так:
Это звучит просто, но похоже, что это не так - я что-то упускаю?
CREATE TABLE `products` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`rating` float NOT NULL,
`created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `created` (`created`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `product_tags_for_products` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`product_id` mediumint(8) unsigned NOT NULL,
`product_tag_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_product_tags_for_products` (`product_id`,`product_tag_id`),
KEY `product_tag_id` (`product_tag_id`),
CONSTRAINT `product_tags_for_products_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
CONSTRAINT `product_tags_for_products_ibfk_2` FOREIGN KEY (`product_tag_id`) REFERENCES `product_tags` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `product_tags` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
Информация о профиле обновлена по запросу Салмана А.
Status,
Duration,CPU_user,CPU_system,Context_voluntary,Context_involuntary,Block_ops_in,Block_ops_out,Messages_sent,Messages_received,Page_faults_major,Page_faults_minor,Swaps,Source_function,Source_file,Source_line
starting,
0.000124,0.000106,0.000015,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL
"Opening tables",
0.000022,0.000020,0.000003,0,0,0,0,0,0,0,0,0,"unknown function",sql_base.cc,4519
"System lock",
0.000007,0.000004,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",lock.cc,258
"Table lock",
0.000011,0.000009,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",lock.cc,269
init,
0.000055,0.000054,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,2524
optimizing,
0.000008,0.000006,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,833
statistics,
0.000116,0.000051,0.000066,0,0,0,0,0,0,0,1,0,"unknown function",sql_select.cc,1024
preparing,
0.000027,0.000023,0.000003,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1046
"Creating tmp table",
0.000054,0.000053,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1546
"Sorting for group",
0.000018,0.000015,0.000003,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1596
executing,
0.000004,0.000002,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,1780
"Copying to tmp table",
0.061716,0.049455,0.013560,0,18,0,0,0,0,0,3680,0,"unknown function",sql_select.cc,1927
"converting HEAP to MyISAM",
0.046731,0.006371,0.017543,3,5,0,3,0,0,0,32,0,"unknown function",sql_select.cc,10980
"Copying to tmp table on disk",
10.700166,3.038211,1.191086,538,1230,1,31,0,0,0,65,0,"unknown function",sql_select.cc,11045
"Sorting result",
0.777887,0.155327,0.618896,2,137,0,1,0,0,0,634,0,"unknown function",sql_select.cc,2201
"Sending data",
0.000336,0.000159,0.000178,0,0,0,0,0,0,0,1,0,"unknown function",sql_select.cc,2334
end,
0.000005,0.000003,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,2570
"removing tmp table",
0.106382,0.000058,0.080105,4,9,0,11,0,0,0,0,0,"unknown function",sql_select.cc,10912
end,
0.000015,0.000007,0.000007,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,10937
"query end",
0.000004,0.000002,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,5083
"freeing items",
0.000012,0.000012,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,6107
"removing tmp table",
0.000010,0.000009,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_select.cc,10912
"freeing items",
0.000084,0.000022,0.000057,0,1,0,0,1,0,0,0,0,"unknown function",sql_select.cc,10937
"logging slow query",
0.000004,0.000001,0.000001,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,1723
"logging slow query",
0.000049,0.000031,0.000018,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,1733
"cleaning up",
0.000007,0.000005,0.000002,0,0,0,0,0,0,0,0,0,"unknown function",sql_parse.cc,1691
Таблицы:
Products = 84,1MiB ( в таблице продуктов есть дополнительные поля, которые я опустил для ясности) Tags = 32KiB Linking table = 46,6MiB