Results 1 to 1 of 1
Recently when I ran the following simple MySQL query “select email from subscribers where email not in (select email from grp_mbrs)” on a client’s website via PHP, I continually got ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
- 04-09-2008 #1
- Join Date
- Mar 2008
Really Slow MySQL Queries
Recently when I ran the following simple MySQL query “select email from subscribers where email not in (select email from grp_mbrs)” on a client’s website via PHP, I continually got server timeout errors.The web server is running MySQL 4.1.2
I downloaded the MySQL tables to my local machine with MySQL 5.1.0 and ran the query via the MySQL Query Browser GUI. It took over 15 minutes. I then imported the tables into MS SQLServer and into Visual Foxpro and ran the query. Both were under 1 second, which is what I would have expected. Neither of these is optimized, I also tried “WHERE NOT EXIST” on MySQL but it was worse.
Although I’m relatively new to MySQL, I’ve got years of experience with various enterprise databases and have never experienced this before. Is MySQL just slow or what? Would any improvements in performance be realized by switching from ISAM to InnoDB (which currently does not seem to be an option in MySQL Administrator)? As this type of performance is not acceptable for the future plans for this site, should we be considering switching to the PostgreSQL, which is currently installed on the web server?
Here’s the structure for the two tables both of which have approx. 20,000 records
CREATE TABLE `grp_mbrs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`grp_id` int(10) unsigned NOT NULL,
`subscribe_id` int(10) unsigned NOT NULL,
`email` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=40300 DEFAULT CHARSET=latin1;
CREATE TABLE ‘subscribers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(30) DEFAULT NULL,
`last_name` varchar(30) DEFAULT NULL,
`middle_initial` char(1) DEFAULT NULL,
`email` varchar(100) NOT NULL DEFAULT '',
`organization` varchar(125) DEFAULT NULL,
`active` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=20611 DEFAULT CHARSET=latin1;