MySQL Forums
Forum List  »  Partitioning

Re: Partitioning throwing #1064 - Constant/Random expression in (sub)partitioning function is not allowed near ') ( PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2) PARTIT' at line 2 error
Posted by: chillyroll r
Date: April 16, 2009 11:35PM

Hi Rick,

Thanks for your interest.

I guess I should have given the below details in the first place itself. But anyways, below are the complete details.

The thing is my table has around more than 16 million records and the are increasing per day, around 50K per day. This is going to happen for next several months. My web application performs a search on this table. The queries, which I am using, are taking a lot of time currently (more than 60 seconds sometimes). I am expecting major performance boost-up through partitioning (maybe less than a second). I have already have indexed the state_id column, (thinking to remove index from state and create on city). And it gave me a bit of better performance, but not yet satisfactory. I am expecting to partition my data and then perform the search. Hoping that will solve my issue.

Below is my current table structure, It has more than 16 million records now and will add 50K everyday for next several months:


CREATE TABLE `tbl_businesses` (
`business_id` int(20) NOT NULL DEFAULT '0',
`business_name` varchar(255) NOT NULL,
`business_type_id` int(20) NOT NULL,
`business_user_id` bigint(20) NOT NULL,
`address1` varchar(255) DEFAULT NULL,
`address2` varchar(255) DEFAULT NULL,
`city_id` int(20) NOT NULL,
`state_code` varchar(2) DEFAULT NULL,
`zip_code` varchar(10) DEFAULT NULL,
`business_phone` varchar(20) DEFAULT NULL,
`business_secondary_phone` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`business_tertiary_phone` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`website` varchar(255) DEFAULT NULL,
`business_info` varchar(255) DEFAULT NULL,
`latitude` double DEFAULT '0',
`longitude` double DEFAULT '0',
`created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`is_deleted` enum('Y','N') CHARACTER SET utf8 DEFAULT 'N',
`source_id` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`is_rated` char(1) CHARACTER SET utf8 DEFAULT 'N',
`source_key_id` varchar(50) CHARACTER SET utf8 NOT NULL,
`business_type_id_back` int(20) NOT NULL DEFAULT '0'
)

Below is just one of the query I am using. Currently its giving me results in 62-70 seconds :(


SELECT business_id,CONCAT(UPPER(SUBSTRING(city, 1, 1)), LOWER(SUBSTRING(city FROM 2))) AS cty from tbl_businesses WHERE city_id = '78646' AND state_code = 'CA' AND (business_name LIKE '%restaurants%' OR business_info LIKE '%restaurants%' OR business_type_id IN ('33000000','21000000','59000000'))

I think creating 4 partitions (KEY) and then sub-partitioning (4 RANGE partitions) them based business_type_id will do it for me. I have around 400 business_type_id and around 300 cities.

Hope I am not in a wrong world!

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Partitioning throwing #1064 - Constant/Random expression in (sub)partitioning function is not allowed near ') ( PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2) PARTIT' at line 2 error
3007
April 16, 2009 11:35PM


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.