[MySQL] Select in grote tabel duurt te lang?

[Verwijderd]

Zo... Ik heb een database met daarin een tabel met ongeveer 1.000.000 records. Als ik nu een normale select doe hierop, bijvoorbeeld:

code:

SELECT `code` FROM `table`

dan duurt dit gewoon al bijna 20 seconden. Volgens mij moet dit vele malen sneller kunnen. Ik heb natuurlijk al een index op dat veld gezet dus dat is het niet.
Wel moet ik er nog even bij vermelden dat dat "code"-veld een varchar is. Uit die tabel moet ik dan de dubbele codes halen en ook nog deze tabel op het "code" veld vergelijken met een andere tabel van ongeveer 500.000 records. Ik heb die query' s ook geprobeerd maar daar is ie iig niet binnen 10 minuten mee klaar. Toen ben ik er maar mee opgehouden. Heeft er iemand een idee waar dit nog meer aan kan liggen?

[Verwijderd]

Dit kan ook aan de snelheid van je server liggen, het is immers IO. En een index toevoegen heeft hier niet zoveel zin aangezien je die index niet in de query gebruikt.

[justmental]

Met die select haal je alle records op, dus dat mag best even duren en een index heeft geen zin in zo'n geval.

De andere query's zou je moeten posten om daar naar te kunnen kijken.

[Michali]

Ik snap eerlijk gezegd niet echt wat je nou wil bereiken. Je uitleg is een beetje vaag. Mischien tijd voor een snellere server anders?

[Verwijderd]

Ik denk niet dat het alleen aan de server ligt. Dat is een prima bak. Maar om bijvoorbeeld het aantal dubbelen in die tabel te bereken heb ik dus het totaal aantal nodig en het aantal verschillende. Als dan de query voor het aantal verschillende al meer dan een minuut duurt dan klopt er volgens mij iets niet, maar ik zou zo niet weten wat.Hier bij ons bedrijf komen we er alle 5 niet uit dus ik dacht ik probeer het even op good-old-GoT
Maar dit zou dus nog de snelste query van alles moeten zijn want in stap twee moet ik dus kijken of er dezeflde in twee van die tabellen zitten. Join is dan geen optie (1000000*500000 == veel) dus dat had ik al anders opgelost. Maar dan wordt er een temporary tabel gemaakt en dat duurt zo lang dat hij er na een half uur mee ophoud... Kan het iets met een bepaalde buffer te maken hebben ofzo?

code:

SELECT DISTINCT(`matchcode`) FROM `tbl_8_20041025_1317`
duurde 65.722112894058 seconden

[justmental]

Dat is weer een full table scan met een sort over alle rijen, die 65 seconden vind ik niet gek dan.

Wees eens duidelijk over wat je wil bereiken.

[StevenK]

Wat is er mis met een

code:

Select count(*), code from tabel group by code

[MaTriCX]

Wellicht zou je in plaats van de standaard MyISAM een ander opslagtype kunnen gebruiken. InnoDB is bijvoorbeeld sneller in het werken met indexen en werkt ook met transacties. Er was nog een variant die nog sneller was, maar die wordt volgens mij niet ondersteund door MySQL.
Ik dacht BTree.

[Alex]

Zojuist heb ik een simpele select op een int zonder where-clause gedaan over een vrij grote tabel:
Record: 342,561
Type: InnoDB
Grote v. Tabel: 326.6 MB

