1

I have a table partitioned by values of a KPI field. There are some kpi with a large amount of rows some with small numbers. Partitioning by KPI name (string) keeps query performance good.

The data source occasionally will introduce a new KpI which breaks the loader. The file being uploaded that contains the new kpi value will fail.

I want to add a default partition to cover these conditions.

After searching around I found and tried this command

alter table X add partition (partition pDefault values in (DEFAULT));

MySQL workbench doesn’t like this command highlighting the second to last ‘)’ saying it is not valid at this position, expecting ‘(‘.

I’ve looked through MySQL help index and googled for an hour but can’t find what is wrong with this or another way to do what I want to do.

Ideas?

1
  • Please provide SHOW CREATE TABLE.
    – Rick James
    Dec 7, 2023 at 20:52

2 Answers 2

2

You must list explicit values when using LIST partitioning. You can't make a "default" or "catch-all" partition for values you didn't list.

This is documented:

https://dev.mysql.com/doc/refman/8.0/en/partitioning-list.html says:

Unlike the case with RANGE partitioning, there is no “catch-all” such as MAXVALUE; all expected values for the partitioning expression should be covered in PARTITION ... VALUES IN (...) clauses. An INSERT statement containing an unmatched partitioning column value fails with an error.

1
  • Thanks Bill. As mentioned in my other comment we got what we needed out list partitioning. My team does control new kpi values and they are few. I'll just manage it manually if/when it happens. Dec 9, 2023 at 0:07
2

PARTITIONing rarely helps with performance. If your queries are usually

WHERE ...
  AND kpi = 'asdf'

then performance may be improved by getting rid of partitioning and adding composite index(es) that start with kpi.

Please provide SHOW CREATE TABLE and some of the main queries. With them, we can provide more specific advice.

As mentioned by Bill, PARTITION BY LIST is a maintenance nightmare if the list changes. I see no good use for it.

3
  • A good use for list partitioning is if you want to drop a specific partition quickly. I agree that using partitioning for performance is no better than using indexes in most cases, and indexes are more flexible. Dec 7, 2023 at 22:23
  • I was using indexes but couldn't get to the performance we wanted. When I partitioned by list the performance improved dramatically. ¯_(ツ)_/¯ Dec 9, 2023 at 0:05
  • @user22668153 - Did you have INDEX(kpi, something_else)? What was the WHERE clause in the query?
    – Rick James
    Dec 9, 2023 at 0:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.