Re: Exceptions with Partition Pruning
I've changed the partition key to search_query_id instead of search_agent_id for this example to show that performance deteriorate with partitioned table versus having individual index on regular table.
Example
==========
Table with search_query_id as regular index
CREATE TABLE `search_agent_3m_qry` (
`search_agent_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`search_query_id` bigint(20) unsigned NOT NULL,
`user_id` bigint(20) unsigned NOT NULL,
`company_id` bigint(20) unsigned NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`initialized` tinyint(1) NOT NULL DEFAULT '0',
`last_updated` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`search_agent_id`),
KEY `search_query_id` (`search_query_id`),
KEY `company_id` (`company_id`),
KEY `user_id` (`user_id`),
KEY `active` (`active`),
KEY `initialized` (`initialized`)
) ENGINE=InnoDB AUTO_INCREMENT=3050001 DEFAULT CHARSET=utf8
Response Time using above table with 3 Million records
=========================================================
Response Time Queries
0.000912 select count(*) from search_agent_3m_qry where search_agent_id =790 and search_query_id=790
0.000533 select count(*) from search_agent_3m_qry where search_agent_id =2586 and search_query_id=2586
0.001213 select count(*) from search_agent_3m_qry where search_agent_id in (4567,8987,3423) and search_query_id in (4567,8987,3423)
0.000831 select count(*) from search_agent_3m_qry where search_agent_id in (1234,987,6765,4354) and search_query_id in (1234,987,6765,4354)
0.000509 select count(*) from search_agent_3m_qry where search_agent_id =6936 and search_query_id=6936
0.000898 select count(*) from search_agent_3m_qry where search_agent_id in (6326,2585,4575,9595) and search_query_id in (6326,2585,4575,9595)
Table with partition key as composite primary key
=================================================
CREATE TABLE `search_agent_3m_comp` (
`search_agent_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`search_query_id` bigint(20) unsigned NOT NULL,
`user_id` bigint(20) unsigned NOT NULL,
`company_id` bigint(20) unsigned NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`initialized` tinyint(1) NOT NULL DEFAULT '0',
`last_updated` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`search_agent_id`,`search_query_id`),
KEY `company_id` (`company_id`),
KEY `user_id` (`user_id`),
KEY `active` (`active`),
KEY `initialized` (`initialized`)
) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (search_query_id)
PARTITIONS 32 */
Response of queries using above table
===================================================
Response Time Queries
0.001795 select count(*) from search_agent_3m_comp where search_agent_id =790 and search_query_id=790
0.00104 select count(*) from search_agent_3m_comp where search_agent_id =2586 and search_query_id=2586
0.001736 select count(*) from search_agent_3m_comp where search_agent_id in (4567,8987,3423) and search_query_id in (4567,8987,3423)
0.001842 select count(*) from search_agent_3m_comp where search_agent_id in (1234,987,6765,4354) and search_query_id in (1234,987,6765,4354)
0.000921 select count(*) from search_agent_3m_comp where search_agent_id =6936 and search_query_id=6936
0.00241 select count(*) from search_agent_3m_comp where search_agent_id in (6326,2585,4575,9595) and search_query_id in (6326,2585,4575,9595)
Regarding my routine case , below is the table structure using 2000 parameter values, & this does show improvement over a regular table. Although, the improvement would be in milli seconds or even less. The data is evenly distributed across 32 partitions
CREATE TABLE `search_agent_3m_32` (
`search_agent_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`search_query_id` bigint(20) unsigned NOT NULL,
`user_id` bigint(20) unsigned NOT NULL,
`company_id` bigint(20) unsigned NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`initialized` tinyint(1) NOT NULL DEFAULT '0',
`last_updated` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`search_agent_id`),
KEY `company_id` (`company_id`),
KEY `user_id` (`user_id`),
KEY `active` (`active`),
KEY `initialized` (`initialized`)
) ENGINE=InnoDB AUTO_INCREMENT=6000001 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (search_agent_id)
PARTITIONS 32 */
Here is the repsonse time comparision with this case
Response Time with partitioned table with 2000 values in the WHERE clause with search_agent_id
0.138785 Seconds
Response Time with non-partitioned table with 2000 values in WHERE clause with search_agent_id
0.244869 Seconds