我正在尝试对表进行分区时出错

Here is my posts table:

这是我的帖子表：

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `img` varchar(255) COLLATE utf8_croatian_ci NOT NULL,
 `vid` varchar(255) COLLATE utf8_croatian_ci NOT NULL,
 `title` varchar(255) COLLATE utf8_croatian_ci NOT NULL,
 `subtitle` varchar(255) COLLATE utf8_croatian_ci NOT NULL,
 `auth` varchar(54) COLLATE utf8_croatian_ci NOT NULL,
 `story` longtext COLLATE utf8_croatian_ci NOT NULL,
 `tags` varchar(255) COLLATE utf8_croatian_ci NOT NULL,
 `status` varchar(100) COLLATE utf8_croatian_ci NOT NULL,
 `moder` varchar(50) COLLATE utf8_croatian_ci NOT NULL,
 `rec` varchar(50) COLLATE utf8_croatian_ci NOT NULL,
 `pos` varchar(50) COLLATE utf8_croatian_ci NOT NULL,
 `inde` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci

I want to make two partitions in order to improve query performances.
First partition should contain all non-archive rows.
Second partition - all archive rows.

我想制作两个分区以提高查询性能。第一个分区应包含所有非归档行。第二个分区 - 所有归档行。

ALTER TABLE posts
PARTITION BY LIST COLUMNS (status)
(
PARTITION P1 VALUES IN ('admin', 'moder', 'public', 'rec'),
PARTITION P2 VALUES IN ('archive')
);

phpmyadmin error:

phpmyadmin错误：

Static analysis:

    1 errors were found during analysis.

    Unrecognized alter operation. (near "" at position 0)
    MySQL said: 

    #1503 - A PRIMARY KEY must include all columns in the table's partitioning function

Any help?

有帮助吗？

1 个解决方案

#1

What queries are you trying to speed up? Since the only index you currently have, WHERE id=... or WHERE id BETWEEN ... AND ... are the only queries that will be fast. And the partitioning you suggest will not help much for other queries.

您想加快哪些疑问？由于您当前拥有的唯一索引，WHERE id = ...或WHERE id BETWEEN ... AND ...是唯一快速的查询。你建议的分区对其他查询没什么帮助。

You seem to have only dozens of rows; don't consider partitioning unless you expect to have at least a million rows.

你似乎只有几十行;除非您预计至少有一百万行，否则不要考虑分区。

status has only 5 values? Then make it ENUM('archive', 'admin', 'moder', 'public', 'rec') NOT NULL. That will take 1 byte instead of lots.

状态只有5个值？然后将它设为ENUM（'archive'，'admin'，'moder'，'public'，'rec'）NOT NULL。这将需要1个字节而不是批次。

If you will be querying on date and/or status and/or auth, then let's talk about indexes, especially 'composite' indexes on such. And, to achieve the "archive" split you envision, put status as the first column in the index.

如果您要查询日期和/或状态和/或auth，那么让我们谈谈索引，尤其是“复合”索引。并且，为了实现您想象的“存档”拆分，将状态作为索引中的第一列。

#1