MySQL 5.1.73 LEFT JOIN time out

Question

I'm working locally with a mysql 5.7.28 and everything is working but on my develop server on 5.1.73 my request time out (+ 2mn).

The request is pretty simple but I don't get why it's not working...

SELECT t1.*
FROM t1
LEFT JOIN t2 ON t1.id = t2.id

Weird things, if i'm swapping t1 and t2 it's working perfectly. If I use INNER JOIN it's working perfectly. If I do :

SELECT t1.* 
FROM t1 
WHERE t1.id 
NOT IN (
SELECT t1.id 
FROM t1 
INNER JOIN t2 ON t1.id = t2.id)

it's not working.

t1.id and t2.id have both indexes. t1.id can be NULL, t2.id can't (but in localhost too so ??).

If you have any idea about why this isn't working feel free to help me :D

TABLES :

T1 :

  CREATE TABLE `t1` (
 `idDossier` bigint(10) NOT NULL AUTO_INCREMENT,
 `id` char(11) DEFAULT NULL,
 `idEleve` varchar(11) CHARACTER SET latin1 DEFAULT NULL,
 `source` varchar(1) CHARACTER SET latin1 NOT NULL,
 `departementNaissance` varchar(3) CHARACTER SET latin1 DEFAULT NULL,
 `codeCommuneNaissance` varchar(5) CHARACTER SET latin1 DEFAULT NULL,
 `uairne` varchar(8) CHARACTER SET latin1 NOT NULL,
 `sexe` int(10) NOT NULL,
 `nom` varchar(50) CHARACTER SET latin1 NOT NULL,
 `prenom` varchar(50) CHARACTER SET latin1 NOT NULL,
 `prenom2` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
 `prenom3` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
 `dateNaissance` date DEFAULT NULL,
 `division` varchar(8) CHARACTER SET latin1 DEFAULT NULL,
 `niveau` varchar(11) CHARACTER SET latin1 DEFAULT NULL,
 `regimeScolaire` varchar(2) CHARACTER SET latin1 DEFAULT NULL,
 `dateEntree` date DEFAULT NULL,
 `dateSortie` date DEFAULT NULL,
 `exam` int(3) DEFAULT NULL,
 `autorisation` smallint(1) NOT NULL DEFAULT '0',
 `dateAutorisation` date DEFAULT NULL,
 PRIMARY KEY (`idDossier`),
 KEY `exam` (`exam`),
 KEY `nom` (`nom`),
 KEY `prenom` (`prenom`),
 KEY `dateNaissance` (`dateNaissance`),
 KEY `codeCommuneNaissance` (`codeCommuneNaissance`),
 KEY `nom_2` (`nom`,`prenom`,`dateNaissance`,`codeCommuneNaissance`),
 KEY `autorisation` (`autorisation`),
 KEY `ine` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=116431 DEFAULT CHARSET=utf8

////////////////////

T2 :

 CREATE TABLE `t2` (
 `idEleve` int(19) NOT NULL,
 `id` varchar(11) DEFAULT NULL,
 `etablissement` varchar(8) NOT NULL,
 `sexe` int(10) DEFAULT NULL,
 `nom` varchar(50) NOT NULL,
 `prenom` varchar(50) NOT NULL,
 `prenom2` varchar(100) DEFAULT NULL,
 `prenom3` varchar(100) DEFAULT NULL,
 `dateNaissance` date DEFAULT NULL,
 `codeCommuneNaissance` varchar(5) DEFAULT NULL,
 `departementNaissance` varchar(3) DEFAULT NULL,
 `adresseEleveLigne1` varchar(50) DEFAULT NULL,
 `adresseEleveLigne2` varchar(50) DEFAULT NULL,
 `adresseEleveLigne3` varchar(50) DEFAULT NULL,
 `adresseEleveLigne4` varchar(50) DEFAULT NULL,
 `codeCommuneEleve` varchar(5) DEFAULT NULL,
 `codePostalEleve` varchar(7) DEFAULT NULL,
 `adresseEleve2Ligne1` varchar(50) DEFAULT NULL,
 `adresseEleve2Ligne2` varchar(50) DEFAULT NULL,
 `adresseEleve2Ligne3` varchar(50) DEFAULT NULL,
 `adresseEleve2Ligne4` varchar(50) DEFAULT NULL,
 `codeCommuneEleve2` varchar(5) DEFAULT NULL,
 `codepostalEleve2` varchar(5) DEFAULT NULL,
 `division` varchar(20) DEFAULT NULL,
 `niveau` varchar(11) DEFAULT NULL,
 `regimeScolaire` varchar(2) DEFAULT NULL,
 `dateEntree` date DEFAULT NULL,
 `dateSortie` date DEFAULT NULL,
 `source` varchar(1) NOT NULL,
 PRIMARY KEY (`idEleve`,`source`,`etablissement`),
 KEY `nom` (`nom`),
 KEY `prenom` (`prenom`),
 KEY `codeCommuneNaissance` (`codeCommuneNaissance`),
 KEY `dateNaissance` (`dateNaissance`),
 KEY `niveau` (`niveau`),
 KEY `etablissement` (`etablissement`),
 KEY `nom_2` (`nom`,`prenom`,`dateNaissance`,`codeCommuneNaissance`),
 KEY `nom_3` (`nom`,`prenom`),
 KEY `eleve_ine` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Thanks !

Answer 1

When using the same table twice in a complex query, give each instance a different alias. The repetition of t1 confuses me, and may be confusing the parser!

If you want the rows in t1 that are not also in t2, use this pattern:

SELECT  ...
    FROM t1
    LEFT JOIN t2  ON t2.foo = t1.foo
    WHERE t2.id IS NULL;   -- Note

Avoid NOT IN ( SELECT ... ); it may be very poorly optimized.

Please provide SHOW CREATE TABLE so we can understand whether the repetition of the index on nom is a type of deliberate (such as INDEX and FULLTEXT).

it's not working

Timing out? Wrong results? Too many results? Too few results?

A lot of optimizations have been added between 5.1 and 5.7; this may explain "timing out".

Answer 2

Ok I solved the problem !

t1.id was in latin and t2.id was in utf8 .... Changed both to the same and it's instant !