MySQL Forums
Forum List  »  Partitioning

Optimizer chooses full table scan
Posted by: Bill Willits
Date: December 11, 2007 01:22PM

We have a master-detail table relationship. The master table (Subscribers) is partitioned by range on ClientID and has 38M records. The detail table (Subscriptions) is a regular MyISAM table and has 56M records. The table structure and status are below.

For the following query, the optimizer should use the partitioned table as the driver but instead performs a full table scan on the detail table. Obviously, this takes a very long time to return the results.

Is there a way to restructure the tables or indexes to make the optimizer choose a better plan?

explain partitions
SELECT COUNT(DISTINCT a.SubscribeID)
FROM Subscribers a,Subscriptions b
WHERE a.ClientID=509
AND a.IsDeleted = 0
AND a.SubscribeID=b.SubscriberID \G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: index
possible_keys: cid_ip_uid
key: cid_ip_uid
key_len: 8
ref: NULL
rows: 56343702
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: p7
type: ref
possible_keys: PRIMARY,ix_Email,ix_ClientID
key: ix_ClientID
key_len: 8
ref: const,stun_development.b.SubscriberID
rows: 1
Extra: Using where

FYI: I can rewrite the query as follows and it will return in under 2s but I would rather not do so as it is a bit of a hack.

SELECT COUNT(DISTINCT SubscribeID)
FROM Subscriptions ,
( select SubscribeID from Subscribers where ClientID=509 and IsDeleted=0 ) Subscribers509
WHERE SubscribeID=SubscriberID;


Here are the table details (some fields removed for clarity).

