3

Congratulations. I think you found a case where partitioning can't be made to be even as fast as non-partitioning. WHERE user_id = 1234567 ORDER BY my_id DESC LIMIT 10; Needs INDEX(user_id, my_id) in that order, and without partitioning. Thus, it would touch 10 rows and quit. With the partitioning you have, it must check each partition, gather the row(s)...


3

The maximum number of partitions allowed in MySQL 8 for InnoDB tables is 8192 (from https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html). This includes subpartitions. In general, partitions do not on their own improve performance, but there are a some scenarios where they can help: Secondary indexes are local to the partition, so if you ...


2

Dropping the index is not required, and is likely a coding oversight in the wizard output. It appears you've not just blindly ran the generated code; this is a Good Thing™, and is why I much prefer to write my own T-SQL statements by hand. The procedure for modifying a clustered primary key to a non-clustered primary key, then adding a clustered index to a ...


1

Use the proper GRANULARITY option. CREATE TABLE quarterly_regional_sales (deptno number, item_no varchar2(20), txn_date date, txn_amount number, state varchar2(2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY')) (SUBPARTITION ...


1

When in doubt, read the fine manual, which says about partition bounds: and partition_bound_spec is: IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) | FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) ...


1

I put this query together for you that should show you what you want. You can add additional where clauses to trim it further if you need to. ;WITH CTE_PartCount AS ( SELECT P.object_id , P.index_id , COUNT(P.partition_number) AS PartitionCount FROM sys.partitions AS P GROUP BY P.object_id , P.index_id ) , ...


Only top voted, non community-wiki answers of a minimum length are eligible