在大表中快速查找MYSQL表 [英] Fast MYSQL table lookup in a large table

问题描述

大家好，


我还在努力学习MYSQL的来龙去脉。我在我们的服务器上有一个大型餐馆数据库，我希望能够交叉参考菜肴并显示一个页面，上面写着加利福尼亚州洛杉矶有15家意大利餐馆，例如洛杉矶有12家墨西哥餐馆。美食领域是个性。例如，餐馆名称可能是Charlie's Pizzeria，而烹饪领域可能有披萨，意大利面，意大利。还有一个单独的美食桌。当前的PHP代码正在烹饪表上做一个select语句，在那里的每个记录对着餐馆桌子，在那里城市& state是用户选择的，以及餐厅表中的菜肴字段与菜肴表中的当前记录相似。然后它继续到菜表中的下一个记录并再次完成。


只使用PHP，MYSQL和魔法，有没有人有任何想法，快速选择记录的最佳方法是什么？我尝试为记录中的每个菜肴创建一个单独的字段，并将它们编入索引，但MYSQL显然没有那么多的索引。无论如何，这将是一个重新设计，所以任何想法都值得赞赏。即使我必须重新设计数据库，我也希望以正确的方式进行。


谢谢！

Hi all,

I''m still trying to learn the ins and outs of MYSQL. I have a large database of restaurants sitting on our server, and I want to be able to cross reference the cuisines and display a page that says there are 15 Italian restaurants in Los Angeles California, 12 Mexican places in L.A., for example. the cuisine fields are character. For example, the restaurant name might be Charlie''s Pizzeria, and the cuisine field could have "pizza, pasta, Italian". There is a separate table for cuisines. The current PHP code is doing a select statement on the cuisine table, going through every record in there against the restaurant table where the city & state are what the user selected, and where the cuisine field in the restaurant table is LIKE the current record from the cuisine table. Then it goes on to the next record in the cuisine table and does that all again.

Using nothing but PHP, MYSQL, and magic, does anyone have any ideas what the best approach would be to select the records out quickly? I tried creating a separate field for every cuisine in the record, and indexing them, but MYSQL can''t have that many indexes evidentially. This will be a re-design anyway, so any ideas are appreciated. Even if I have to re-design the database, I''d like to do it the right way.

Thanks!

推荐答案

要获得快速结果，您必须重新设计数据库结构。一个解决方案可以创建一个桌子（你可以说中间的桌子，加入餐馆和美食）restaurant_cuisine包含2个字段

restaurant_id

cuisine_id


在restaurant_id和cuisine_id上​​创建综合索引。


通过这种方式，您可以添加许多美食。

To get quick results you have to redesign the DB structure. A solution can be creating a table (you can say a middle table, joining restaurants and cuisines) restaurant_cuisine containing 2 fields
restaurant_id
cuisine_id

create a combined index on restaurant_id and cuisine_id.

In this way, you''ll be able to add many cuisines as you can.

Heya，cooldht。欢迎来到TSDN！

Heya, cooldht. Welcome to TSDN!

我尝试为记录中的每种菜肴创建一个单独的字段，并将它们编入索引，但是MYSQL不能有那么多指数显然。

I tried creating a separate field for every cuisine in the record, and indexing them, but MYSQL can''t have that many indexes evidentially.

你能解释一下你的意思吗？我之前有过超过20个索引的表。

Can you explain what you mean by this? I''ve had tables with over 20 indexes before.

Hi MW，感谢您的建议！我会把它放在重新设计的可能性列表上。


PB，我想有一个限制。我收到错误＃1069 - 指定的密钥太多。允许最多32个密钥。不幸的是，大约有90个美食区。


谢谢！

Hi MW, thanks for the advice! I''ll put that on the list of possibilities for the re-design.

PB, I guess there is a limit. I receive the error "#1069 - Too many keys specified. Max 32 keys allowed". There are somewhere around 90 cuisine fields unfortunately.

Thanks!

这篇关于在大表中快速查找MYSQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！