[MySQL] Join op een VARCHAR kolom

[HuHu]

Ik heb in MySQL 5.0 een probleem. Zie onderstaand scenario:

SQL:

CREATE TABLE a (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = MYISAM

CREATE TABLE b (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a_id VARCHAR(11) NOT NULL,
  INDEX (a_id)
) ENGINE = MYISAM

CREATE TABLE c (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a_id INT NOT NULL,
  INDEX (a_id)
) ENGINE = MYISAM

Tabel a is gevuld met 250.000 oplopende ID's. Tabellen b en c zijn gevuld met 150.000 waarden, die allemaal ook in a zitten.

De volgende query:

SQL:

SELECT * 
FROM a
WHERE NOT EXISTS (
  SELECT *
  FROM b
  WHERE a.id = b.a_id
)

Als ik dit uitvoer op tabel b duurt dit een eeuwigheid, op tabel c is het zo klaar. Dat komt natuurlijk omdat je voor tabel b een VARCHAR gebruikt voor a_id.

Nu heb ik het probleem dat ik een VARCHAR moet gebruiken in tabel b, omdat deze gevuld wordt vanuit een externe applicatie die niet anders snapt.

Heeft iemand een suggestie hoe dit te versnellen op één of andere manier? Zelf dacht ik om tabel b zo aan te passen dat een trigger before-insert-on die waarde omzet naar een INT. Maar is er nog een andere (wellicht handigere) manier om toch snel dit soort queries te kunnen doen op tabel a en b?

[hellfighter87]

Een index maken op die varchar?

[Thomasje]

Het is volgens mij zo als je een string in mysql insert op een veld wat int is word het volgens mij omgezet naar int.

[Voutloos]

HuHu schreef op zaterdag 16 januari 2010 @ 12:33:
.... Dat komt natuurlijk omdat je voor tabel b een VARCHAR gebruikt voor a_id.

Nu heb ik het probleem dat ik een VARCHAR moet gebruiken in tabel b, omdat deze gevuld wordt vanuit een externe applicatie die niet anders snapt.

Als het een int kolom zou zijn zouden de huidige insert queries gewoon blijven werken hoor?

edit:
Wat thomas zegt dus.

[ Voor 4% gewijzigd door Voutloos op 16-01-2010 12:49 ]

[HuHu]

hellfighter87 schreef op zaterdag 16 januari 2010 @ 12:37:
Een index maken op die varchar?

Zoals je in de CREATE TABLE kunt zien zit die er al op.

Thomasje schreef op zaterdag 16 januari 2010 @ 12:48:
Het is volgens mij zo als je een string in mysql insert op een veld wat int is word het volgens mij omgezet naar int.

Dat klopt, maar de externe applicatie die de tabel vult controleert eerst het type voordat hij de INSERT doet. Een INT, wat in MySQL een INT(11) is, ziet het als een INT(4). Ik kan dus geen ID's hebben die groter zijn dan 9999, alles na het vierde cijfer wordt afgekapt door die applicatie. Een INSERT van het getal 123456 door die applicatie gaat niet, het zal het altijd omzetten naar 1234.

De enige oplossing die ik daarvoor zag was het "nabootsen" van een INT met een VARCHAR(11).

[Thomasje]

Als het dan toch zo'n workaround is kan je dan niet een trigger er opzetten die de value van de varchar(11) in een int veld zet?

[Macros]

Een index op een foreign key werkt alleen goed als de foreign key en het id hetzelde type hebben. Anders kan de index niet gebruikt worden in de join. Je zult in je tabel b toch een veld zien te maken van het type INT die een foreign key is met een index erop. Dat kan je doen door een nieuw veld te declareren, die de waarde van veld a_id gecast naar een int als waarde heeft. Dat kan je als default in de table definitie opnemen. Daar leg je dan je index overheen (ipv over a_id), en je joined voortaan op het nieuwe veld.

[GlowMouse]

Die index wordt niet gebruikt omdat je een string met een integer vergelijkt. Probeer a.id in je query eens naar een string te casten voor je vergelijkt.

[ Voor 5% gewijzigd door GlowMouse op 16-01-2010 13:07 ]

[Voutloos]

Dude, dat nummertje bij de integer data types betekent helemaal niets. Het regelt enkel wat formatting in de CLI. Het beinvloedt verder niets aan de storage en range van die kolom.

Het gedrag van die insertende app is gewoon stom en/of buggy.

Thomasje schreef op zaterdag 16 januari 2010 @ 13:04:
Als het dan toch zo'n workaround is kan je dan niet een trigger er opzetten die de value van de varchar(11) in een int veld zet?

Als de oplossing een workaround moet zijn (de echte oplossing zit natuurlijk in het fixen van de insertende app), dan is dit wellicht een voldoende fix.

Macros schreef op zaterdag 16 januari 2010 @ 13:06:
Een index op een foreign key werkt alleen goed als de foreign key en het id hetzelde type hebben.

En dat zou te zien zijn in iets dat in de topicstart ontbreekt: De output van EXPLAIN.

[HuHu]

GlowMouse schreef op zaterdag 16 januari 2010 @ 13:07:
Die index wordt niet gebruikt omdat je een string met een integer vergelijkt. Probeer a.id in je query eens naar een string te casten voor je vergelijkt.

Casten naar een VARCHAR kan niet, maar dit helpt ook niet:

SQL:

SELECT *  
FROM a 
WHERE NOT EXISTS ( 
  SELECT * 
  FROM b 
  WHERE CAST(a.id AS CHAR) = b.a_id 
)

Voutloos schreef op zaterdag 16 januari 2010 @ 13:10:
Dude, dat nummertje bij de integer data types betekent helemaal niets. Het regelt enkel wat formatting in de CLI. Het beinvloedt verder niets aan de storage en range van die kolom.

Het gedrag van die insertende app is gewoon stom en/of buggy.

Ik weet dat het nummertje niets betekend, maar dat was ter illustratie van wat er gebeurd. Een INT bestaat uit een aantal cijfers (bijvoorbeeld 6, 123456), maar die applicatie denkt dat een INT uit maximaal 4 cijfers kan bestaan (123456 wordt dus 1234) en kapt ze af.

[...]
Als de oplossing een workaround moet zijn (de echte oplossing zit natuurlijk in het fixen van de insertende app), dan is dit wellicht een voldoende fix.

Ik hoopte dat er wellicht nog een andere oplossing was, maar waarschijnlijk wordt het dan toch werken met een TRIGGER.

[...]
En dat zou te zien zijn in iets dat in de topicstart ontbreekt: De output van EXPLAIN.

De EXPLAIN met tabel b:

code:

1   PRIMARY         a   index   NULL    PRIMARY 4   NULL    250000  Using where; Using index
2   DEPENDENT SUBQUERY  b   index   NULL    a_id    13  NULL    150000  Using where; Using index

De EXPLAIN met tabel c:

code:

1   PRIMARY         a   index   NULL    PRIMARY 4   NULL        250000  Using where; Using index
2   DEPENDENT SUBQUERY  c   ref a_id    a_id    4   test.a.id   1   Using index

Hierin kun je zien dan in het tweede geval er veel minder rijen hoeven te worden bekeken, waardoor het sneller is.

[Voutloos]

Overigens is een left join met controle op de rechterterm niet gematched equivalent aan deze query en in het geval van mysql in 90% vd gevallen flink sneller. De reden is dat je dan geen dependent subquery meer hebt. Wellicht leuk om eens te proberen.

[GlowMouse]

HuHu schreef op zaterdag 16 januari 2010 @ 13:25:
[...]

Casten naar een VARCHAR kan niet, maar dit helpt ook niet:

SQL:

SELECT *  
FROM a 
WHERE NOT EXISTS ( 
  SELECT * 
  FROM b 
  WHERE CAST(a.id AS CHAR) = b.a_id 
)

Dat helpt niet omdat het type nog steeds niet goed is. Probeer eens iets als CONVERT(a.id USING latin1)

[HuHu]

Voutloos schreef op zaterdag 16 januari 2010 @ 13:28:
Overigens is een left join met controle op de rechterterm niet gematched equivalent aan deze query en in het geval van mysql in 90% vd gevallen flink sneller. De reden is dat je dan geen dependent subquery meer hebt. Wellicht leuk om eens te proberen.

De volgende query:

SQL:

SELECT a.id
FROM a
LEFT JOIN c
ON a.id = c.a_id
WHERE c.a_id IS NULL

Deze is inderdaad zo'n 20% sneller op tabel c, maar voor tabel b duurt het nog steeds een eeuwigheid. Dat helpt dus niet voldoende.

Overigens geeft de explain nu aan bij extra: not exists. Dus ik vermoed dat hij het stiekem omschrijft naar een NOT EXISTS query.

[HuHu]

GlowMouse schreef op zaterdag 16 januari 2010 @ 13:37:
[...]

Dat helpt niet omdat het type nog steeds niet goed is. Probeer eens iets als CONVERT(a.id USING latin1)

Ik moest eerst de kolom b.a_id nog op latin1_swedish_ci zetten, anders wilde hij niet, maar toen deed de volgende query het:

SQL:

SELECT *   
FROM a  
WHERE NOT EXISTS (  
  SELECT *  
  FROM b  
  WHERE CONVERT(a.id USING latin1) = b.a_id
)

Deze doet er 2,1 seconden over, die op tabel c 0,4. Maar nu kun je tenminste nog op het resultaat wachten .

De explain:

code:

1   PRIMARY         a   index   NULL    PRIMARY 4   NULL    250000  Using where; Using index
2   DEPENDENT SUBQUERY  b   ref a_id    a_id    13  func    1   Using where; Using index

Veel dank!

[RobIII]

Voutloos schreef op zaterdag 16 januari 2010 @ 13:10:
Dude, dat nummertje bij de integer data types betekent helemaal niets. Het regelt enkel wat formatting in de CLI. Het beinvloedt verder niets aan de storage en range van die kolom.

offtopic:
Ik had gisteren toevallig een DB waar ik van een INT(10) een FK naar een INT(11) wou maken (InnoDB) maar dat vrat 'ie toch mooi niet. Er wordt altijd wel beweerd door iedereen wat jij nu ook beweert, maar als het puur met formatting te maken had dan zie ik niet waarom 'ie die FK niet aan wou leggen (en met een HEUL andere vage foutmelding kwam). INT(10) naar INT(11) aangepast en opgelost.

[GlowMouse]

RobIII schreef op zaterdag 16 januari 2010 @ 14:02:
[...]

offtopic:
Ik had gisteren toevallig een DB waar ik van een INT(10) een FK naar een INT(11) wou maken (InnoDB) maar dat vrat 'ie toch mooi niet. Er wordt altijd wel beweerd door iedereen wat jij nu ook beweert, maar als het puur met formatting te maken had dan zie ik niet waarom 'ie die FK niet aan wou leggen (en met een HEUL andere vage foutmelding kwam). INT(10) naar INT(11) aangepast en opgelost.

Geen probleem met signed/unsigned? Dit werkt prima namelijk:

code:

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT(10),
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

[Voutloos]

offtopic:
@rob: vage shit. Dan is het gewoon een kwestie van altijd de magische 11 in te vullen en verder niet meer over na te denken. Wel een beetje zot natuurlijk. Dergelijke errors steunen eigenlijk alleen maar mijn progressieve mening mbt dit soort shit: ram het er nou gewoon uit. Bij de overstap moet men er misschien 10 seconden aan denken, maar dan ben je er de rest van je leven van af. Hmz... misschien maar eens een fork starten met iets van 'lichte regen' in de naam.