Hourly partitioning a MySQL table based on time field
<p>I have a table with the following fields in it.</p>

<pre><code>CREATE TABLE log (
 `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `User` varchar(100) DEFAULT NULL,
 `RcvdDate` date NOT NULL,
 `RcvdTime` time NOT NULL,
 `Act` text NOT NULL,
 `MID` text NOT NULL,
 `S` varchar(100) NOT NULL,
 `DD` int(11) NOT NULL,
 `QE` varchar(100) NOT NULL,
 `DP` int(11) NOT NULL,
 `IN` text,
 `SPO` int(11) NOT NULL,
 `NOS` varchar(100) DEFAULT NULL
 PRIMARY KEY (`ID`),
 KEY `RcvdDate` (`RcvdDate`),
 KEY `RcvdTime` (`RcvdTime`),
 UNIQUE KEY `ReceivedAt` (`RcvdTime`,`SP`,`DP`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
</code></pre>

<p>Now I want to create hourly partitions. So there will be 24 partitions in it. The main reason is that my application creates a log table per day and in log table there are around 36000000 records. So make searching faster I want to create the partition per hour. Also apart from partitioning are there any optimization technique?</p>

<p>The data will be searched mostly using Date / Time / user Fields.</p>