制作这个MySQL数据库最有效的方法是什么？

Question

好的，我有一个数据库，其中有一个用于存储分类帖子的表，每个帖子属于不同的城市。出于本示例的目的，我们将此表称为 posts。该表有列：

id        (INT, +AI),
cityid    (TEXT),
postcat   (TEXT), 
user      (TEXT), 
datatime  (DATETIME), 
title     (TEXT), 
desc      (TEXT), 
location  (TEXT)

该数据的一个示例是：

'12039',
'fayetteville-nc',
'[email protected]',
'December 28th, 2010 - 11:55 PM',
'post title',
'post description',
'spring lake'

id是自动递增的，cityid是文本格式（这是我认为一旦数据库很大）...

最初我计划为每个城市建立一个不同的表，现在由于用户必须可以选择在多个城市中搜索和发布，我想我需要将它们全部放在一个表中。当我每张桌子有一个城市时，一切都很完美，我可以：

SELECT       *
FROM         `posts`
WHERE        MATCH (`title`, `desc`, `location`)
    AGAINST ('searchtext' IN BOOLEAN MODE)
  AND        `postcat` LIKE 'searchcatagory'

但是当我尝试一次搜索多个城市，或者列出所有用户帖子供他们删除或编辑时，我遇到了问题。

所以看起来我必须有一个包含所有帖子的表，并且还要匹配另一个全文字段：cityid。我猜我需要全文，因为如果用户选择整个州，并且我的 cityid 是“fayetteville-nc”，我需要将 cityid 与“-nc”进行匹配“这只是一个假设，我会喜欢另一种方式。该数据库可以在 6 个月内轻松达到超过 100 万行，并且针对 4 列的全文搜索可能会很慢。

我的问题是，有没有更好的方法可以更有效地做到这一点？现在数据库里什么都没有了，除了我写的一些测试帖子。所以如果有必要我可以完全重新设计表结构。我愿意接受任何和所有建议，即使这只是执行查询的更有效方式。

Answer 1

是的，一张表包含所有帖子听起来很明智。 posts 表有一个 city_id 也是正常的设计，指的是城市表中的 id。每个城市也会有一个 state_id，引用州表中的 id，类似地，每个州都会有一个country_id，引用国家/地区表中的 id。因此，您可以这样写：

SELECT $columns
FROM posts JOIN city ON city.id = posts.city_id
WHERE city.tag = 'fayetteville-nc'

将城市放入单独的表中后，预先进行 city-to-city_id 解析可能更有意义。例如，如果您从下拉列表中选择了一个城市，这种情况就会很自然地发生。但如果您要在搜索字段中输入自由文本，您可能需要采取不同的方式。

您还可以搜索给定州（或一组州）中的所有帖子，如下所示：

SELECT $columns
FROM posts
     JOIN city ON city.id = posts.city_id
     JOIN state ON state.id = city.state_id
WHERE state.tag = 'NC'

如果您想要更花哨或更国际化，您可能需要一种更灵活的方式将位置安排到层次结构中（例如，您可能想要城市区、县、跨国地区、国内地区（中西部、东海岸等）），但现在保持轻松:)