时间戳

首页 > 解决方案 > mysql force index for subquery

mysql - mysql force index for subquery

问题描述

in this query we only talk about the ability to set the index JOIN for the subquery. Don't concern about the performance of the query 

SELECT cate.id, cate.name as category_name, b.id book_id, b.name book_name
FROM categories as cate
LEFT JOIN (select * from books where books.name='some pets 1') as b
ON b.category_id = cate.id;

enter image description here

I would like to reduce rows of table books by using sub-query to create temporary table 'b', then try to force join index for 'b' but it didn't work ("force index (category_id)"). My draft code:

SELECT cate.id, cate.name as category_name, b.id book_id, b.name book_name
FROM categories as cate
LEFT JOIN (select * from books force index (idx_books_name) where books.name='some pets 1') b force index (category_id)
ON b.category_id = cate.id;

It through the syntax error at "force index". Is the idea possible, you think?

标签： mysqlmysql-workbench

解决方案

您的“子查询” b 是派生表。派生表（基本上）是一个全新的表。这就是您的意图，您想创建一个小型临时表b。但是这个新表无法访问原始索引，因为它是一个新表。这就是您收到错误消息的原因。

想象一下 MySQL 需要如何使用类别索引。该索引本身没有改变，因此它仍将包含原始表的所有行的条目。因此，您将使用此索引category_id在整个表中查找具有该记录的记录（正如该索引所做的那样），然后以某种方式需要检查该条目是否在您的派生表中。由于派生表实际上可以包含任何内容，即使没有对基表的任何引用，这些都不是“以某种方式”执行此操作的通用方法。

在您的情况下，这可以通过重新评估条件来完成where books.name='some pets 1'，但这意味着您将失去从idx_books_name首先使用索引和派生表获得的任何优势，因此它与

LEFT JOIN books b ON b.category_id = cate.id AND b.name='some pets 1');

这将使用 index books(category_id, name)，按照您的意图使用索引。

以您的方式使用派生表基本上已经是一种优化，它希望阻止 MySQL 以通常的方式进行连接（尽管允许 MySQL 将您的条件合并回外部查询）。如果您有特殊情况，您将执行此操作，例如，如果只有很小比例的行符合此条件，或者您没有有用的索引category_id。

MySQL可能决定在category_id. _ 您（当前）无法通过优化器提示强制生成这些索引，值得注意的是，小表上的索引可能没有您想象的那么有用。索引以对数方式扩展，因此虽然对大表的影响很大，但对于小表，生成此类索引的开销可能大于收益。

推荐阅读