使用“OR”时Mysql索引出现问题陈述
有什么方法可以为此查询创建一个功能索引并摆脱“文件排序”吗？
SELECT id, title FROM recipes use index (topcat) where
(topcat='$cid' or topcat2='$cid' or topcat3='$cid')
and approved='1' ORDER BY id DESC limit 0,10;
我创建了索引“topcat”（列：topcat1+topcat2+topcat3+approved+id），但仍然是“Using where;Using filesort”。
我可以再创建一列，例如“all_topcats”，将 topcat 数字存储在数组中 - 1,5,7，然后运行查询“... where $cid iIN ()...”。但问题是，在这种情况下，“all_topcats”列将为“varchar”，但“approved”和“id”列 - int，并且无论如何都不会使用索引。
有什么想法吗？谢谢。
Is there any way how to create an functioning index for this query and to get rid of "filesort"?
SELECT id, title FROM recipes use index (topcat) where
(topcat='$cid' or topcat2='$cid' or topcat3='$cid')
and approved='1' ORDER BY id DESC limit 0,10;
I created index "topcat" ( columns: topcat1+topcat2+topcat3+approved+id) but still ge "Using where; Using filesort".
I can create one more column, lets say, "all_topcats" to store topcat numbers in an array - 1,5,7 and then to run query "... where $cid iIN ()...". But the probem is that in this case "all_topcats" column will be "varchar" but "approved" and "id" columns - int, and index will not be used anyway.
Any ideas? Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您对索引中的列重新排序，则可能会提高该查询的性能：
了解表的外观以及为什么有这样命名的三列将很有用。如果您有一个子表来存储 topcat 值，并带有返回主表的链接，那么组织一个好的查询可能会更容易，但如果不知道为什么要这样设置，就很难知道这是否明智。
您可以发布
CREATE TABLE吗？根据用户消息进行编辑
您的表格听起来设计得不太好。下面的设计会更好：添加两个新表，
Category和Category_Recipe（交叉引用表）。Category将包含您的类别列表，Category_Recipe将包含两列，一列是Category的外键，一列是现有的外键食谱表。一行Category_Recipe是一条语句“这个菜谱属于这个类别”。然后，您将能够非常简单地编写一个查询来搜索给定类别中的食谱。您还可以将配方放入任意多个类别，而不是仅限于 3 个类别。查找“数据库规范化”和“外键”。You might improve performance for that query if you reordered the columns in the index:
It would be useful to know what the table looks like and why you have three columns named like that. It might be easier to organise a good query if you had a subsidiary table to store the topcat values, with a link back to the main table, but without knowing why you have it set up like that it's hard to know whether that would be sensible.
Can you post the
CREATE TABLE?Edit in response to user message
Your table doesn't sound like it's well-designed. The following design would be better: Add two new tables,
CategoryandCategory_Recipe(a cross-referencing table).Categorywill contain a list of your categories andCategory_Recipewill contain two columns, one a foreign key toCategoryand one a foreign key to the existingRecipetable. A row ofCategory_Recipeis a statement "this recipe is in this category". You will then be able to very simply write a query that will search for recipes in a given category. You also have the ability to put a recipe in arbitrarily many categories, rather than being limited to 3. Look up "database normalisation" and "foreign keys".