简单的查询很慢，由于订购
问题描述：
请任何人都可以帮助下面的查询？ （180352行）简单的查询很慢，由于订购
SELECT COUNT(p.stock_id) AS num_products,
p.master_photo, p.product_photo, p.stock_id, p.master, p.title, p.price, p.stock_level, p.on_order, p.location, p.supplier, p.category, p.sub_category, p.reorder
FROM products AS p
WHERE p.sub_category != 'Subscriptions'
GROUP BY p.master
ORDER BY p.stock_id ASC
LIMIT 0, 20
它在6秒钟内运行。
当我通过它运行的在0.0023秒删除订单。
而且还当我通过删除该组相同。
的STOCK_ID（唯一）和sub_category进行索引。
我不能想到另一种方式来接近这样的查询，因为它是至关重要的，我是由主人分组来获得产品变化的数量，也是至关重要的，他们可以订购（不一定由stock_id，但这是默认）。
谢谢
按照要求通过e4c5下面是有顺序的解释由
id: 1
select_type: SIMPLE
table: p
type: range
possible_keys: sub_category
key: sub_category
key_len: 52
ref: NULL
rows: 181691
Extra: Using where; Using temporary; Using filesort
，然后没有ORDER BY
id: 1
select_type: SIMPLE
table: p
type: index
possible_keys: sub_category
key: master
key_len: 52
ref: NULL
rows: 21
Extra: Using where
结果
然后下面是创造e表
CREATE TABLE IF NOT EXISTS `products` (
`stock_id` varchar(50) NOT NULL,
`conv_stock_id` varchar(100) NOT NULL,
`conv_quantity` decimal(10,2) NOT NULL,
`master` varchar(50) NOT NULL,
`master_photo` varchar(255) NOT NULL,
`free_guide_photo` varchar(255) NOT NULL,
`product_var_photo` varchar(255) NOT NULL,
`master_title` varchar(255) NOT NULL,
`master_slug` varchar(255) NOT NULL,
`master_page_title` varchar(255) NOT NULL,
`product_photo` varchar(255) NOT NULL,
`original_product_photo` varchar(255) NOT NULL,
`title` varchar(255) NOT NULL,
`orig_title` varchar(255) NOT NULL,
`page_title` varchar(255) NOT NULL,
`description` longtext NOT NULL,
`slug` varchar(255) NOT NULL,
`custom_url` varchar(255) NOT NULL,
`location` varchar(255) NOT NULL,
`supplier` varchar(50) NOT NULL,
`supplier_stock_id` varchar(50) NOT NULL,
`supplier_discount` int(11) NOT NULL,
`category` varchar(50) NOT NULL,
`sub_category` varchar(50) NOT NULL,
`cost_price` decimal(10,2) NOT NULL,
`discount_cost_price` decimal(10,2) NOT NULL,
`price` decimal(10,2) NOT NULL,
`sale_price` decimal(10,2) NOT NULL,
`sale_price_startdate` date NOT NULL,
`sale_price_enddate` date NOT NULL,
`orig_price_trail` int(3) NOT NULL,
`price_trail` varchar(50) NOT NULL,
`price_rule` int(1) NOT NULL,
`pack_size` int(11) NOT NULL,
`parcel_size` int(1) NOT NULL,
`packaging_rule` int(11) NOT NULL,
`cut_tear` int(1) NOT NULL,
`oversized_parcel` int(1) NOT NULL,
`print_label` int(1) NOT NULL,
`stock_level` decimal(10,1) NOT NULL,
`stock_level_group` varchar(50) NOT NULL,
`stock_level_increment` decimal(10,2) NOT NULL,
`stock_check_date` datetime NOT NULL,
`reorder` int(1) NOT NULL,
`reorder_level` decimal(10,1) NOT NULL,
`reorder_quantity` decimal(10,1) NOT NULL,
`reorder_attempts` int(1) NOT NULL,
`unit_size` decimal(10,1) NOT NULL,
`on_order` decimal(10,1) NOT NULL,
`date_ordered` datetime NOT NULL,
`back_order` decimal(10,1) NOT NULL,
`uom` decimal(10,1) NOT NULL,
`uom_value` varchar(100) NOT NULL,
`stock_estimate` int(1) NOT NULL,
`due_date` datetime NOT NULL,
`quantity` varchar(255) NOT NULL,
`colour` varchar(255) NOT NULL,
`colour_family` varchar(255) NOT NULL,
`type` varchar(255) NOT NULL,
`style` varchar(255) NOT NULL,
`pattern` varchar(255) NOT NULL,
`shape` varchar(255) NOT NULL,
`design` varchar(255) NOT NULL,
`fibre` varchar(255) NOT NULL,
`material` varchar(255) NOT NULL,
`pattern_for` varchar(255) NOT NULL,
`difficulty` varchar(255) NOT NULL,
`fabric_count` varchar(255) NOT NULL,
`yarn_thickness` varchar(255) NOT NULL,
`suggested_needle_size` varchar(255) NOT NULL,
`tension` varchar(255) NOT NULL,
`collections` varchar(255) NOT NULL,
`product_features` varchar(255) NOT NULL,
`size` varchar(255) NOT NULL,
`actual_size` varchar(255) NOT NULL,
`length` varchar(255) NOT NULL,
`width` varchar(255) NOT NULL,
`weight` varchar(255) NOT NULL,
`weight_gsm` varchar(255) NOT NULL,
`brand` varchar(255) NOT NULL,
`designer` varchar(255) NOT NULL,
`composition` varchar(255) NOT NULL,
`washing_instructions` varchar(255) NOT NULL,
`matching_thread` varchar(50) NOT NULL,
`sample` varchar(50) NOT NULL,
`fat_quarter` varchar(50) NOT NULL,
`barcode` varchar(13) NOT NULL,
`list_international` int(1) NOT NULL,
`token` varchar(50) NOT NULL,
`create_sample` int(1) NOT NULL,
`create_fatquarter` int(1) NOT NULL,
`create_listing_type` int(1) NOT NULL,
`create_listing_size` int(11) NOT NULL,
`create_listing_price` decimal(10,2) NOT NULL,
`create_listing_price_rule` int(11) NOT NULL,
`create_listing_sale_price` decimal(10,2) NOT NULL,
`create_listing_parcelsize` int(1) NOT NULL,
`create_listing_barcode` varchar(13) NOT NULL,
`auto_listing` int(1) NOT NULL,
`custom_bridal` int(1) NOT NULL,
`pickwave_assign` int(1) NOT NULL,
`kit_product` int(11) NOT NULL,
`fatquarter_product` int(1) NOT NULL,
`sample_product` int(1) NOT NULL,
`grouped_product` int(1) NOT NULL,
`grouped_product_quantity` decimal(10,1) NOT NULL,
`multiple_product` int(1) NOT NULL,
`freepost_product` int(1) NOT NULL,
`status` int(1) NOT NULL,
`update_stock_level` int(1) NOT NULL,
`force_product_photo` int(1) NOT NULL,
`created_master_photo` int(1) NOT NULL,
`force_master_photo` int(1) NOT NULL,
`created_free_guide_photo` int(1) NOT NULL,
`force_free_guide_photo` int(1) NOT NULL,
`created_product_var_photo` int(1) NOT NULL,
`force_product_var_photo` int(1) NOT NULL,
`force_additional_photo` int(1) NOT NULL,
`created_price_levelling` int(1) NOT NULL,
`created_grouped_product` int(1) NOT NULL,
`updated_stock_level` int(1) NOT NULL,
`create_multiple_listing` int(1) NOT NULL,
`create_freepost_listing` int(1) NOT NULL,
`create_freeguide_info` int(1) NOT NULL,
`created_by` int(11) NOT NULL,
`date_created` datetime NOT NULL,
UNIQUE KEY `stock_id` (`stock_id`),
KEY `token` (`token`),
KEY `title` (`title`),
KEY `stock_level_group` (`stock_level_group`),
KEY `sub_category` (`sub_category`),
KEY `stock_level` (`stock_level`),
KEY `category` (`category`),
KEY `conv_stock_id` (`conv_stock_id`),
KEY `conv_quantity` (`conv_quantity`),
KEY `created_price_levelling` (`created_price_levelling`),
KEY `master` (`master`),
KEY `colour` (`colour`),
KEY `auto_listing` (`auto_listing`),
KEY `multiple_product` (`multiple_product`),
KEY `status` (`status`),
KEY `ebay_master` (`ebay_master`),
KEY `parcel_size` (`parcel_size`),
KEY `grouped_product` (`grouped_product`),
KEY `sample_product` (`sample_product`),
KEY `fatquarter_product` (`fatquarter_product`),
KEY `created_grouped_product` (`created_grouped_product`),
KEY `price` (`price`),
KEY `freepost_product` (`freepost_product`),
KEY `master_title` (`master_title`),
KEY `c_sub_category_master` (`sub_category`,`master`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
答
尝试在产品表中的stock_id上添加索引...应该有所帮助。
+0
嗨StuartMc，stock_id上有一个索引（这是唯一的列） –
答
您没有提供从解释的输出，但是根据您的查询它似乎ORDER BY力量全表扫描。这会使查询非常缓慢。
当您不使用ORDER BY时，数据库将读取前20个值的结果（可能有相当多的值），并将它们组合在一起并返回结果。
当您stock_id命令将整个表需要在待四下寻找这master s的与最低值相关stock_id小号
它也许可以提高sub_category,master与综合指数的表现不过的结论不能除非你分享你的SHOW CREATE TABLES，EXPLAIN输出。
根据您的CREATE TABLE语句更新 ，我看你的数据库不被标准化。例如，为什么我觉得以下列应该放在自己的表中？
supplier varchar(50) NOT NULL,
supplier_stock_id varchar(50) NOT NULL,
supplier_discount int(11) NOT NULL,
您应该只有在你的产品表supplier_stock_id（外键供应商表）。有类似的一组列，真的应该搬出去。
当你这样做的时候，你可以在这个表上创建更精简和更清晰的索引。但这并不是所有的桌子都变得更窄。这又意味着全表扫描的最坏情况实际上变得快得多。
我也注意到表没有主键。这是一个很大的禁忌。 stock_id如果是数字，则应该是主键。如果它不是数字，它可能会成为主键的最佳人选，但这是你需要决定的。
+0
嗨e4c5，我试过了一个复合索引sub_category，主，它仍然需要6秒钟。我也分享了show create tables并在上面的帖子中解释。感谢您的时间。 –
+0
不幸的是，这些都不会让你走得更远。你必须规范你的数据 – e4c5
'GROUP BY'应该和'ORDER BY'一样慢。 –
为什么你需要所有的行？真的，你需要180352行同时？分页它可能会帮助你。 – Grommy
你尝试在主人上添加索引吗？你正在对它进行分组 – spiderman