How does partitioning affect indexes in MySQL

Question

I'm a newbie to MySQL so apologising in advance if the question seems silly. I have a transaction log table that is very big (contains 18M rows) and is growing at a very fast rate. The records that are over 3 months old are not used in the day-to-day flows and hence I'm looking at a way to split the table and then archive the older records. I'm looking at partitioning the table as a way to do this. My question is this:

If I partition the table based on a date column,how does that affect the indexes that are NOT based on the partitioned date column ? If there is a select query that uses one of these indexes, then will the partition containing the older records be searched as well? because that kind of defeats the whole point of partitioning for me.

Is there any other approach that you would recommend?

Thanks in advance.

Rick James · Accepted Answer · 2015-12-16 18:24:56Z

1

(a non-answer)

PARTITIONing is useful in only a few use cases. Let's see your SELECTs so we can consider whether any of them would benefit from partitioning.

If your queries are like "count the widgets for last week", then you may be looking at Data Warehouse techniques like "Summary Tables". This is quite effective, performancewise, and does not (usually) involve partitioning.

More on partitioning use cases: http://mysql.rjweb.org/doc.php/latlng

answered Dec 16, 2015 at 18:24

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How does partitioning affect indexes in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related