Skip navigation links

MySQL Forums :: Partitioning :: Do I have to re write querries when Partitioning?


Advanced Search

Do I have to re write querries when Partitioning?
Posted by: john hango ()
Date: September 01, 2010 11:17PM

This is my table that I need to partition How do you partition a table like this that basically is going to have new auctions everyday and old ones every week that are no longer good? What would I use for a revolving primary key? Any Ideas would be great. I need to be able to have at least twenty million items in this table so 20000 a partition would work I think. Thanks for the help.
phpMyAdmin SQL Dump
-- version 3.2.4
--
--
-- Host: localhost
-- Generation Time: Aug 16, 2010 at 03:08 AM
-- Server version: 5.1.47
-- PHP Version: 5.2.9
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `esazzlec_ysp`
--
-- --------------------------------------------------------
--
-- Table structure for table `yspnash_auctions`
--
CREATE TABLE IF NOT EXISTS `yspnash_auctions` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`itemname` varchar(255) NOT NULL DEFAULT '',
`description` mediumblob NOT NULL,
`picpath` varchar(255) NOT NULL DEFAULT '',
`quantity` smallint(6) NOT NULL DEFAULT '0',
`auctiontype` varchar(30) NOT NULL DEFAULT '',
`bidstart` double(16,2) NOT NULL DEFAULT '0.00',
`bidend` double(16,2) NOT NULL,
`bidstart_original` double(16,2) NOT NULL,
`rp` char(2) NOT NULL DEFAULT '',
`rpvalue` double(16,2) NOT NULL DEFAULT '0.00',
`bn` char(2) NOT NULL DEFAULT '',
`bnvalue` double(16,2) NOT NULL DEFAULT '0.00',
`bi` tinyint(4) NOT NULL DEFAULT '0',
`bivalue` double(16,2) NOT NULL DEFAULT '0.00',
`duration` int(255) NOT NULL,
`country` varchar(100) NOT NULL DEFAULT '',
`zip` varchar(20) NOT NULL DEFAULT '',
`sc` char(2) NOT NULL DEFAULT '',
`scint` char(2) NOT NULL DEFAULT '',
`pm` text NOT NULL,
`main_category` int(11) NOT NULL,
`category` int(11) NOT NULL DEFAULT '0',
`active` tinyint(4) NOT NULL DEFAULT '0',
`payment_status` varchar(20) NOT NULL DEFAULT '',
`startdate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`enddate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`enddate_old` datetime NOT NULL,
`closed` tinyint(4) NOT NULL DEFAULT '0',
`keywords` text NOT NULL,
`nrbids` int(11) NOT NULL DEFAULT '0',
`maxbid` double(16,2) NOT NULL DEFAULT '0.00',
`clicks` int(11) NOT NULL DEFAULT '0',
`ownerid` int(11) NOT NULL DEFAULT '0',
`hpfeat` char(2) NOT NULL DEFAULT 'N',
`catfeat` char(2) NOT NULL DEFAULT 'N',
`bolditem` char(2) NOT NULL DEFAULT 'N',
`hlitem` char(2) NOT NULL DEFAULT 'N',
`private` varchar(10) NOT NULL DEFAULT '0',
`currency` varchar(100) NOT NULL DEFAULT '',
`swapped` tinyint(4) NOT NULL DEFAULT '0',
`postage_costs` double(16,2) NOT NULL DEFAULT '0.00',
`insurance` varchar(50) NOT NULL DEFAULT '',
`type_service` varchar(50) NOT NULL DEFAULT '',
`isswap` char(2) NOT NULL DEFAULT 'N',
`acceptdirectpayment` tinyint(4) NOT NULL DEFAULT '0',
`directpaymentemail` varchar(255) NOT NULL DEFAULT '',
`paidwithdirectpayment` tinyint(4) NOT NULL DEFAULT '0',
`addlcategory` int(11) NOT NULL DEFAULT '0',
`deleted` tinyint(4) NOT NULL DEFAULT '0',
`amountpaid` double(16,2) NOT NULL DEFAULT '0.00',
`paymentdate` int(11) NOT NULL DEFAULT '0',
`processor` varchar(50) NOT NULL DEFAULT '',
`shipping_details` mediumtext NOT NULL,
`hpfeat_desc` varchar(255) NOT NULL DEFAULT '',
`reserveoffer` double(16,2) NOT NULL DEFAULT '0.00',
`rpwinner` int(11) NOT NULL DEFAULT '0',
`listin` varchar(20) NOT NULL DEFAULT 'auction',
`close_in_progress` tinyint(4) NOT NULL DEFAULT '0',
`bid_in_progress` tinyint(4) NOT NULL DEFAULT '0',
`bank_details` text,
`accept_payment_systems` varchar(50) DEFAULT NULL,
`apply_vat` tinyint(4) NOT NULL DEFAULT '0',
`auto_relist` varchar(2) NOT NULL DEFAULT 'N',
`auto_relist_bids` varchar(2) NOT NULL DEFAULT 'N',
`endtime_type` enum('duration','customtime','lts') NOT NULL DEFAULT 'duration',
`videofile_path` varchar(255) NOT NULL,
`approved` tinyint(4) NOT NULL DEFAULT '0',
`count_in_progress` tinyint(4) NOT NULL,
`listing_type` enum('full','quick') NOT NULL DEFAULT 'full',
`offer_active` tinyint(4) NOT NULL,
`offer_range_min` double(16,2) NOT NULL,
`offer_range_max` double(16,2) NOT NULL,
`endauction_notification` tinyint(4) NOT NULL,
`auto_relist_nb` int(11) NOT NULL,
`nash_relist_status` tinyint(4) NOT NULL DEFAULT '0',
`endreason` text NOT NULL,
`counter_style` tinyint(4) NOT NULL DEFAULT '1',
`iconit` varchar(4) NOT NULL DEFAULT '000',
`isverify` char(2) NOT NULL DEFAULT 'Y',
`template` smallint(6) NOT NULL DEFAULT '0',
`main_region` varchar(255) NOT NULL,
`region_suburbs` varchar(255) NOT NULL,
`sc_insurance` varchar(50) NOT NULL DEFAULT '',
`sc_type_service` text NOT NULL,
`estimated_weight_lbs` varchar(50) NOT NULL DEFAULT '',
`estimated_weight_oz` varchar(50) NOT NULL DEFAULT '',
`package_size_length` varchar(50) NOT NULL DEFAULT '',
`package_size_width` varchar(50) NOT NULL DEFAULT '',
`package_size_height` varchar(50) NOT NULL DEFAULT '',
`odd_shaped_package` char(2) NOT NULL DEFAULT 'N',
`handling_fee` varchar(50) NOT NULL DEFAULT '',
`calculated` char(2) NOT NULL DEFAULT 'N',
`declVal` varchar(50) NOT NULL DEFAULT '0',
`combine_shipp` char(2) NOT NULL DEFAULT 'T',
`subitemname` varchar(255) NOT NULL,
`item_price_discount` double(16,2) NOT NULL,
`poweredbynbulker` tinyint(4) NOT NULL DEFAULT '0',
`is_relisted` tinyint(4) NOT NULL,
`nash_secondchanceoffer` tinyint(4) NOT NULL DEFAULT '0',
`charity_gets` double(16,2) NOT NULL,
`charity_id` int(11) NOT NULL,
`chacha` char(2) NOT NULL DEFAULT 'N',
`item_pw_type` int(4) NOT NULL,
`item_pw` varchar(30) NOT NULL,
`postage_costs_ww` double(16,2) NOT NULL,
`insurance_ww` varchar(50) NOT NULL,
`combine_shipp_ww` char(2) NOT NULL DEFAULT 'T',
`type_service_ww` varchar(50) NOT NULL,
`shipping_details_ww` mediumtext NOT NULL,
`digdel_path` varchar(255) NOT NULL,
`digdel_dl_nb` int(11) NOT NULL,
`isgoodtillcanceled` tinyint(4) NOT NULL DEFAULT '0',
`reserveadv_timelimit` int(11) NOT NULL DEFAULT '0',
`is_shipped` tinyint(4) NOT NULL DEFAULT '0',
`itemAddress` varchar(100) NOT NULL,
`itemCity` varchar(100) NOT NULL,
`itemState` varchar(100) NOT NULL,
`is_bidder_coseller` int(4) NOT NULL DEFAULT '0',
`is_fairwarning` tinyint(4) NOT NULL DEFAULT '0',
`myfolder_id` int(11) NOT NULL,
`reviews_nb` int(11) NOT NULL,
`retail_price` double(16,2) NOT NULL,
`ebay_post_cache` tinyint(4) NOT NULL DEFAULT '0',
`ebay_item_link` text NOT NULL,
`postage_costs_ww_specific` text NOT NULL,
`postage_costs_ww_country` text NOT NULL,
`pa_bidders` tinyint(4) NOT NULL DEFAULT '0',
`return_policy` mediumtext NOT NULL,
`return_policy_ww` mediumtext NOT NULL,
`enlpicitem` char(2) NOT NULL DEFAULT 'N',
`is_start_on_first_bid` tinyint(4) NOT NULL DEFAULT '0',
`hpfeatplus` char(2) NOT NULL DEFAULT 'N',
`catfeatplus` char(2) NOT NULL DEFAULT 'N',
`list_till_sold` tinyint(4) NOT NULL DEFAULT '0',
`immediatePayment` tinyint(4) NOT NULL DEFAULT '0',
`crazy_auctiontype` tinyint(4) NOT NULL DEFAULT '0',
`tax_amt` double(16,2) NOT NULL DEFAULT '0.00',
`nbwatchers` int(11) NOT NULL DEFAULT '0',
`forpickuponly` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `part_of_keywords` (`keywords`(30)),
KEY `part_of_category` (`category`),
KEY `part_of_country` (`country`(10)),
KEY `part_of_zip` (`zip`),
KEY `part_of_enddate` (`enddate`),
KEY `part_of_startdate` (`startdate`),
KEY `part_of_addlcategory` (`addlcategory`),
KEY `part_of_hlitem` (`hlitem`),
KEY `part_of_catfeat` (`catfeat`),
KEY `part_of_bolditem` (`bolditem`),
KEY `part_of_enlpicitem` (`enlpicitem`),
KEY `part_of_hpfeat` (`hpfeat`),
KEY `part_of_main_category` (`main_category`),
KEY `part_of_active` (`active`),
KEY `part_of_closed` (`closed`),
KEY `part_of_nrbids` (`nrbids`),
KEY `part_of_maxbid` (`maxbid`),
KEY `part_of_clicks` (`clicks`),
KEY `part_of_ownerid` (`ownerid`),
KEY `part_of_deleted` (`deleted`),
KEY `part_of_listin` (`listin`),
KEY `part_of_isverify` (`isverify`),
KEY `part_of_main_region` (`main_region`),
KEY `part_of_region_suburbs` (`region_suburbs`),
KEY `part_of_subitemname` (`subitemname`(10)),
KEY `part_of_charity_id` (`charity_id`),
KEY `part_of_chacha` (`chacha`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 COMMENT='Table with auctions details' AUTO_INCREMENT=166198 ;
--
-- Dumping data for table `yspnash_auctions`
--
INSERT INTO `yspnash_auctions` (`id`, `itemname`, `description`, `picpath`, `quantity`, `auctiontype`, `bidstart`, `bidend`, `bidstart_original`, `rp`, `rpvalue`, `bn`, `bnvalue`, `bi`, `bivalue`, `duration`, `country`, `zip`, `sc`, `scint`, `pm`, `main_category`, `category`, `active`, `payment_status`, `startdate`, `enddate`, `enddate_old`, `closed`, `keywords`, `nrbids`, `maxbid`, `clicks`, `ownerid`, `hpfeat`, `catfeat`, `bolditem`, `hlitem`, `private`, `currency`, `swapped`, `postage_costs`, `insurance`, `type_service`, `isswap`, `acceptdirectpayment`, `directpaymentemail`, `paidwithdirectpayment`, `addlcategory`, `deleted`, `amountpaid`, `paymentdate`, `processor`, `shipping_details`, `hpfeat_desc`, `reserveoffer`, `rpwinner`, `listin`, `close_in_progress`, `bid_in_progress`, `bank_details`, `accept_payment_systems`, `apply_vat`, `auto_relist`, `auto_relist_bids`, `endtime_type`, `videofile_path`, `approved`, `count_in_progress`, `listing_type`, `offer_active`, `offer_range_min`, `offer_range_max`, `endauction_notification`, `auto_relist_nb`, `nash_relist_status`, `endreason`, `counter_style`, `iconit`, `isverify`, `template`, `main_region`, `region_suburbs`, `sc_insurance`, `sc_type_service`, `estimated_weight_lbs`, `estimated_weight_oz`, `package_size_length`, `package_size_width`, `package_size_height`, `odd_shaped_package`, `handling_fee`, `calculated`, `declVal`, `combine_shipp`, `subitemname`, `item_price_discount`, `poweredbynbulker`, `is_relisted`, `nash_secondchanceoffer`, `charity_gets`, `charity_id`, `chacha`, `item_pw_type`, `item_pw`, `postage_costs_ww`, `insurance_ww`, `combine_shipp_ww`, `type_service_ww`, `shipping_details_ww`, `digdel_path`, `digdel_dl_nb`, `isgoodtillcanceled`, `reserveadv_timelimit`, `is_shipped`, `itemAddress`, `itemCity`, `itemState`, `is_bidder_coseller`, `is_fairwarning`, `myfolder_id`, `reviews_nb`, `retail_price`, `ebay_post_cache`, `ebay_item_link`, `postage_costs

Options: ReplyQuote


Subject Views Written By Posted
Do I have to re write querries when Partitioning? 2988 john hango 09/01/2010 11:17PM
Re: Do I have to re write querries when Partitioning? 1017 john hango 09/02/2010 04:09AM
Re: Do I have to re write querries when Partitioning? 1109 Rick James 09/03/2010 07:26PM
Re: Do I have to re write querries when Partitioning? 1227 john hango 09/05/2010 07:43PM
Re: Do I have to re write querries when Partitioning? 1071 john hango 09/05/2010 07:45PM
Re: Do I have to re write querries when Partitioning? 975 john hango 09/05/2010 07:47PM
Re: Do I have to re write querries when Partitioning? 1164 john hango 09/05/2010 07:51PM
Re: Do I have to re write querries when Partitioning? 881 john hango 09/05/2010 07:52PM
Re: Do I have to re write querries when Partitioning? 1323 john hango 09/05/2010 07:52PM
Here are some of the crons that run. 948 john hango 09/06/2010 11:38PM
Re: Here are some of the crons that run. 1109 john hango 09/06/2010 11:39PM
Slowest query is when I click on a category on my site that has alot of products 1535 john hango 09/08/2010 11:33AM
Re: Do I have to re write querries when Partitioning? 967 Rick James 09/10/2010 08:00PM


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.