JOIN performance MS Access VS MySQL
I love MySQL and I'm trying to "ween" myself off the great QBE tool in ACCESS. I tested the following statement which took only a few minutes in ACCESS on the same laptop machine......
create table Merged as select a.blact, a.ssn, a.acsv, a.tya, a.btn, b.cust_cd, a.wtn, a.address, a.notes
from mdvw a join custcdlkup b
on a.blact = b.blact and
a.ssn = b.ssn and
a.acsv = b.acsv and
a.btn = b.btn
where a.wtn is not null ;
Query OK, 47650 rows affected (1 hour 18 min 35.08 sec)
Records: 47650 Duplicates: 0 Warnings: 0
My default table type is INNODB. Later, I created the above "Merged" table as a MyIsam and tried to insert records into it using the same joins and got similar slow results.
OTHER PERTINENT INFO
THERE ARE NO INDEXES, LIKEWISE THE ACCESS TABLES HAD NO INDEXES.
CAN SOMEONE EXPLAIN THIS HUGE PERFORMANCE DISPARITY......REALLY....I'M NOT A MS GUY TRYING TO SET THIS UP....THIS IS A REAL PERPLEXING QUESTION TO ME.... I WAS EXPECTING MYSQL TO BE MUCH FASTER, OR NOT SLOWER.
/***************
-- MySQL dump 10.9
--
-- Host: localhost Database: cts
-- ------------------------------------------------------
-- Server version 4.1.10-nt
--
-- Table structure for table `custcdlkup`
--
DROP TABLE IF EXISTS `custcdlkup`;
CREATE TABLE `custcdlkup` (
`BLACT` varchar(255) default NULL,
`SSN` varchar(255) default NULL,
`ACSV` varchar(255) default NULL,
`BTN` varchar(255) default NULL,
`ADDRESS` varchar(255) default NULL,
`CUST_CD` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- MySQL dump 10.9
--
-- Host: localhost Database: cts
-- ------------------------------------------------------
-- Server version 4.1.10-nt
--
-- Table structure for table `mdvw`
--
DROP TABLE IF EXISTS `mdvw`;
CREATE TABLE `mdvw` (
`id` int(10) unsigned NOT NULL auto_increment,
`BLACT` varchar(255) default NULL,
`SSN` varchar(255) default NULL,
`ACSV` varchar(255) default NULL,
`TYA` varchar(255) default NULL,
`BTN` varchar(255) default NULL,
`CUST_CD` varchar(255) default NULL,
`WTN` varchar(255) default NULL,
`ADDRESS` varchar(255) default NULL,
`NOTES` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;