MySQL Forums
Forum List  »  Partitioning

Re: Exceptions with Partition Pruning
Posted by: ratheesh balakrishnan
Date: June 16, 2010 05:28PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Exceptions with Partition Pruning
2049
June 16, 2010 05:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.