CODEWITHSUNDEEP
mysqlpartitioning
user7461846
user7461846

Reputation:

Error while I'm trying to partitioning a table

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: 

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?

Upvotes: 0

Views: 314

Answers (1)

Rick James
Rick James

Reputation: 142298

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.

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.

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.

Upvotes: 1

Related Questions