0

I am trying to partition my table so I can narrow down the record so accessing data won't take as long as it is taking now.

this table that I want to partition has 2 key fields (1) 'tigger_on' which is a datetime field and I use this a lot as look up key. (2) 'status' which has 3 values 1=active,2=completed,0=purged.

I am not sure what is the best way to partition this table so that it will be easier to access for the select statement?

First I have one question when I do a partition does this create a new table so I will have to alter my queries? or is it something like an index where it narrow down the search so the look up data will be less?

Second How can I alter my existing table to add this partition? should I partition base on date range or my status or can I do it by both?

I never done partition before so I am clueless on how its done.

Note this table has 5 million records and I have added index. So I am looking for solution beyond indexing at this point.

Jaylen
  • 39,043
  • 40
  • 128
  • 221
  • Can you show the query you mention that accesses this table? Also any more information you can give about your access pattern would be useful, as that will really determine what the best partitioning approach might be. For example, are you typically looking up more recent records based on `tigger_on` value? – Mike Brant Mar 26 '13 at 16:10
  • @MikeBrant, Thanks for your response. I actually have posted a different post the other day about tuning my query and thanks to "Captin Payalytic" he lead me to the right path http://stackoverflow.com/questions/15512839/order-by-datetime-makes-the-query-very-slow this link show the query and more information about my table – Jaylen Mar 26 '13 at 16:22
  • Try this link once : http://dev.mysql.com/doc/refman/5.1/en/partitioning.html – georgecj11 Mar 26 '13 at 16:35
  • This answered some of my questions. Thanks for that. But I still have question, do I have to change my query after creating partition? and it looks like I would have "status" and partition and year range and a sub partition. – Jaylen Mar 26 '13 at 17:09

0 Answers0