Create Table: CREATE TABLE `Subscribers` (
`SubscribeID` int(11) NOT NULL AUTO_INCREMENT,
`UserID` int(10) unsigned NOT NULL DEFAULT '0',
`FormID` int(11) NOT NULL DEFAULT '0',
`Lists` varchar(100) NOT NULL DEFAULT '',
`ClientID` int(11) NOT NULL DEFAULT '0',
`Email` varchar(250) NOT NULL DEFAULT '',
`EmailPreference` varchar(15) NOT NULL DEFAULT 'HTML',
`BusinessPhone` varchar(15) NOT NULL DEFAULT '',
`HomePhone` varchar(15) NOT NULL DEFAULT '',
`FaxNumber` varchar(15) NOT NULL DEFAULT '',
`CellPhoneNumber` varchar(15) NOT NULL DEFAULT '',
`InstantMessageHandle` varchar(150) NOT NULL DEFAULT '',
`InstantMessageService` varchar(25) NOT NULL DEFAULT '',
`DateCreated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`LastModified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`LastModifiedBy` int(11) NOT NULL DEFAULT '0',
`IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`SubscribeID`,`ClientID`),
KEY `ix_Email` (`ClientID`,`Email`),
KEY `ix_HomePhone` (`HomePhone`,`ClientID`),
KEY `ix_FaxNumber` (`FaxNumber`,`ClientID`),
KEY `ix_CellPhoneNumber` (`CellPhoneNumber`,`ClientID`),
KEY `ix_DateCreated` (`DateCreated`,`ClientID`),
KEY `ix_ClientID` (`ClientID`,`SubscribeID`),
KEY `ix_LastModified` (`LastModified`,`ClientID`)
) ENGINE=MyISAM AUTO_INCREMENT=55027175 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( ClientID) (PARTITION p0 VALUES LESS THAN (30) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (31) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (200) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (300) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (400) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN (500) ENGINE = MyISAM, PARTITION p7 VALUES LESS THAN (600) ENGINE = MyISAM, PARTITION p8 VALUES LESS THAN (700) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (800) ENGINE = MyISAM, PARTITION p10 VALUES LESS THAN (834) ENGINE = MyISAM, PARTITION p11 VALUES LESS THAN (835) ENGINE = MyISAM, PARTITION p12 VALUES LESS THAN (900) ENGINE = MyISAM, PARTITION p13 VALUES LESS THAN (1000) ENGINE = MyISAM, PARTITION p14 VALUES LESS THAN (1100) ENGINE = MyISAM, PARTITION p15 VALUES LESS THAN (1200) ENGINE = MyISAM, PARTITION p16 VALUES LESS THAN (1300) ENGINE = MyISAM, PARTITION p17 VALUES LESS THAN (1313) ENGINE = MyISAM, PARTITION p18 VALUES LESS THAN (1314) ENGINE = MyISAM, PARTITION p19 VALUES LESS THAN (1400) ENGINE = MyISAM, PARTITION p20 VALUES LESS THAN (1437) ENGINE = MyISAM, PARTITION p21 VALUES LESS THAN (1438) ENGINE = MyISAM, PARTITION p22 VALUES LESS THAN (1500) ENGINE = MyISAM, PARTITION p23 VALUES LESS THAN (1600) ENGINE = MyISAM, PARTITION p24 VALUES LESS THAN (1700) ENGINE = MyISAM, PARTITION p25 VALUES LESS THAN (1800) ENGINE = MyISAM, PARTITION p26 VALUES LESS THAN (1900) ENGINE = MyISAM, PARTITION p28 VALUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p29 VALUES LESS THAN (2100) ENGINE = MyISAM, PARTITION p30 VALUES LESS THAN (2200) ENGINE = MyISAM, PARTITION p31 VALUES LESS THAN (2300) ENGINE = MyISAM, PARTITION p32 VALUES LESS THAN (2400) ENGINE = MyISAM, PARTITION p33 VALUES LESS THAN (2500) ENGINE = MyISAM, PARTITION p34 VALUES LESS THAN (2600) ENGINE = MyISAM, PARTITION p35 VALUES LESS THAN (2700) ENGINE = MyISAM, PARTITION p36 VALUES LESS THAN (2800) ENGINE = MyISAM, PARTITION p37 VALUES LESS THAN (2900) ENGINE = MyISAM, PARTITION p38 VALUES LESS THAN (3000) ENGINE = MyISAM, PARTITION p39 VALUES LESS THAN (3100) ENGINE = MyISAM, PARTITION p40 VALUES LESS THAN (3200) ENGINE = MyISAM, PARTITION p41 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

Create Table: CREATE TABLE `Subscriptions` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`SubscriberID` int(11) NOT NULL DEFAULT '0',
`ListID` int(11) NOT NULL DEFAULT '0',
`SubscriptionDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`Status` tinyint(1) NOT NULL DEFAULT '1',
`CellStatus` tinyint(1) NOT NULL DEFAULT '1',
`VoiceStatus` tinyint(1) NOT NULL DEFAULT '1',
`FaxStatus` tinyint(1) NOT NULL DEFAULT '1',
`DMStatus` tinyint(1) NOT NULL DEFAULT '1',
`IsConfirmed` tinyint(1) NOT NULL DEFAULT '0',
`EmailUnSubscriberDate` datetime DEFAULT NULL,
`CellUnSubscriberDate` datetime DEFAULT NULL,
`VoiceUnSubscriberDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`FaxUnSubscriberDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`DMUnSubscriberDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`UploadID` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`),
UNIQUE KEY `cid_ip_uid` (`SubscriberID`,`ListID`),
KEY `select_subs2` (`ListID`,`Status`,`SubscriberID`),
KEY `ix_EmailUnSubscriberDate` (`EmailUnSubscriberDate`),
KEY `ix_CellUnSubscriberDate` (`CellUnSubscriberDate`),
KEY `ix_VoiceUnSubscriberDate` (`VoiceUnSubscriberDate`),
KEY `ix_FaxUnSubscriberDate` (`FaxUnSubscriberDate),
KEY `ix_DMUnSubscriberDate` (`DMUnSubscriberDate`)
) ENGINE=MyISAM AUTO_INCREMENT=87873358 DEFAULT CHARSET=latin1

mysql> show table status like 'Subscribers' \G
*************************** 1. row ***************************
Name: Subscribers
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 38084737
Avg_row_length: 144
Data_length: 5513369836
Max_data_length: 0
Index_length: 4063664128
Data_free: 1370320
Auto_increment: 55027974
Create_time: 2007-12-10 10:39:06
Update_time: 2007-12-11 10:55:20
Check_time: 2007-12-10 11:29:13
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: partitioned
Comment:
1 row in set (0.00 sec)

mysql> show table status like 'Subscriptions' \G
*************************** 1. row ***************************
Name: Subscriptions
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 56345962
Avg_row_length: 71
Data_length: 4000563302
Max_data_length: 19984723346456575
Index_length: 6926308352
Data_free: 0
Auto_increment: 87873505
Create_time: 2007-11-26 15:33:26
Update_time: 2007-12-11 10:55:28
Check_time: 2007-11-26 16:39:50
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizer chooses full table scan
5463
December 11, 2007 01:22PM
3180
December 12, 2007 11:40AM


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.