这个查询应该使用什么mysql索引?

SELECT * FROM items WHERE caption LIKE 'name%' AND type = 'private' OR owner LIKE 'name%' type = 'private' ORDER BY uses DESC LIMIT 40;

Possible keys: items_caption,items_owner,items_type Key: items_uses (Got these by using the explain command)

可能的键:items_title、items_owner、items_type Key: items_use(通过使用explain命令获得这些)

It takes about 1.8 seconds to do that query and there are over million records in the table. I don't know how to make a index for this query and I've no control over the source so I can't modify the query.

执行该查询大约需要1.8秒，并且表中有超过百万条记录。我不知道如何为这个查询创建索引，而且我无法控制源，所以我无法修改查询。

3 个解决方案

#1

Since you've got at select *, you can't really do a covering index, but you could do a pair of composite indexes on (caption,type) and (owner, type).

既然已经有了select *，那么就不能做覆盖索引，但是可以在(标题、类型)和(所有者、类型)上做一对复合索引。

ALTER TABLE items ADD INDEX (caption, type);
ALTER TABLE items ADD INDEX (owner, type);

This should cut down on internal point lookups.

这将减少内部点查找。

I don't think you need fulltext search since the value you're comparing with your LIKE's do not start with a wildcard (%). Likes that compare with strings not starting with % can use traditional fast b-tree indexes. If you have a % at the start of the LIKE as in '%name' this wouldn't be the case.

我认为您不需要全文本搜索，因为您正在比较的值不是以通配符(%)开头的。比较不以%开头的字符串可以使用传统的快速b-tree索引。如果你在“%name”的开头有%，这就不是这个情况了。

Also, don't forget the conditions your searching are:

同时，不要忘记你的搜索条件:

caption LIKE 'name%' AND type = 'private'

标题如'name%'和type = 'private'

或

owner LIKE 'name%' AND type = 'private'

如'name%'和type = 'private'

So "ADD FULLTEXT(caption,owner)" won't help much if the caption clause fails and it tries to lookup by owner and type.

因此，如果标题子句失败并且试图按所有者和类型查找，那么“添加全文(标题，所有者)”不会有多大帮助。

#2

You can do fulltext indexing on 'caption' and it will increase the query time significantly.

你可以对“标题”进行全文索引，这会大大增加查询时间。

ALTER TABLE items ADD FULLTEXT(caption,owner);

Mysql v 5.6 and above support fulltext search in innodb. https://blogs.oracle.com/MySQL/entry/full_text_search_with_innodb

Mysql v5.6及以上版本支持innodb中的全文搜索。https://blogs.oracle.com/MySQL/entry/full_text_search_with_innodb

#3

You need to make a explain query first to see how is it working. We can't help you if we don't know how is the query being execute and the structure of your table. I.E, @chheplo says that you can add index on your text search

您需要首先进行一个explain查询，看看它是如何工作的。如果我们不知道查询是如何执行的，以及表的结构，我们无法帮助您。我。@chheplo说你可以在你的文本搜索上添加索引。

ALTER TABLE items ADD FULLTEXT(caption,owner);

But it will depend to your DB design, if you are using Mysql 5.6- and your engine is Innodb it wont work, you'll need engine = MyISAM, to be able to create text index, also you need to execute the explain to see if the select is also using it. However I think if you avoid the "LIKE" it will be faster, Performance of like '%Query%' vs full text search CONTAINS query

但是这取决于你的DB设计，如果你使用的是Mysql 5.6——而且你的引擎是Innodb，它不能工作，你需要engine = MyISAM，才能创建文本索引，你还需要执行explain，看看select是否也在使用它。但是我认为如果您避免了“LIKE”，那么它将会更快，比如'%Query%' vs full text search包含查询

Regards

问候

#1