[mySQL5] select query op basis van strings traag

[Robbeke]

Dit speelt me nu al een tijdje parten dus hoop ik dat iemand van jullie een goede oplossing weet.

Ik werk met Ubuntu 7.04, Mysql 5 en PHP 5.5.

Ik heb in mysql een tabel met een lijst van emailadressen, deze telt +/- 150000 rijen. (Tabel A)
Daarnaast heb ik nog 4 andere tabellen die ook een textveld emailadres hebben. (Tabel B )
De vier tabellen (Tabel B ) bevatten tussen 1200 en 600 000 rijen.
Nu ben ik op zoek naar een manier om snel de gemeenschappelijke adressen te vinden tussen Tabel A en één van de andere tabellen uit B.


Nu wil ik een query uitvoeren waarin ik de lijst van emailadressen terugkrijg die in beide tabellen voorkomen.

Als ik deze Query gebruik:

code:

SELECT a.email 
FROM tabela a, tabelb b
WHERE a.email = b.email

Deze Query duurt 35 seconden bij wanneer ik voor Tabel B een tabel gebruik met maar 1200 rijen. Ik wil niet weten hoe lang het zal duren wanneer ik voor Tabel B een tabel gebruik met 600000 rijen...

Iemand een idee hoe ik dit zou moeten aanpakken? zou mysql full-text indexing hierbij helpen en een drastisch verschil geven? Ik heb ook al naar UNION's en VIEWs gekeken maar ik zie niet direct hoe deze hier mij kunnen bij helpen...

[GlowMouse]

Een index op a.email zou al een verschil maken. Maar wil je een nog grotere verbetering, dan maak je een tabel met id|e-mailaddres, en sla je in die andere tabellen alleen de numerieke id op. Joins op getallen zijn over het algemeen een stuk sneller dan joins op lange strings.

[Janoz]

Fulltext indexing is voor het zoeken van woorden in lappen tekst. Dat is hier niet relevant. Ik ben echter wel benieuwd naar het datatype dat je gebruikt voor de email adressen. Ik heb een beetje het vermoeden dat je hiervoor TEXT hebt gebruikt. Mocht dat zo zijn, verander dit dan snel naar VARCHAR.

[Robbeke]

Bedankt voor de snelle reacties.

De kolommen zijn inderdaad van het type text. Wat is juist het voordeel van type VARCHAR gebruiken in deze situatie? Het zal blijkbaar sneller gaan maar wat is de achterliggende reden?


Edit: heb ze omgezet naar type VARCHAR en een index toegevoegd op beide kolommen en nu gaat het supersnel.... is de reden om VARCHAR te gebruiken omdat je dan kan indexeren?

[ Voor 28% gewijzigd door Robbeke op 21-07-2008 17:18 ]

[remco_k]

GlowMouse schreef op maandag 21 juli 2008 @ 17:04:
Een index op a.email zou al een verschil maken. Maar wil je een nog grotere verbetering, dan maak je een tabel met id|e-mailaddres, en sla je in die andere tabellen alleen de numerieke id op. Joins op getallen zijn over het algemeen een stuk sneller dan joins op lange strings.

Juistem! Naam voor dit: Normalisatie.

[Janoz]

TEXT is bedoeld voor grote lappen tekst. Het type is daarvoor geoptimaliseerd. VARCHAR is eerder voor kortere strings (hier moet je ook vaak een afmeting bij opgeven). VARCHARS worden in het record zelf opgenomen. Een TEXT is veel groter. Hiervoor wordt een stuk meer ruimte gereserveerd. Ze passen dus niet meer bij het record zelf en worden dan ook ergens anders opgeslagen. Vergelijkingen zijn daarom een stuk trager.

Een email adres zal bijvoorbeeld nooit langer zijn dan 255 tekens. Een VARCHAR(255) is dan ook genoeg.

[Creepy]

