I wonder how somebody can approve such a query and tables. 1) There is no indexes, thats the main reason for the time and cpu consuming. At least, the fields used in the joins MUST be indexed.
2) varchar(255) on ALL fields? That's unlikely, in-cre-di-ble. Right type for the right data, int for numbers, float for money, char for fixed string, and certainly NOT 255 for lenght! If its necesary such amount of characters, TEXT or BLOB must be used, but only if it is necesary. CREATE TABLE `memupdate` ( `MemID` int(10) NOT NULL default 0, `Status` varchar(255) NOT NULL default '', `Salut` varchar(255) NOT NULL default '', `First` varchar(255) NOT NULL default '', `Middle` varchar(255) NOT NULL default '', `Last` varchar(255) NOT NULL default '', `Suffix` varchar(255) NOT NULL default '', `SSN` varchar(255) NOT NULL default '', `Sex` varchar(255) NOT NULL default '', `Admit` varchar(255) NOT NULL default '', `Birth` varchar(255) NOT NULL default '', `Exam` varchar(255) NOT NULL default '', `School` varchar(255) NOT NULL default '', `Company` varchar(255) NOT NULL default '', `Add1` varchar(255) NOT NULL default '', `Add2` varchar(255) NOT NULL default '', `City` varchar(255) NOT NULL default '', `State` varchar(255) NOT NULL default '', `Zip` varchar(255) NOT NULL default '', `Country` varchar(255) NOT NULL default '', `Phone` varchar(255) NOT NULL default '', `Fax` varchar(255) NOT NULL default '', `Circuit` varchar(255) NOT NULL default '', `County` varchar(255) NOT NULL default '', `Year` varchar(255) NOT NULL default '', `Email` varchar(255) NOT NULL default '', `LastUpdated` varchar(255) NOT NULL default '', primary key (memID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `tblcc` ( `record_status` varchar(255) NOT NULL default '', `grp0` varchar(255) NOT NULL default '', `member` int(10) NOT NULL default 0, `appl_year` varchar(255) NOT NULL default '', `course_grp0` varchar(255) NOT NULL default '', `sponsor` varchar(255) NOT NULL default '', `course` varchar(255) NOT NULL default '', `dater` varchar(255) NOT NULL default '', `sub` varchar(255) NOT NULL default '', `date_maint` varchar(255) NOT NULL default '', `who_maint` varchar(255) NOT NULL default '', `seq_maint` varchar(255) NOT NULL default '', `sp_require` varchar(255) NOT NULL default '', `appl_hrs_01` varchar(255) NOT NULL default '', `appl_hrs_02` varchar(255) NOT NULL default '', `appl_hrs_03` varchar(255) NOT NULL default '', `appl_hrs_04` varchar(255) NOT NULL default '', `appl_hrs_05` varchar(255) NOT NULL default '', `appl_hrs_06` varchar(255) NOT NULL default '', `type` varchar(255) NOT NULL default '', `fee` varchar(255) NOT NULL default '', `comment_flag` varchar(255) NOT NULL default '', `paid` varchar(255) NOT NULL default '', `fee_override` varchar(255) NOT NULL default '', `charge` varchar(255) NOT NULL default '', `attend_type` varchar(255) NOT NULL default '', `instructor` varchar(255) NOT NULL default '', `date_taken` date NOT NULL default '', index memindex (member), index a_year (appl_year), index d_taken (date_taken) ENGINE=InnoDB DEFAULT CHARSET=latin1 Im assuming memupdate has unique values for memID, thus, the primary key over memID -> primary key (memID) -> index memindex (member) index for the where ->index a_year (appl_year) index fot order ->index d_taken (date_taken) For a performance issues, is better that fields in a join be of the same type and lenght -> `MemID` int(10) NOT NULL default 0, -> `member` int(10) NOT NULL default 0, data types ->`appl_year` int(4) NOT NULL default 0, ->`date_taken` date NOT NULL default '', Who made that tables and query really REALLY needs to RTFM :) http://dev.mysql.com/doc/mysql/en/select-speed.html ___________________________________________________________ 250MB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
