List:General Discussion« Previous MessageNext Message »
From:Christofer Dutz Date:September 21 2005 6:17pm
Subject:Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)Keys
View as plain text  
Ok ... here all the information I could find:

CREATE TABLE `disco_lad` (
   `id` bigint(16) unsigned NOT NULL auto_increment,
   `import_datum` date default NULL,
   `import_zeit` time default NULL,
   `gst` smallint(4) unsigned default '0',
   `gst_bez` varchar(40) default NULL,
   `betrieb` tinyint(2) unsigned default '0',
   `auft_nr` bigint(10) unsigned default NULL,
   `auft_referenz` varchar(40) default NULL,
   `lad_datum` date default NULL,
   `lad_von_zeit` time default NULL,
   `lad_bis_zeit` time default NULL,
   `lad_land` char(3) default NULL,
   `lad_plz` varchar(6) default NULL,
   `lad_lon_deg` double default NULL,
   `lad_lat_deg` double default NULL,
   `lad_ort` varchar(40) default NULL,
   `lad_str` varchar(40) default NULL,
   `lad_fix` tinyint(1) default '0',
   `frankatur` varchar(20) default NULL,
   `name_vers` varchar(40) default NULL,
   `name_auftg` varchar(40) default NULL,
   `pnr_vers` varchar(10) default NULL,
   `pnr_auftg` varchar(10) default NULL,
   `pnr_ladeort` varchar(10) default NULL,
   `lad_gew` int(6) unsigned default '0',
   `lad_meter` float(3,2) unsigned default '0.00',
   `lad_fracht` float(5,2) unsigned default '0.00',
   `lad_lkw_typ` varchar(30) default NULL,
   `lad_art` varchar(20) default NULL,
   `lad_gg` tinyint(1) default '0',
   `lad_tausch` tinyint(1) default '0',
   `lad_last` mediumint(6) default '0',
   `entlad_datum` date default NULL,
   `entlad_von_zeit` time default NULL,
   `entlad_bis_zeit` time default NULL,
   `entlad_land` char(3) default NULL,
   `entlad_plz` varchar(6) default NULL,
   `entlad_lon_deg` double default NULL,
   `entlad_lat_deg` double default NULL,
   `entlad_ort` varchar(40) default NULL,
   `entlad_str` varchar(40) default NULL,
   `entlad_fix` tinyint(1) default '0',
   `lad_hinweis` varchar(200) default NULL,
   `pnr_empf` varchar(10) default NULL,
   `pnr_entladeort` varchar(10) default NULL,
   `lad_disp_name` varchar(40) default NULL,
   `lad_disp_tel` varchar(20) default NULL,
   `lad_disp_mobil` varchar(20) default NULL,
   `lad_disp_fax` varchar(20) default NULL,
   `lad_disp_email` varchar(30) default NULL,
   `lad_disp_kontakt` tinyint(1) default '0',
   `export_gst` smallint(4) unsigned default '0',
   `export_betrieb` tinyint(2) unsigned default '0',
   `export_datum` date default NULL,
   `export_zeit` time default NULL,
   `export_disp_name` varchar(40) default NULL,
   `export_disp_tel` varchar(20) default NULL,
   `export_disp_mobil` varchar(20) default NULL,
   `export_disp_fax` varchar(20) default NULL,
   `export_disp_email` varchar(30) default NULL,
   `export_ref_auft` varchar(10) default NULL,
   `state` tinyint(3) unsigned default NULL,
   `modify_data` datetime default NULL,
   `modify_user` varchar(20) default NULL,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


CREATE TABLE `disco_snd` (
   `lad_id` bigint(16) unsigned NOT NULL default '0',
   `gst` smallint(4) unsigned default NULL,
   `betrieb` tinyint(2) unsigned default NULL,
   `auft_nr` bigint(10) unsigned default NULL,
   `snd_gew` mediumint(6) unsigned default NULL,
   `snd_meter` decimal(3,2) default NULL,
   `snd_signum` varchar(20) default NULL,
   `snd_inhalt` varchar(40) default NULL,
   `lademittel_bez` varchar(40) default NULL,
   `lademittel_code` smallint(4) unsigned default NULL,
   `lademittel_anz` mediumint(5) unsigned default NULL,
   `snd_gg_schl` varchar(6) default NULL,
   `snd_gg_bez` varchar(40) default NULL,
   `state` tinyint(3) unsigned default NULL,
   `modify_date` datetime default NULL,
   `modify_user` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 107520 kB'

I am using a MySQL 4.1.13 on a Redhat Linux server and accessing it 
using the new version of the JDBC driver from a windows XP client.
While performing the query I used the MySQL Admin-Tool to check the 
server Load and the load shown there was only minimal. The 
internet-connection can't be the problem, since The query returns the 
same results and one is fast and the other one realy slow.

Chris

Devananda wrote:
> Christofer Dutz wrote:
> 
>> Hi,
>>
>> I just ran into a very strange problem. I have two simple tables with 
>> BIGINT(16) PKs. The primary table has 7500 records and the secondars 
>> has 15000. If I execute:
>>
>> SELECT * FROM lad JOIN snd ON lad.id = snd.lad_id
>>
>> The query takes 8 seconds. If I execute this one:
>>
>> SELECT * FROM lad LEFT JOIN snd ON lad.id = snd.lad_id
>>
>> I quit the query after 5 minutes.
>>
>> Why is this so?
>>
>> Chritofer Dutz
>>
> 
> Christofer,
> 
> You'll need to provide the list with more information for anyone to be 
> able to help you. What version of MySQL are you running? Can you paste 
> the output of "show create table snd; show create table lad;" Also, do 
> you have other processes using the server that could have locked one of 
> the tables? When the query is taking a long time, try opening a second 
> connection to the server and running "show full processlist;" to see 
> what else is going on.
> 
> 
> Regards,
> Devananda vdv
Thread
Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)KeysChristofer Dutz21 Sep
  • Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)KeysDevananda21 Sep
    • Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)KeysChristofer Dutz21 Sep
      • Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)KeysAlexey Polyakov21 Sep