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
