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
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)...
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 ...
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 ...
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'))
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
, COUNT(P.partition_number) AS PartitionCount
FROM sys.partitions AS P
GROUP BY P.object_id