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