De query duurde bijna 55 seconde. In een live omgeving die gem. 550 q/s heeft. Al met al niet heel schokkend. Mijn volledige dataset was bijna 4 Mb.
Mijn grote vraag is nu, wat wil ik met deze data? Is deze data genormaliseerd? Wat gebeurd er met deze data? Heb je de juiste oplossing voor dit probleem?
Mij lijkt namelijk dat je 500.000 rows niet voor een bepaald simpel doeleinde gata evrgelijken met nog eens een x aantal rows. Er vallen zat trucjes te verzinnen hoe je dit op zou kunne lossen.
In die codes zullen namelijk vast ene hoop 'prefixen' voorkomen. Zeg 3 identieke letter-cijfer combo's die veel vaker voorkomen. Indexeer deze in een aparte tabel.
Indexeer vervolgens in een andere tabel het 2e paar van 3 identieke letter-cijfer combo's. Etc. Etc.
Deze koppel je dmv een stel koppeltabellen aan elkaar, je maakt eenmaal ene query en je krijgt een x-hoeveelheid potentiële rows. Steker nog, je hebt als een aantal potentiële rows geindexeerd doordat je die 'prefixen' al hebt.

[Creepy]

MaTriCX schreef op dinsdag 04 januari 2005 @ 20:35:
Wellicht zou je in plaats van de standaard MyISAM een ander opslagtype kunnen gebruiken. InnoDB is bijvoorbeeld sneller in het werken met indexen en werkt ook met transacties. Er was nog een variant die nog sneller was, maar die wordt volgens mij niet ondersteund door MySQL.
Ik dacht BTree.

Zonder where krijg je nagenoeg automatisch een table scan. Hierbij wordt de index niet gebruikt omdat simpelweg het doorlezen van de gehele tabel gewoon sneller is.

Er zal een query verzonnen moeten worden waarbij een where gebruikt kan worden en het resultaat zo klein mogelijk is, alleen dan is het nog te versnellen.

Dus wat wil je nu precies waarom bereiken en waarom denk je dat je een select ZONDER where nodig hebt hiervoor?

[Verwijderd]

De bedoeling is ten eerste dat het aantal dubbele codes wordt weergegeven in 1 tabel. Dus mijn optie was dan: Eerst het totale aantal, dan het aantal verschillende codes en die van elkaar afhalen, voila het aantal dubbelen.

Ten tweede moet het dubbelen uit 2 tabellen getoond worden. Dus alles wat in beide tabellen zit. Volgens mij moetie daar toch de hele tabellen voor doorleopen dan of niet ... ?

[whoami]

Kan je dat zo niet doen:

code:

select count(1), code
from tabel
group by code
having count(1) > 1

[ Voor 10% gewijzigd door whoami op 05-01-2005 09:10 ]

[Eelke Spaak]

En alles wat in beide tabellen zit zou toch zo moeten kunnen:

code:

SELECT veld
FROM tabel1
INNER JOIN tabel2 ON (tabel2.veld = tabel1.veld)

[Verwijderd]

Creepy schreef op dinsdag 04 januari 2005 @ 21:29:
[...]

Er zal een query verzonnen moeten worden waarbij een where gebruikt kan worden en het resultaat zo klein mogelijk is, alleen dan is het nog te versnellen.

Als ik heel even offtopic mag gaan om een kleine vraag te stellen:
Is het bij zo'n grote tabel niet juist langzamer om ook nog een where te doen? Hierbij moet de hele (grote) tabel doorzocht worden én eventueel gesorteerd en daarna nog eens de resultaten teruggegeven worden. Bij een grote tabel hoeft niks gezocht te worden en wordt alles gewoon teruggegeven. Geen zoekwerk en geen sorteerwerk, aangezien alles op de primary key gesorteerd is.

[whoami]

Verwijderd schreef op woensdag 05 januari 2005 @ 13:49:
[...]

Is het bij zo'n grote tabel niet juist langzamer om ook nog een where te doen?

Niet als je filtercriteria in die WHERE een column behelst waar er een index op ligt, en als die index kan gebruikt worden.
Dan zal er nl. geen table scan gebeuren, maar een index seek. De index wordt dan gebruikt om de records die voldoen aan de criteria snel terug te vinden. Niet ieder record in de tabel moet overlopen worden.

Geen zoekwerk en geen sorteerwerk, aangezien alles op de primary key gesorteerd is.

