MySQL Forums
Forum List  »  Partitioning

Inner Joins, Compostie Keys and Partition issues
Posted by: Tofeeq Ali
Date: May 21, 2008 02:12AM

I am having a very strange problem. I have partitioned a table by range that had around two million records. When I run a select query on that table joined with another, sometimes it gives results but sometimes it doesn't although records meeting that criteria are there.

Here are the tables involved and query that is having probelms.


#This table stores million of subscriber records

CREATE TABLE `subscribers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`responderId` int(10) unsigned NOT NULL DEFAULT '0',
`email` varchar(75) NOT NULL DEFAULT '',
`firstName` varchar(75) NOT NULL DEFAULT '',
`lastName` varchar(75) NOT NULL DEFAULT '',
`status` tinyint(2) NOT NULL DEFAULT '0',
`address` varchar(100) NOT NULL DEFAULT '',
`address2` varchar(100) NOT NULL DEFAULT '',
`city` varchar(75) NOT NULL DEFAULT '',
`state` varchar(20) NOT NULL DEFAULT '',
`zip` varchar(10) NOT NULL DEFAULT '',
`phone` varchar(15) NOT NULL DEFAULT '',
`fax` varchar(15) NOT NULL DEFAULT '',
`country` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`,`responderId`),
UNIQUE KEY `responderId` (`responderId`,`email`),
KEY `email` (`email`),
KEY `status` (`status`)
) ENGINE=MyISAM AUTO_INCREMENT=2097145 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (responderId) (PARTITION p0 VALUES LESS THAN (4) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (7) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (13) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN (16) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (19) ENGINE = MyISAM, PARTITION p6 VALUES LESS THAN (22) ENGINE = MyISAM, PARTITION p7 VALUES LESS THAN (25) ENGINE = MyISAM, PARTITION p8 VALUES LESS THAN (28) ENGINE = MyISAM, PARTITION p9 VALUES LESS THAN (31) ENGINE = MyISAM, PARTITION p10 VALUES LESS THAN (34) ENGINE = MyISAM, PARTITION p11 VALUES LESS THAN (37) ENGINE = MyISAM, PARTITION p12 VALUES LESS THAN (40) ENGINE = MyISAM, PARTITION p13 VALUES LESS THAN (43) ENGINE = MyISAM, PARTITION p14 VALUES LESS THAN (46) ENGINE = MyISAM, PARTITION p15 VALUES LESS THAN (49) ENGINE = MyISAM, PARTITION p16 VALUES LESS THAN (52) ENGINE = MyISAM, PARTITION p17 VALUES LESS THAN (55) ENGINE = MyISAM, PARTITION p18 VALUES LESS THAN (58) ENGINE = MyISAM, PARTITION p19 VALUES LESS THAN (61) ENGINE = MyISAM, PARTITION p20 VALUES LESS THAN (64) ENGINE = MyISAM, PARTITION p21 VALUES LESS THAN (67) ENGINE = MyISAM, PARTITION p22 VALUES LESS THAN (70) ENGINE = MyISAM, PARTITION p23 VALUES LESS THAN (73) ENGINE = MyISAM, PARTITION p24 VALUES LESS THAN (76) ENGINE = MyISAM, PARTITION p25 VALUES LESS THAN (79) ENGINE = MyISAM, PARTITION p26 VALUES LESS THAN (82) ENGINE = MyISAM, PARTITION p27 VALUES LESS THAN (85) ENGINE = MyISAM, PARTITION p28 VALUES LESS THAN (88) ENGINE = MyISAM, PARTITION p29 VALUES LESS THAN (91) ENGINE = MyISAM, PARTITION p30 VALUES LESS THAN (94) ENGINE = MyISAM, PARTITION p31 VALUES LESS THAN (97) ENGINE = MyISAM, PARTITION p32 VALUES LESS THAN (100) ENGINE = MyISAM, PARTITION p33 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */

#This table stores lists that is related to messages and subscribers
#id field in this table is foriegn key in subscribers table i.e. responderId

CREATE TABLE `lists` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL DEFAULT '',
`fromName` varchar(50) NOT NULL DEFAULT '',
`fromEmail` varchar(50) NOT NULL DEFAULT '',
`status` tinyint(4) NOT NULL DEFAULT '0',
`priority` tinyint(3) NOT NULL DEFAULT '10',
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

# This table stores messages
# id of lists table is stored as a forieng key i.e responderId

CREATE TABLE `Messages` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`responderId` int(10) unsigned NOT NULL DEFAULT '0',
`subject` varchar(100) NOT NULL DEFAULT '',
`messageText` text NOT NULL,
`status` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx1` (`responderId`),
KEY `status` (`status`),
KEY `dateAdded` (`dateAdded`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12173 ;

# This table is populated when a new message is sent to a list and records are removed when message are sent
# pId is primary key of subscribers table
# mId is primary key of Messages table

CREATE TABLE `TempMessages` (
`pId` bigint(20) unsigned NOT NULL DEFAULT '0',
`mId` int(10) unsigned NOT NULL DEFAULT '0',
`status` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`pId`,`mId`),
KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now when I execute a query that joins subscribers and TempMessages table and use responderId in where clause, for some lists it gives empty results while works for some others. Here is the query that is issue

select p.id,p.email,p.firstName,p.lastName,p.address,p.address2,
p.city,p.state,p.zip,p.phone,p.fax, p.responderId,
p.status from subscribers p inner join
TempMessages temp on p.id=temp.pId and
temp.status=0 and p.id>0 and p.status < 2 and temp.mId=12165 order by p.id

This works fine but when I add "and p.responderId = 7" before order by to optimize the query, i get no results although in my previous query value for responderId in resultset is 7.

Now adding "and p.responderId = [some responderid]" works for few numbers e.g. 9 16, 84, 85, 90, 92 but doesn't work with some e.g. 7, 10, 11, 12, 20, 25, 26, 27, 65.

Now I don't know if it is a bug or some problem with my scheme but if it is a problem with schema then it should not work on some instances that it is doing.

Any help here will be greatly appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
Inner Joins, Compostie Keys and Partition issues
3653
May 21, 2008 02:12AM


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.