Is it possible to partition a view, like table in MySQL?
<p>I have created view from UNION ALL clause of around 10 tables and want to apply some search queries by date range on it. But as number of records increases it takes longer to execute the query. Right now the view is having 2 billion rows.</p>

<p>Table structure is like:</p>

<pre><code>CREATE TABLE IF NOT EXISTS `tbl_queue_stats_0716` (
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `server_id` int(11) NOT NULL, 
    `uniqueid` varchar(100) DEFAULT NULL, 
    `queue_datetime` datetime NOT NULL, 
    `queue_timestamp` varchar(100) NULL, 
    `qname_id` int(11) NOT NULL, 
    `qagent_id` int(11) NOT NULL, 
    `qevent_id` int(11) NOT NULL, 
    `info1` varchar(100) DEFAULT NULL, 
    `info2` varchar(100) DEFAULT NULL, 
    `info3` varchar(100) DEFAULT NULL, 
    `info4` varchar(100) DEFAULT NULL, 
    `info5` varchar(100) DEFAULT NULL, 
    PRIMARY KEY (`id`)
);
</code></pre>

<p>Tables are created on monthly basis , so there can be tables like tbl_queue_stats_0616, tbl_queue_stats_0516, tbl_queue_stats_0416...</p>

<p>And I want to apply search query on multiple tables if date range required to search from 2 or more months.</p>

<p>Search query is look like:</p>

<pre><code>select  server_id,server_name,queue_id,queue_name,qevent_id,event,
        count(id) as cnt,sum(info1) as info1, sum(info2) as info2,
        sum(info3) as info3, sum(info4) as info4, sum(info5) as info5,
        max(cast(info2 AS SIGNED)) as max_info2,
        max(cast(info3 AS SIGNED)) as max_info3
   from
      ( SELECT  a.server_id as server_id,e.server_name as server_name,
                a.id,a.`queue_datetime`, b.agent, a.qname_id as queue_id ,
               c.queue as queue_name,d.event,a.qevent_id,a.info1,a.info2,
               a.info3,a.info4,a.info5
            FROM  view_queue_stats a,tbl_qagent b, tbl_qname c, tbl_qevent d,
                tbl_server e
            WHERE  a.qagent_id=b.id
              AND  a.qname_id=c.id
              AND  a.qevent_id=d.id
              AND  a.server_id=e.id
              AND  DATE(a.queue_datetime) between '" . $start_date .
                                           "' AND '" . $end_date . "'
              AND  a.server_id IN ($server_name) 
      )as total
    GROUP BY  qevent_id,queue_id,server_id
    ORDER BY  length(server_name), server_name,queue_id,qevent_id. 
</code></pre>

<p>I think search through partitioned view can execute my query faster. To achieve this I applied partition related parameters to create view but not succeeded.</p>

<p>Below is Output of SHOW CREATE VIEW view_queue_stats; </p>

<pre><code>CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
       VIEW `view_queue_stats`
       AS    select  `tbl_queue_stats_0116`.`id` AS `id`,
       `tbl_queue_stats_0116`.`server_id` AS `server_id`,
       `tbl_queue_stats_0116`.`uniqueid` AS `uniqueid`,
       `tbl_queue_stats_0116`.`queue_datetime` AS `queue_datetime`,
       `tbl_queue_stats_0116`.`queue_timestamp` AS `queue_timestamp`,
       `tbl_queue_stats_0116`.`qname_id` AS `qname_id`,
       `tbl_queue_stats_0116`.`qagent_id` AS `qagent_id`,
       `tbl_queue_stats_0116`.`qevent_id` AS `qevent_id`,
       `tbl_queue_stats_0116`.`info1` AS `info1`,
       `tbl_queue_stats_0116`.`info2` AS `info2`,
       `tbl_queue_stats_0116`.`info3` AS `info3`,
       `tbl_queue_stats_0116`.`info4` AS `info4`,
       `tbl_queue_stats_0116`.`info5` AS `info5`
    from  `tbl_queue_stats_0116`
    union  all 
select  `tbl_queue_stats_0216`.`id` AS `id`,
       `tbl_queue_stats_0216`.`server_id` AS `server_id`,
       `tbl_queue_stats_0216`.`uniqueid` AS `uniqueid`,
       `tbl_queue_stats_0216`.`queue_datetime` AS `queue_datetime`,
       `tbl_queue_stats_0216`.`queue_timestamp` AS `queue_timestamp`,
       `tbl_queue_stats_0216`.`qname_id` AS `qname_id`,
       `tbl_queue_stats_0216`.`qagent_id` AS `qagent_id`,
       `tbl_queue_stats_0216`.`qevent_id` AS `qevent_id`,
       `tbl_queue_stats_0216`.`info1` AS `info1`,
       `tbl_queue_stats_0216`.`info2` AS `info2`,
       `tbl_queue_stats_0216`.`info3` AS `info3`,
       `tbl_queue_stats_0216`.`info4` AS `info4`,
       `tbl_queue_stats_0216`.`info5` AS `info5`
    from  `tbl_queue_stats_0216`
    union  all
    ...

| utf8                 | utf8_general_ci      |
</code></pre>

<p>So, Is there any way to partition a view?</p>