У меня есть две таблицы (NewProducts и OldProducts), которые сравниваются. NewProducts насчитывает около 68 000 записей, а OldProducts — около 51 000. Я использую индекс покрытия для каждой таблицы, однако выполнение запроса занимает 20 минут, поэтому я не использую его должным образом. Действительно ли индекс покрытия применим к нескольким таблицам? Что я делаю неправильно? Спасибо.
Вот мой код запроса и индексы:
$querystring = "SELECT newProducts.Id, newProducts.SKU,
newProducts.Title, oldProducts.Title, oldProducts.product_Id
FROM
newProducts, oldProducts
WHERE
trim(newProducts.SKU)=trim(oldProducts.SKU) and
trim(newProducts.Title)=trim(oldProducts.Title) and
oldProducts.Position=1 and
oldProducts.Customer=$shop";
Indexes for NewProducts:
Primary: Id
Index: SKU, Title, customer (not unique)
Indexes for OldProducts:
Primary: Id
Index: Product_id (not unique)
Index: SKU, Title, Postition, Customer (not unique)
?>
CREATE TABLE `NewProducts` (
`Id` bigint(11) NOT NULL,
`Title` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
`Category` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`Office` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
`Rehashed` smallint(6) NOT NULL,
`Quantity` smallint(6) NOT NULL,
`Price1` decimal(7,2) NOT NULL,
`Price2` decimal(7,2) NOT NULL,
`Price3` decimal(7,2) NOT NULL,
`Price4` decimal(7,2) NOT NULL,
`created_at` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`OldQuantity` int(11) NOT NULL,
`SKU` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`Source` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
`customer` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `I-T-S` (`ItemId`,`Title`,`SKU`),
KEY `customer` (`customer`),
KEY `Title` (`Title`,`Rehashed`),
KEY `SKU` (`SKU`),
KEY `Title_2` (`Title`,`SKU`,`customer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `OldProducts` (
`barcode` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`compare_at_price` decimal(10,2) DEFAULT NULL,
`created_at` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`fulfillment` varchar(35) COLLATE utf8_unicode_ci DEFAULT NULL,
`grams` decimal(10,2) DEFAULT NULL,
`id` bigint(11) NOT NULL,
`management` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`policy` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`size` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`color` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`type` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`position` int(11) DEFAULT NULL,
`price` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
`product_id` bigint(11) NOT NULL,
`SKU` varchar(55) COLLATE utf8_unicode_ci DEFAULT NULL,
`Title` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`customer` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `P-S-T-PO-CUST`
(`product_id`,`SKU`,`Title`,`position`,`customer`),
KEY `product_id` (`product_id`),
TRIM
это злодей Когда вы скрываете индексированный столбец (например, SKU
) внутри функции (например, TRIM
), индекс не может быть использован.
Очистите ваши данные:
TRIM
перед вставкой (или как она вставляет).UPDATE tbl SET SKU = TRIM(SKU), title = TRIM(title);
— для каждого столаSELECT
: TRIM(SKU)
-> SKU
и т.п.Даже лучше
oldProducts
должен иметь, в этом порядке,
`INDEX(`position`,`customer` ,`SKU`,`Title`, `product_id`)
С этим, WHERE
нужно смотреть только на old
строки для position=1 and customer =...
, (На самом деле, первые 2 столбца могут быть в любом порядке; последние 3 в любом порядке.)
Других решений пока нет …