Dat is niet altijd correct. Bij sommige databases kan je aangeven welk veld moet gebruikt worden om de fysieke opslag-volgorde te bepalen. Dit hoeft niet noodzakelijk de PK te zijn.

[Verwijderd]

Nee dat is in dit geval geen optie aangezien het veld waarop gezocht moet worden natuurlijk niet de PK is. De PK is namelijk uniek en dit veld kan juist dubbel zijn en die moeten eruit. Het lijkt me best vaak voorkomend probleem en de meest simpele weg, dus met een join, is natuurlijk een no go want dan doetie een carthesisch product in een tijdelijke tabel. Dat worden dan 45.000.000.000 records en dat kan mysql echt niet aan. Dat is trouwens ook meer dan een TB dus dat past niet op me server, en al helemaal niet als twee mensen tegelijk dit opvragen

Anyway, ik zoek me nog steeds suf maar er staat eigenlijk weinig over dit soort dingen. Het "code" veld ziet er trouwens zo uit:

pak4815AP9a

eerste drie letters achternaam - postcode - huisnummer - huisnummer toevoeging. Is hier niet iets mee te doen, opsplitsen, naar een int omzetten ofzo?

[whoami]

Een join die een cartesisch product veroorzaakt ?

[Creepy]

Ik vraag me af waarom je niet een UNIQUE constraint hebt geplaats op dat veld. Dan had je geen dubbelen gehad

[Verwijderd]

Creepy schreef op donderdag 06 januari 2005 @ 09:11:
Ik vraag me af waarom je niet een UNIQUE constraint hebt geplaats op dat veld. Dan had je geen dubbelen gehad

Het zit namelijk zo: Het is een tabel met persoonssgegevens. Er worden automatisch zogenaamde matchcodes gegenereerd zoals ik hierboven beschreven heb. Die codes zouden per persoon, of iig per huishouden uniek moeten zijn. Nou kan het gebeuren dat er mensen dubbel in staan, maar dan moet met de hand gekeken kunnen worden of mensen verhuisd zijn en zoja waarheen. (De matchcode wordt niet opnieuw gegenereerd bij verhuizen dus daar kan op gecheckt worden). UNIQUE gaat dus niet werken hierbij want dan kan je hem al niet invoeren. Ook met het checken tussen twee tabellen op dubbelen gaat dat niet werken want ze kunnen in principe in ieder van de tabellen uniek zijn, maar ze kunnen wel in beide tabellen voorkomen, en DIE moet ik hebben Snappie?

[Verwijderd]

whoami schreef op donderdag 06 januari 2005 @ 08:47:

Een join die een cartesisch product veroorzaakt ?

Als een join geen index goed kan gebruiken dan wordt het jointype "ALL" en dan wordt een carthesisch product gemaakt van het geheel. Dat is dus NIET goed

zie EXPLAIN

de query werkt dan hetzelfde als:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both will produce a Cartesian product between the specified tables (that is, each and every row in the first table will be joined onto all rows in the second table).

[ Voor 34% gewijzigd door Verwijderd op 06-01-2005 09:26 ]

[frickY]

Pleur een index op de `code` kolom en neem de bovengenoemde query;

code:

SELECT * 
FROM tabel
GROUP BY code
HAVING count(*) > 1

Zo selecteer je alle rijen waarvan de 'code' ook in andere rijen voorkomt.

[whoami]

Verwijderd schreef op donderdag 06 januari 2005 @ 09:23:
[...]


Als een join geen index goed kan gebruiken dan wordt het jointype "ALL" en dan wordt een carthesisch product gemaakt van het geheel. Dat is dus NIET goed

zie EXPLAIN

de query werkt dan hetzelfde als:


[...]

Een cartesisch product wordt veroorzaakt als je selecteert uit 2 of meerdere tabellen zonder dat je een goede JOIN clausule(s) hebt.
Enkel en alleen als je geen JOIN hebt, zal je dus een cartesisch product verkrijgen; dat heeft niets te maken met het al of niet aanwezig zijn van een index.