Problem with join function

[vitessenet]

I everyone, I'm new to this forum

 

I had a problem that I can't seem to solve. I recently learned MYSQL join function. this is really handy and I really want to understand why it doesn't work.

 

I work my test on an AMD s939 3500+ CPU with 2gb of ram, so it should be fast enough.

 

I have two table on my database, v2011client and v2011contrat and I want to find client in v2011client that has no associated contract in v2011contrat

 

At first it return no result and it should at least output one result as I created a client without any contract to test the system. and the query run for more than 1 minutes, so it indicate me the mysql server probably timeout has I have a time limit for query.

 

Any help explaining me what is wrong is welcome

 

 

SHOW CREATE TABLE v2011client output:

Table,"Create Table"
v2011client,"CREATE TABLE `v2011client` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `nom` varchar(40) NOT NULL DEFAULT '',
  `compagnie` varchar(40) NOT NULL DEFAULT '',
  `telephone` varchar(10) NOT NULL DEFAULT '',
  `cellulaire` varchar(10) NOT NULL DEFAULT '',
  `telecopieur` varchar(10) NOT NULL DEFAULT '',
  `couriel` varchar(40) NOT NULL DEFAULT '',
  `adresse` varchar(40) NOT NULL DEFAULT '',
  `ville` varchar(40) NOT NULL DEFAULT '',
  `province` varchar(40) NOT NULL DEFAULT '',
  `codepostale` varchar(6) NOT NULL DEFAULT '',
  `clienttype1` varchar(40) NOT NULL,
  `clienttype2` varchar(20) NOT NULL,
  `langue` varchar( NOT NULL DEFAULT '',
  `siteweb` varchar(40) NOT NULL,
  `solicitation` int(1) NOT NULL,
  `interet` int(2) NOT NULL,
  `limitecorporative` varchar(4) NOT NULL,
  `rappelledate` date NOT NULL,
  `rappelletype` int(1) NOT NULL,
  `commentaire` text NOT NULL,
  `login` varchar(20) NOT NULL DEFAULT '',
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3098 DEFAULT CHARSET=latin1 COMMENT='client bank'"

 

 

SHOW CREATE TABLE v2011contrat output:

Table,"Create Table"
v2011contrat,"CREATE TABLE `v2011contrat` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `clientid` int(6) unsigned NOT NULL,
  `nom` tinytext NOT NULL,
  `compagnie` tinytext NOT NULL,
  `telephone` int(14) NOT NULL,
  `cellulaire` int(10) NOT NULL,
  `telecopieur` int(10) NOT NULL,
  `couriel` varchar(40) NOT NULL DEFAULT '',
  `adresse` varchar(40) NOT NULL DEFAULT '',
  `ville` varchar(40) NOT NULL DEFAULT '',
  `province` varchar(40) NOT NULL DEFAULT '',
  `codepostale` varchar(7) NOT NULL DEFAULT '',
  `clienttype` varchar(40) NOT NULL DEFAULT '',
  `langue` varchar( NOT NULL DEFAULT '',
  `datedebut` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `datefin` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `lieudepart` varchar(10) NOT NULL DEFAULT '',
  `lieufin` varchar(10) NOT NULL DEFAULT '',
  `forfaitgroupe` varchar(40) NOT NULL DEFAULT '',
  `etape` text NOT NULL,
  `reservepassager` text NOT NULL,
  `contratpassager` varchar(80) NOT NULL DEFAULT '',
  `totalepassager` decimal(3,0) NOT NULL DEFAULT '0',
  `soustotale` varchar(10) NOT NULL DEFAULT '',
  `tps` varchar(10) NOT NULL DEFAULT '',
  `tvq` varchar(10) NOT NULL DEFAULT '',
  `grandtotale` varchar(10) NOT NULL DEFAULT '',
  `depotdue` varchar(10) NOT NULL DEFAULT '',
  `depotduedate` varchar(10) NOT NULL DEFAULT '',
  `paiementcomplet` text NOT NULL,
  `soldeapayer` varchar(10) NOT NULL DEFAULT '',
  `reservation` char(2) NOT NULL DEFAULT '',
  `commentaire` text NOT NULL,
  `commentaireinterne` text NOT NULL,
  `impressionbillet` char(2) NOT NULL DEFAULT '0',
  `impressioncontrat` char(2) NOT NULL DEFAULT '0',
  `config10` varchar(4) NOT NULL,
  `config11` varchar(4) NOT NULL,
  `config12` int(1) NOT NULL,
  `login` varchar(20) NOT NULL DEFAULT '',
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7032 DEFAULT CHARSET=latin1 COMMENT='contract'"

 

 

The query:

SELECT v2011client.id, v2011contrat.clientid, v2011client.compagnie, v2011client.telephone, v2011client.nom, v2011client.solicitation FROM v2011client LEFT JOIN v2011contrat ON v2011client.id = v2011contrat.clientid 

 

 

The explain extended output of the query:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,v2011client,ALL,NULL,NULL,NULL,NULL,3097,100.00,
1,SIMPLE,v2011contrat,ALL,NULL,NULL,NULL,NULL,5776,100.00,

[Barand]

try adding an index on clientid to the contract table

[vitessenet]

Thanks is way much faster. so I needed to add an index.