我有一个需要10-20秒的查询,但我确信它可以优化,我只是不够好做到这一点.我想要一些帮助和解释,以便我可以将它应用于类似的查询.
这是我的查询：
SELECT
`store_formats`.`Store Nbr`,`store_formats`.`Store Name`,`store_formats`.`Format Name`,`eds_sales`.`Date`,sum(`eds_sales`.`EPOS Sales`) AS Sales,sum(`eds_sales`.`EPOS Quantity`) AS Quantity
FROM
`eds_sales`
INNER JOIN `item_codes` ON `eds_sales`.`Prime Item Nbr` = `item_codes`.`Customer Item`
INNER JOIN `store_formats` ON `eds_sales`.`Store Nbr` = `store_formats`.`Store Nbr`
WHERE
`eds_sales`.`Store Nbr` IN ($storenbr) AND
`eds_sales`.`Date` BETWEEN '$startdate' AND '$enddate' AND
`eds_sales`.`Client` = '$customer' AND
`eds_sales`.`Retailer` IN ($retailer) AND
`store_formats`.`Format Name` IN ($storeformat) AND
`item_codes`.`Item Number` IN ($products)
GROUP BY
`store_formats`.`Store Name`,`store_formats`.`Store Nbr`,`eds_sales`.`Date`
这是解释输出：
正如您将在那里看到的那样,我尝试并创建了几个索引,其中涉及的列没有太大成功.主要的延迟是由我认为复制到临时表引起的.
这些是涉及的表格：
store_formats：
CREATE TABLE `store_formats` (
`id` int(12) NOT NULL,`Store Nbr` smallint(5) UNSIGNED DEFAULT NULL,`Store Name` varchar(27) DEFAULT NULL,`City` varchar(19) DEFAULT NULL,`Post Code` varchar(9) DEFAULT NULL,`Region #` int(2) DEFAULT NULL,`Region Name` varchar(10) DEFAULT NULL,`Distr #` int(3) DEFAULT NULL,`Dist Name` varchar(26) DEFAULT NULL,`Square Footage` varchar(7) DEFAULT NULL,`Format` int(1) DEFAULT NULL,`Format Name` varchar(23) DEFAULT NULL,`Store Type` varchar(20) DEFAULT NULL,`TV Region` varchar(12) DEFAULT NULL,`Pharmacy` varchar(3) DEFAULT NULL,`Optician` varchar(3) DEFAULT NULL,`Home Shopping` varchar(3) DEFAULT NULL,`Retailer` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `store_formats`
ADD PRIMARY KEY (`id`),ADD UNIQUE KEY `uniqness` (`Store Nbr`,`Store Name`,`Format`),ADD KEY `Store Nbr_2` (`Store Nbr`,`Format Name`,`Store Name`);
eds_sales：
CREATE TABLE `eds_sales` (
`id` int(12) UNSIGNED NOT NULL,`Prime Item Nbr` mediumint(7) NOT NULL,`Prime Item Desc` varchar(255) NOT NULL,`Prime Size Desc` varchar(255) NOT NULL,`Variety` varchar(255) NOT NULL,`WHPK Qty` int(5) NOT NULL,`SUPPK Qty` int(5) NOT NULL,`Depot Nbr` int(5) NOT NULL,`Depot Name` varchar(50) NOT NULL,`Store Nbr` smallint(5) UNSIGNED NOT NULL,`Store Name` varchar(255) NOT NULL,`EPOS Quantity` smallint(3) NOT NULL,`EPOS Sales` decimal(13,2) NOT NULL,`Date` date NOT NULL,`Client` varchar(10) NOT NULL,`Retailer` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `eds_sales`
ADD UNIQUE KEY `uniqness` (`Prime Item Nbr`,`Prime Item Desc`,`Prime Size Desc`,`Variety`,`WHPK Qty`,`SUPPK Qty`,`Depot Nbr`,`Depot Name`,`Store Nbr`,`Date`,`Client`) USING BTREE,ADD KEY `Store Nbr` (`Store Nbr`),ADD KEY `Prime Item Nbr_2` (`Prime Item Nbr`,`Date`),ADD KEY `id` (`id`) USING BTREE,ADD KEY `Store Nbr_2` (`Prime Item Nbr`,`Client`,`Retailer`) USING BTREE,ADD KEY `Client` (`Client`,ADD KEY `Date` (`Date`,`Retailer`);
item_codes：
CREATE TABLE `item_codes` (
`id` int(12) NOT NULL,`Item Number` varchar(30) CHARACTER SET latin1 NOT NULL,`Customer Item` mediumint(7) NOT NULL,`Description` varchar(255) CHARACTER SET latin1 NOT NULL,`Status` varchar(15) CHARACTER SET latin1 NOT NULL,`Customer` varchar(30) CHARACTER SET latin1 NOT NULL,`Sort Name` varchar(255) CHARACTER SET latin1 NOT NULL,`EquidataCustomer` varchar(30) CHARACTER SET latin1 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `item_codes`
ADD PRIMARY KEY (`id`),ADD UNIQUE KEY `uniq` (`Item Number`,`Customer Item`,`Customer`,`EquidataCustomer`),ADD KEY `Item Number_2` (`Item Number`,`Sort Name`,ADD KEY `Customer Item` (`Customer Item`,`Item Number`,ADD KEY `Customer Item_2` (`Customer Item`,`EquidataCustomer`);
所以我的问题：
正如您所看到的,我正在加入3个表,我正在按商店格式查找销售日期.我一直在尝试不同类型的连接,或者例如,不是将销售加入item_codes和store_formats,而是将store_formats连接到其他连接,但结果相同.我也使用IN传递一些变量数组,因为它们是由应用程序中的选择框提供的.
>加入这些表格的最佳方式
>建议每张桌子的最佳指数
>为什么我要获得临时表？是因为小组的？有解决方法吗？
>如果需要临时表,还是要加速这个创建吗？ (我已经拥有8个磁盘的raid中的数据文件夹,但仍然很慢.
>当然,欢迎任何建议的替代方案
更新：使用评论中的一些建议更新了我的表格
更新：修改我的f以提高性能(我的RAM为8GB,2核,/ data / tmp在8驱动器raid上,与数据相同)
tmpdir = /dev/shm/:/data/tmp:/tmp
lc-messages-dir = /usr/share/MysqL
skip-external-locking
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
query_cache_type=1