Een varchar kan maximaal 255 tekens bevatten, een TEXT veel meer maar heeft dan weer andere index support (een index op een TEXT veld kan wel, maar dan moet je een maximum lengte op gaan geven). Ik zou er juist een gewoonte van maken om voor variabele teksten die niet langer dan 255 tekens te hoeven zijn een varchar te nemen en pas als je meer ruimte nodig bent een TEXT.

*bliep*Wat Janoz zegt dus...

[ Voor 4% gewijzigd door Creepy op 21-07-2008 17:23 ]

[GlowMouse]

Zoals ik het nu lees is tabel A al zijn tabel met e-mailadressen. Dan moet je daarin een id opnemen, en alleen dat id opslaan in de andere tabellen.

[ Voor 37% gewijzigd door GlowMouse op 21-07-2008 17:23 . Reden: traag ]

[Noork]

Robbeke schreef op maandag 21 juli 2008 @ 17:12:
De kolommen zijn inderdaad van het type text. Wat is juist het voordeel van type VARCHAR gebruiken in deze situatie? Het zal blijkbaar sneller gaan maar wat is de achterliggende reden?

Varchar bevat max 255 tekens, Text 65535 tekens. Dit levert gewoonweg performance-verschil op.

spuit 11

[ Voor 24% gewijzigd door Noork op 21-07-2008 17:23 ]

[RobIII]

Creepy schreef op maandag 21 juli 2008 @ 17:22:
Een varchar kan maximaal 255 tekens bevatten

Noork schreef op maandag 21 juli 2008 @ 17:23:
[...]

Varchar bevat max 255 tekens, Text 65535 tekens. Dit levert gewoonweg performance-verschil op.

Niet helemaal waar:

quote: http://dev.mysql.com/doc/refman/5.0/en/char.html

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

En dat is overigens per RDBMS verschillend (in in het geval van MySQL zelfs per storage engine als ik me niet vergis). MSSQL kent weer andere waarden waar _{(v.z.i.w. bij MSSQL 2000 de max. row size op iets minder dan 8Kb lag, wegens een paar bytes overhead)}... Bij TEXT gelden weer andere regels en is het volgens mij 'gelimiteerd' op 2Gb inhoud (maar ook dat verschilt weer per RDBMS en soms zelfs per versie).

Los van dat alles dien je gewoon een VARCHAR te gebruiken voor "korte" tekst als emailadressen e.d. TEXT gebruik je bij complete 'krantenartikelen' enzo.

Janoz schreef op maandag 21 juli 2008 @ 17:21:
Een email adres zal bijvoorbeeld nooit langer zijn dan 255 tekens. Een VARCHAR(255) is dan ook genoeg.

Hoewel 255 in 99.999999% van de gevallen zal voldoen:

quote: http://tools.ietf.org/html/rfc2821

local-part
The maximum total length of a user name or other local-part is 64 characters.
domain
The maximum total length of a domain name or number is 255 characters.

Dan kom ik op 319

[ Voor 48% gewijzigd door RobIII op 21-07-2008 17:42 ]

[GlowMouse]

Janoz schreef op maandag 21 juli 2008 @ 17:21:
Een TEXT is veel groter. Hiervoor wordt een stuk meer ruimte gereserveerd. Ze passen dus niet meer bij het record zelf en worden dan ook ergens anders opgeslagen.

Daarvoor zie ik hier geen onderbouwing.

edit: ah, hier:

TEXT and BLOB columns are implemented differently in the NDB Cluster storage engine, wherein each row in a TEXT column is made up of two separate parts. One of these is of fixed size (256 bytes), and is actually stored in the original table. The other consists of any data in excess of 256 bytes, which is stored in a hidden table. The rows in this second table are always 2,000 bytes long. This means that the size of a TEXT column is 256 if size <= 256 (where size represents the size of the row); otherwise, the size is 256 + size + (2000 – (size – 256) % 2000).

[ Voor 47% gewijzigd door GlowMouse op 21-07-2008 17:34 ]