Re: Partitioning

[Rick James]

(Your sample data has two rows with ID=2, hence one of my questions.)

Plan A:

PRIMARY KEY(id),
INDEX(uri)

Plan B:

PRIMARY KEY(uri, id),
INDEX(id)

Either Plan will "work"; neither Plan will benefit from PARTITIONing.

"PARTITION on URI_CM field" is useless you will be have URI_CM in the WHERE clause of some queries. (And even then, it may or may not be useful.)

Pros and Cons of the two Plans:

Plan A:
* More logical
* Other secondary keys will be more efficient with Plan A because the PK is smaller.

Plan B:
* Faster for `WHERE uri='constant'` because all the 100-1000 rows will be clustered together, hence involve less I/O.

My comment about "a few milliseconds" will not apply if you fetch 1000 rows of 1000 bytes each. For such, Plan A may take 10 seconds and Plan B may take 1 second -- even with a "cold" cache. Again, these timings won't change much regardless of the table size. Again, PARTITIONing won't speed it up.

On the other hand, if something else is going on, I could be advising you wrongly. For example, if you are purging records after 30 days, then PARTITIONing is excellent at that -- but partitioning BY RANGE on the date. Or, if you have other queries, there may be a good argument to have some other set of indexes.