[MySql] Langzaam bij goedgevuld text-veld

[seweso]

Kan het zijn dat mysql erg traag is als je goed gevulde text-velden hebt? Ik heb nu 14.000 record met een x aantal simpele velden en een text-veld met gemiddeld 8 kb per record aan informatie.

Een drop-index mét het text-veld duurt 155 seconden en als ik het veld weg-gooi duurt het maar een seconde.

Een simpele query als "SELECT * FROM `klantkaart` LIMIT 14147 , 30" duurt met het gevreesde text-veld 11 seconden en zonder 0,03 seconden.

(een query op een heel specifiek record gaat in beide gevallen even snel)

- Moet ik deze teksten in een losse tabel opslaan?
- Moet ik mysql op linux draaien i.p.v. win2k?
- Moet ik een ander veld-type gebruiken i.pv. TEXT?
- Moet ik een ander tabel-type gebruiken?

Of moet ik meer info geven?

code:

#
# Tabel structuur voor tabel `klantkaart`
#

CREATE TABLE `klantkaart` (
  `id` int(20) NOT NULL auto_increment,
  `extid` varchar(15) NOT NULL default '',
  `omschrijving` varchar(128) NOT NULL default '',
  `telefoonnr` varchar(15) NOT NULL default '',
  `postcode` varchar(6) NOT NULL default '',
  `cpnaam` varchar(64) NOT NULL default '',
  `actie` varchar(10) NOT NULL default '',
  `plaats` varchar(64) NOT NULL default '',
  `afspraaktijd` datetime NOT NULL default '0000-00-00 00:00:00',
  `afspraaktijd2` varchar(25) NOT NULL default '',
  `afspraakstop` datetime NOT NULL default '0000-00-00 00:00:00',
  `vertegenwoordiger` varchar(5) NOT NULL default '',
  `xml` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `vertegenwooriger` (`vertegenwoordiger`),
  KEY `afspraaktijd` (`afspraaktijd`),
  KEY `extid` (`extid`),
  KEY `afspraakstop` (`afspraakstop`),
  KEY `actie` (`actie`),
  KEY `omschrijving` (`omschrijving`),
  KEY `telefoonnr` (`telefoonnr`),
  KEY `postcode` (`postcode`),
  KEY `plaats` (`plaats`)
) TYPE=MyISAM COMMENT='Bevat alle klantkaarten' AUTO_INCREMENT=14178 ;

Alvast bedankt

[ripexx]

Om meer inzicht te krijgen in je query's en het verloop ervan, kan je eventueel gebruik maken van de explain functie. De query wordt dan: EXPLAIN SELECT * etc etc.

Verder moet zo'n selectie geen probleem zijn. Hier op GoT worden volgens mij ook gewoon TEXT velden gebruikt voor de message tabel en daar staan meer dan 12 miljoen rcords in. Verder is de SELECT * FROM niet echt snel, sneller is om gebruik te maken van SELECT veld1, veld2 FROM.

Voor een select zou he niet zoveel uit moeten maken dat je aardig wat key's hebt. Ik weet trouwens alleen niet hoe mysql efficient omgaat met de LIMIT variant. Verder kan je nog kijken naar de instellingen van je server, is er genoeg geheugen beschikbaar enz enz.:)

[seweso]

ripexx schreef op 16 februari 2004 @ 10:09:
Om meer inzicht te krijgen in je query's en het verloop ervan, kan je eventueel gebruik maken van de explain functie. De query wordt dan: EXPLAIN SELECT * etc etc.

Echt veel nuttige info krijg je daar helaas niet mee.

Verder moet zo'n selectie geen probleem zijn. Hier op GoT worden volgens mij ook gewoon TEXT velden gebruikt voor de message tabel en daar staan meer dan 12 miljoen rcords in. Verder is de SELECT * FROM niet echt snel, sneller is om gebruik te maken van SELECT veld1, veld2 FROM.

Binnen mijn applicatie vraag ik altijd alleen de velden op die ik nodig heb. Ik snap dat als je SELECT * doet dat mysql dan telkens die 8kb aan info loopt op te halen.

Voor een select zou he niet zoveel uit moeten maken dat je aardig wat key's hebt. Ik weet trouwens alleen niet hoe mysql efficient omgaat met de LIMIT variant. Verder kan je nog kijken naar de instellingen van je server, is er genoeg geheugen beschikbaar enz enz.:)

Is er dan een alternatief voor LIMIT?

[ripexx]

Je zou kunnen kijken of je met een where betere resultaten haalt, al is het alleen maar om te kijken of daar ook een bottleneck zit. Ik blijf het een raar probleem vinden, aangezien je text velden gewoon door de select worden opgehaald zonder dat je enige functies, where clauses etc er op los laat.

De hoeveelheid data is nou ook weer niet extreem en moet opzich geen problemen vormen.. 8kb is niets, blob velden kunnen veel meer bevatten en dat zou ook moeten werken.

[djexplo]

Een drop-index mét het text-veld duurt 155 seconden en als ik het veld weg-gooi duurt het maar een seconde.

Ik zou zeggen, exporteer deze database eens en gooi dan je hele mysqldatabase weg, en importeer de geexporteerde database weer. Dan heb je fouten in de database zelf gerepareerd want 155 seconden is wel heel lang.

Probeer verder de database eens uit op een ander systeem of mysql versie.

Op wat voor systeem en versie draait die database nu ?

[ripexx]

djexplo schreef op 16 februari 2004 @ 13:55:
[...]
Ik zou zeggen, exporteer deze database eens en gooi dan je hele mysqldatabase weg, en importeer de geexporteerde database weer. Dan heb je fouten in de database zelf gerepareerd want 155 seconden is wel heel lang.

Opzich moet zo'n drop index niet het probleem zijn, al doet die er een uur over [understatement!!]

Het verschil tussen een tabel met of zonder text veld met dezelfde query geeft een te groot verschil 11s tov 0,03s Dat is dan meer dan 300x zo langzaam

Probeer verder de database eens uit op een ander systeem of mysql versie.

Op wat voor systeem en versie draait die database nu ?

Opzich zit er wel iets in, want alle indexen en datafiles worden dan opnieuw aangemaakt.

[_Sunnyboy_]

Waarom heb je trouwens in hemelsnaam zoveel indexen? MySQL kan per query maar één index gebruiken, dus als je in je WHERE meerdere kolommen noemt dan kan je het best een index over meerdere kolommen maken.

Zoals ripexx al zei is het misschien verstandig om eens EXPLAIN bij al je queries te doen om nou eens te zien welke van die indexen ook daadwerkelijk gebruikt wordt.

http://www.mysql.com/doc/en/MySQL_indexes.html voor meer info over hoe mysql indexes gebruikt.

[seweso]

djexplo schreef op 16 februari 2004 @ 13:55:
[...]


Ik zou zeggen, exporteer deze database eens en gooi dan je hele mysqldatabase weg, en importeer de geexporteerde database weer. Dan heb je fouten in de database zelf gerepareerd want 155 seconden is wel heel lang.

Probeer verder de database eens uit op een ander systeem of mysql versie.

Op wat voor systeem en versie draait die database nu ?

Ja dat exporteren dat duur ook een eeuwigheid en het lijkt me een beetje onzinnig als ik de database net heb aangemaakt. Ik kan namelijk de database weggooien en dan lokaal een export-programma draaien die automatisch de database weer aanmaakt en de xml data upload.

Verder draait alles nu onder windows 2000 (zoals ik al zei ). Ik ben namelijk bezig een webserver die onder windows 2000 draait te vervangen door het magische trio (apache, php, mysql). Maar het was de bedoeling dat de site er sneller en stabieler van zou worden. Als ik de server omgooi naar linux dan moet ik er zeker van zijn dat alles dan soepel gaat lopen.

[seweso]

_Sunnyboy_ schreef op 16 februari 2004 @ 17:17:
Waarom heb je trouwens in hemelsnaam zoveel indexen? MySQL kan per query maar één index gebruiken, dus als je in je WHERE meerdere kolommen noemt dan kan je het best een index over meerdere kolommen maken.

Zoals ripexx al zei is het misschien verstandig om eens EXPLAIN bij al je queries te doen om nou eens te zien welke van die indexen ook daadwerkelijk gebruikt wordt.

http://www.mysql.com/doc/en/MySQL_indexes.html voor meer info over hoe mysql indexes gebruikt.

Ik zeg maar zo: beter te veel indexen dan te weinig . Maar ff serieus, ik heb altijd geleerd dat het updaten van records door indexen langzamer word en dat het opvragen van data er alleen maar sneller van kan worden.

[Guru Evi]

Als dat ding draait onder een Windows die al belast is kan het zijn dat het redelijk traag gaat. Hangt ook af van je algemeen systeem. Kun je geen native Linux systeem installeren en daar eens testen?

[seweso]

Guru Evi schreef op 19 februari 2004 @ 12:39:
Als dat ding draait onder een Windows die al belast is kan het zijn dat het redelijk traag gaat. Hangt ook af van je algemeen systeem. Kun je geen native Linux systeem installeren en daar eens testen?

Er draait verder niets op (dat kun je natuurlijk nooit van windows zeggen omdat ze daar bij microsoft bedacht hebben om allerlei services standaard te installeren).

En ik hoopte eigenlijk dat iemand een linux server in de aanbieding had

Blijkbaar heeft niemand hier ervaring mee... ik zal toch maar eens bij de provider te rade gaan.

[seweso]

We gaan binnenkort dit onder linux testen maar ik denk niet dat het probleem daarmee opgelost is. Ik ben er ondertussen erachter dat hij langzaam is als er geen index gebruikt word. Dan zul je wel zeggen dat dat logisch is, maar mysql is juist normaliter ook heel snel zonder indexen. Zie dit document voor meer info. Want zoals het er nu voor staat denk ik dat ik de text-velden en de overige velden in twee aparte tabellen moet zetten.

De reden waarom ik denk dat dit gewoon komt door de manier waarop mysql de text-gegevens opslaat. Het lijkt erop dat mysql variabele record groottes gebruikt bij text velden. Daardoor staan records ver uit elkaar op de disk en kost het dus veel moeite om alle records door te lopen....

[Olaf van der Spek]

seweso schreef op 08 maart 2004 @ 10:16:
De reden waarom ik denk dat dit gewoon komt door de manier waarop mysql de text-gegevens opslaat. Het lijkt erop dat mysql variabele record groottes gebruikt bij text velden. Daardoor staan records ver uit elkaar op de disk en kost het dus veel moeite om alle records door te lopen....

Dat doet elke DB toch?
Als je variable-sized fields hebt, dan heb je ook variable-sized records.

[beetle71]

Ik vind dit vreemd, ik heb ook een aantal databases draaien met vergelijkbare hoeveelheden data en records, daarbij heb ik geen problemen met snelheid.
Ik vermoed dat het om een heel ineffecient gebruik van de limit door MYSQL gaat.

Waarom wil je een query als:

code:

"SELECT * FROM `klantkaart` LIMIT 14147

doen?
Ik bedoel waarom wil je 14147 records in 1 keer ophalen? Als deze allemaal gevuld zijn zit je excl. de mysql overhead al snel aan ruim 100 Mb... Dus dat kan inderdaad wel ff duren.

Wat gebeurt er als je een WHERE id IN ( 1,2,3,4,5,6,7,8,...........,23,24) gebruikt?

[seweso]

beetle71 schreef op 08 maart 2004 @ 10:31:
Ik vind dit vreemd, ik heb ook een aantal databases draaien met vergelijkbare hoeveelheden data en records, daarbij heb ik geen problemen met snelheid.
Ik vermoed dat het om een heel ineffecient gebruik van de limit door MYSQL gaat.

Waarom wil je een query als:

code:

"SELECT * FROM `klantkaart` LIMIT 14147

doen?
Ik bedoel waarom wil je 14147 records in 1 keer ophalen? Als deze allemaal gevuld zijn zit je excl. de mysql overhead al snel aan ruim 100 Mb... Dus dat kan inderdaad wel ff duren.

Dat wil ik ook niet (zei ik dat? )

Wat gebeurt er als je een WHERE id IN ( 1,2,3,4,5,6,7,8,...........,23,24) gebruikt?

SELECT * FROM `klantkaart` WHERE id IN ( 1,2,3,4,5,6,7,8,23,24) duurt 0.0698 sec. Oftewel ik heb geen problemen als er een index gebruikt word.

OlafvdSpek schreef op 08 maart 2004 @ 10:29:
[...]

Dat doet elke DB toch?
Als je variable-sized fields hebt, dan heb je ook variable-sized records.

Zover was ik net ook, het gaat erom dat als je bepaalde velden gebruikt je opeens een Dynamic Table hebt... Wat ik dus wil is eigenlijk een static table met de text velden in een soort van apart bestand (net zoals foxpro dat bijvoorbeeld doet).

[Olaf van der Spek]

seweso schreef op 08 maart 2004 @ 11:05:
Zover was ik net ook, het gaat erom dat als je bepaalde velden gebruikt je opeens een Dynamic Table hebt... Wat ik dus wil is eigenlijk een static table met de text velden in een soort van apart bestand (net zoals foxpro dat bijvoorbeeld doet).

Dan moet je dat veld handmatig in een andere tabel zetten en handmatig joinen. MySQL kan dat (nog) niet automatisch.

Ik ben bang dat MySQL de query gewoon zonder limit uitvoert en dan pas de limiet toepast. Dan heb je dus al 100 mb data van disk gelezen en dat kan de tijd verklaren.

[ Voor 15% gewijzigd door Olaf van der Spek op 08-03-2004 11:23 ]

[seweso]

OlafvdSpek schreef op 08 maart 2004 @ 11:22:
[...]

Dan moet je dat veld handmatig in een andere tabel zetten en handmatig joinen. MySQL kan dat (nog) niet automatisch.

Als ik even geen work-around hoef te gebruiken dan zou ik erg blij zijn. Als het dan toch moet dan maak ik waarschijnlijk een mooie laag erbovenop.

[ACM]

* ACM ziet e.e.a. aan onzinclaims in dit topic.
Ik moet er wel gelijk zeggen dat ik niet alles goed gelezen heb

seweso schreef op 15 februari 2004 @ 21:43:
Kan het zijn dat mysql erg traag is als je goed gevulde text-velden hebt?

Nooit wat van gemerkt, en we hebben hier toch aardig wat text-velden

Een drop-index mét het text-veld duurt 155 seconden en als ik het veld weg-gooi duurt het maar een seconde.

Mysql maakt een copy van je tabel met de nieuwe definitie (volgens mysql is een index wijziging ook een definitie-wijziging) en kopieert dan alle data naar de nieuwe tabel en mikt dan de oude weg (met een myisam tabel is dat zelfs een eenvoudige delete).
Zodra je 100MB aan tekstdata er is, zal dat wel even duren. Als je juist die data wegmikt hoeft ie uiteraard veel minder te kopieren en is het zo gepiept.

Een simpele query als "SELECT * FROM `klantkaart` LIMIT 14147 , 30" duurt met het gevreesde text-veld 11 seconden en zonder 0,03 seconden.

Hierboven stond het al ergens, als je 14147 x 8KB aan data selecteert, zit je op ruim 110MB aan data, dat zal wel even duren.

(een query op een heel specifiek record gaat in beide gevallen even snel)

Wat dan ook de reden is dat je niet domweg je complete tabel moet selecteren, de complete 110MB, en verwachten dat het super snel is.

- Moet ik deze teksten in een losse tabel opslaan?

Dat hangt er vanaf, waarom wil je ze perse alle 14K meeselecteren? Als je ze in een aparte tabel stopt en dan meeselecteert zal het zelfs nog iets langer duren.

- Moet ik mysql op linux draaien i.p.v. win2k?

Helpt je dusdanig marginaal dat je eerst naar je eigen denkprocess moet kijken (het "waarom je in godsnaam al die data wilt" )

- Moet ik een ander veld-type gebruiken i.pv. TEXT?

Maakt niks uit

- Moet ik een ander tabel-type gebruiken?

Maakt weinig uit

Of moet ik meer info geven?

Wellicht de motivatie waarom je al die data wilt selecteren
Maar ik geloof dat je die al had gegeven hier?

Als je het textveld niet continue nodig hebt, niet meeselecteren. Als je niet alle velden steeds nodig hebt, niet alle velden selecteren. En als je niet alle records nodig hebt, niet alle records selecteren.

[beetle71]

OlafvdSpek schreef op 08 maart 2004 @ 11:22:
[...]

Dan moet je dat veld handmatig in een andere tabel zetten en handmatig joinen. MySQL kan dat (nog) niet automatisch.

Ik ben bang dat MySQL de query gewoon zonder limit uitvoert en dan pas de limiet toepast. Dan heb je dus al 100 mb data van disk gelezen en dat kan de tijd verklaren.

Okee, ik ga ervan uit dat je mbv een LIMIT een stuk of wat records uit het midden van je select * wilt ophalen en dat het door een inefficiente limit?! te lang duurt. Misschien dat je de tabel dan kunt self joinen..

code:

SELECT t1.id, t2.* FROM table as t1 LEFT OUTER JOIN table as t2 ON t1.id=t2.id LIMIT 600,10

Hiervan weet ik niet hoe MYSQL dit oplost. Als ie eerst alles joined dan schiet je er niks mee op. Als ie eerst de hoofdtable ophaalt en limiteerd en dan pas joined, zou je er wel tijd mee kunnen winnen.

In het allerergste geval zou je eerst een SELECT ID FROM ... LIMIT x,y kunnen doen en dan in een tweede querie een WHERE IN gebruiken.

[ Voor 9% gewijzigd door beetle71 op 08-03-2004 11:57 ]

[seweso]

Hierboven stond het al ergens, als je 14147 x 8KB aan data selecteert, zit je op ruim 110MB aan data, dat zal wel even duren.

Ik wil absoluut niet alle gegevens overpompen, dat duurt uiteraard lang.

Voor alle duidelijkheid "SELECT * FROM `klantkaart` LIMIT 14147" is niet hetzelfde als "SELECT * FROM `klantkaart` LIMIT 14147, 30"! Bij de laatste selecteer je 30 records beginnende bij record 14147 (waarbij mysql standaard sorteert op de primaire sleutel).


Ik wil zoeken m.b.v `veld` LIKE '%bla%' zonder dat je daar lang op hoeft te wachten. Zonder text-veld (dus een static-table) is dat snel en met een goed gevuld text-veld is hij langzaam. Ik snap dat er dan géén index word gebruikt, dus word de hele tabel afgescand en moet ie dus door 110 mb aan data ploegen. Maar dat laatste is volgens mij nergens voor nodig.

[ Voor 14% gewijzigd door seweso op 08-03-2004 12:29 . Reden: Ik snap... ]

[beetle71]

seweso schreef op 08 maart 2004 @ 12:21:
[...]

Ik wil absoluut niet alle gegevens overpompen, dat duurt uiteraard lang.

Voor alle duidelijkheid "SELECT * FROM `klantkaart` LIMIT 14147" is niet hetzelfde als "SELECT * FROM `klantkaart` LIMIT 14147, 30"! Bij de laatste selecteer je 30 records beginnende bij record 14147 (waarbij mysql standaard sorteert op de primaire sleutel).


Ik wil zoeken m.b.v `veld` LIKE '%bla%' zonder dat je daar lang op hoeft te wachten. Zonder text-veld (dus een static-table) is dat snel en met een goed gevuld text-veld is hij langzaam.

Ik snap het even niet, je wilt een LIKE gebruiken op het textveld, maar zegt vervolgens dat hij zonder textveld snel is, en met textveld langzaam....
Ik neem aan dat je begrijpt dat dat héél logisch is

Als je overigens snel wilt zoeken in TEXT velden moet je eens naar de mogelijkheden van een FULLTEXT index in MYSQL.

Als je bedoelt dat je over andere velden wilt zoeken maar toch het TEXT veld mee wilt selecteren doe je er volgens mij verstandig aan alle kolomnamen expliciet te noemen, en evt. de methodes die ik hierboven noemde toe te passen.

[seweso]

beetle71 schreef op 08 maart 2004 @ 13:28:
[...]


Ik snap het even niet, je wilt een LIKE gebruiken op het textveld, maar zegt vervolgens dat hij zonder textveld snel is, en met textveld langzaam....
Ik neem aan dat je begrijpt dat dat héél logisch is

Als je overigens snel wilt zoeken in TEXT velden moet je eens naar de mogelijkheden van een FULLTEXT index in MYSQL.

Als je bedoelt dat je over andere velden wilt zoeken maar toch het TEXT veld mee wilt selecteren doe je er volgens mij verstandig aan alle kolomnamen expliciet te noemen, en evt. de methodes die ik hierboven noemde toe te passen.

Die fulltext index had ik al geprobeerd alleen was ik er van uit gegaan dat dan de like vergelijking versneld zou worden...

Woehoe hij is zelfs sneller! Thanx!


Als je het interesseert:
Toon Records 0 - 2 (3 totaal, Query took 0.0022 sec)

SQL-query : [Wijzigen] [Verklaar SQL] [Creëer PHP Code]
SELECT id, omschrijving, cpnaam,
MATCH (
cpnaam
)
AGAINST (
'Kamminga'
) AS score
FROM test_klantkaart
WHERE MATCH (
cpnaam
)
AGAINST (
'Kamminga'
) LIMIT 0 , 30


Ik blijf het vreemd vinden dat mysql de gegevens met variabele veld-lengtes niet in een apart bestand zet, maar ja ik gebruik voortaan gewoon altijd goede indexen.

Ik gebruikte trouwens geen indexen omdat 'iedereen' zei dat mysql zelfs snel was zonder indexen ... dus voortaan niet meer luisteren naar dat soort mensen.

[Olaf van der Spek]

seweso schreef op 08 maart 2004 @ 12:21:
Voor alle duidelijkheid "SELECT * FROM `klantkaart` LIMIT 14147" is niet hetzelfde als "SELECT * FROM `klantkaart` LIMIT 14147, 30"! Bij de laatste selecteer je 30 records beginnende bij record 14147 (waarbij mysql standaard sorteert op de primaire sleutel).

Is dat zo? Of is het gewoon toeval dat de primary key wordt gebruikt?

[djluc]

Het wordt gewoon gebaseerd op de volgorde van de records, dus als je sorteerd op een ander veld krijg je andere records terug.

[Olaf van der Spek]

djluc schreef op 08 maart 2004 @ 15:51:
Het wordt gewoon gebaseerd op de volgorde van de records, dus als je sorteerd op een ander veld krijg je andere records terug.

Natuurlijk, maar wat als je niet sorteerd? Is de output dan undefined of wordt er dan inderdaad implicitiet op primary ket gesorteerd?

[djluc]

OlafvdSpek schreef op 08 maart 2004 @ 17:02:
[...]Natuurlijk, maar wat als je niet sorteerd? Is de output dan undefined of wordt er dan inderdaad implicitiet op primary ket gesorteerd?

Undefined, je mag er nooit van uit gaan dat je ze op een bepaalde volgorde terug krijgt. Toch blijkt dat je vaak gesorteerd op primary key terug krijgt. Maar dit hoeft dus niet altijd zo te zijn.

[The Lord]

Blijkbaar heb je al een oplossing voor je probleem gevonden; wat dat betreft is deze reply niet echt nuttig meer denk ik. Maar voor de mensen die je topic tegenkomen alsnog het volgende :

• * Gebruik goed doordachte indexes. * Gebruik zo min mogelijk SELECT * FROM; gespecificeerde velden i.p.v. * is vrijwel altijd sneller. * Gebruik nooit een LIKE beginnend met % zoals LIKE '%bla%'; een dergelijke query is door de eerste % niet indexable.

Het mag duidelijk zijn waarom een fulltext sneller is op grote hoeveelheden data vergeleken met een LIKE '%bla%' query. De fulltext maakt namelijk een tabel met indexes aan per woord. In plaats van table scans wordt er nu dus een index gebruikt.

edit:
Edit was voor ik reactie van OlafvdSpek had gezien; don't blame him!

[ Voor 31% gewijzigd door The Lord op 08-03-2004 17:23 ]

[Olaf van der Spek]

Wat zou je in plaats van like "% dan aanraden?

[mjax]

Voor de TS. Geef eens wat meer informatie over je systeem. Ik draai hier een tabel op FreeBSB 4.9 met MySQL 4.0.x, die 16MB aan data in LONGTEXT velden bevat. Een SELECT * op deze tabel neemt 0.02 sec. in beslag. Dit alles draait op een dual P4 Xeon met 1 GB RAM.

[ACM]

seweso schreef op 08 maart 2004 @ 12:21:
Ik wil absoluut niet alle gegevens overpompen, dat duurt uiteraard lang.

Voor alle duidelijkheid "SELECT * FROM `klantkaart` LIMIT 14147" is niet hetzelfde als "SELECT * FROM `klantkaart` LIMIT 14147, 30"! Bij de laatste selecteer je 30 records beginnende bij record 14147 (waarbij mysql standaard sorteert op de primaire sleutel).

Klopt, heb ik een beetje overheengelezen. Niettemin wordt alsnog je complete dataset geprocessed als ie wat met elk record moet doen (die %bla%-like van je bijv), als mysql niet zeker kan zijn (bijv door een index) dat alleen een paar records voldoen, dan zal ie ze allemaal af moeten gaan. Ook al vind jij dat dat niet zo is

Ik wil zoeken m.b.v `veld` LIKE '%bla%' zonder dat je daar lang op hoeft te wachten. Zonder text-veld (dus een static-table) is dat snel en met een goed gevuld text-veld is hij langzaam. Ik snap dat er dan géén index word gebruikt, dus word de hele tabel afgescand en moet ie dus door 110 mb aan data ploegen. Maar dat laatste is volgens mij nergens voor nodig.

Je moet toch wat duidelijker uitleggen waarom dat niet nodig zou zijn, als jij alle gegevens uit een tabel wilt hebben waar %bla% inzit (althans, dat specifieer je blijkbaar zo) dan zul je toch ook echt alle velden die potentieel %bla% kunnen bevatten moeten afgrazen. Als je alleen die velden die in het primary-keybereik [14147, 14177] zit wilt hebben, zal je dat expliciet op moeten geven en dat doe je niet met je limit

OlafvdSpek schreef op 08 maart 2004 @ 15:29:
Is dat zo? Of is het gewoon toeval dat de primary key wordt gebruikt?

MySQL heeft een default sort-key waar ie altijd op sorteert als je niks opgeeft, die key is te wijzigen, maar standaard is het de primary key.

OlafvdSpek schreef op 08 maart 2004 @ 17:02:
Natuurlijk, maar wat als je niet sorteerd? Is de output dan undefined of wordt er dan inderdaad implicitiet op primary ket gesorteerd?

De SQL-specificatie zegt "ongedefinieerd" (en veel db's doen het dus op record-volgorde).

seweso schreef op 08 maart 2004 @ 13:33:
Ik blijf het vreemd vinden dat mysql de gegevens met variabele veld-lengtes niet in een apart bestand zet, maar ja ik gebruik voortaan gewoon altijd goede indexen.

Dat is nog steeds een nutteloze opmerking "dat ie ze niet in een apart bestand zet", waarom zou ie dat moeten doen?
Daar wordt het niks sneller van hoor, je query was gewoon zodanig geformuleerd dat MySQL geen efficiente uitvoering er voor kon bedenken en/of dat ie er niet was.

Ik gebruikte trouwens geen indexen omdat 'iedereen' zei dat mysql zelfs snel was zonder indexen ... dus voortaan niet meer luisteren naar dat soort mensen.

Dan moet je die 'iedereen' heel hard slaan, want dat is de meest onzinnige uitspraak die ik ooit gehoord heb. Voor sommige tabellen (laten we zeggen tot 100 records) kan het best waar zijn, maar zodra het een beetje een fatsoenlijke grootte begint te krijgen (laten we zeggen meer dan 10.000) is het absoluut verstandig _wel_ een index te hebben op elk veld waar je specifiek in zoekt. Maar dan uiteraard wel verstandig en met mate, want teveel indices is ook niet goed.

Btw, verwar "full text index" niet met wat mysql vaak "key" noemt, dat laatste is een index, dat eerste een speciale extensie van mysql die een term-tekstveld-map aanmaakt.

[Olaf van der Spek]

ACM schreef op 08 maart 2004 @ 18:11:
MySQL heeft een default sort-key waar ie altijd op sorteert als je niks opgeeft, die key is te wijzigen, maar standaard is het de primary key.

Docs?
En altijd, is dat alleen met een 'limited' query of echt altijd?

[ Voor 3% gewijzigd door Olaf van der Spek op 08-03-2004 19:31 ]

[ACM]

OlafvdSpek schreef op 08 maart 2004 @ 19:22:
Docs?
En altijd, is dat alleen met een 'limited' query of echt altijd?

Hmm, raar, ik meende dat het zo was en dat ik het zelfs in de mysql-docs had gelezen, ooit es. Magoed, iig versie 4.0.17 doet het netjes volgens de standaard (oftewel, geen ordering).

[seweso]

OlafvdSpek schreef op 08 maart 2004 @ 15:29:
[...]

Is dat zo? Of is het gewoon toeval dat de primary key wordt gebruikt?

Per tabel word opgeslagen wat de default order by clausule is.

Kijk maar eens wat er gebeurt als je: ALTER TABLE `tabel` ORDER BY `veld`; doet op een tabel

mjax schreef op 08 maart 2004 @ 17:22:
Voor de TS. Geef eens wat meer informatie over je systeem. Ik draai hier een tabel op FreeBSB 4.9 met MySQL 4.0.x, die 16MB aan data in LONGTEXT velden bevat. Een SELECT * op deze tabel neemt 0.02 sec. in beslag. Dit alles draait op een dual P4 Xeon met 1 GB RAM.

Ik heb geen flauw benul eigenlijk, dat heb ik uitbesteed. Tijdens dit schrijven heb ik op meerdere pc's getest dus aan de harware zal het niet liggen. Daarnaast heb ik het hier over verhoudingen (het één is x keer sneller dan het ander).

ACM schreef op 08 maart 2004 @ 18:11:
...
Je moet toch wat duidelijker uitleggen waarom dat niet nodig zou zijn, als jij alle gegevens uit een tabel wilt hebben waar %bla% inzit (althans, dat specifieer je blijkbaar zo) dan zul je toch ook echt alle velden die potentieel %bla% kunnen bevatten moeten afgrazen. Als je alleen die velden die in het primary-keybereik [14147, 14177] zit wilt hebben, zal je dat expliciet op moeten geven en dat doe je niet met je limit

Ik bedoel dat mysql onnodig door de text velden moet grazen voor het resultaat

MySQL heeft een default sort-key waar ie altijd op sorteert als je niks opgeeft, die key is te wijzigen, maar standaard is het de primary key.

Dat had ik net ook opgeschreven, maar met al die mensen hier die daar anders over dachten mag dat wel twee keer gezegd worden idd (drie keer als je deze quote meeteld)

Dat is nog steeds een nutteloze opmerking "dat ie ze niet in een apart bestand zet", waarom zou ie dat moeten doen?
Daar wordt het niks sneller van hoor, je query was gewoon zodanig geformuleerd dat MySQL geen efficiente uitvoering er voor kon bedenken en/of dat ie er niet was.

Mijn post begon juist dat ie zonder text veld zo'n 500 keer sneller was, dus als het text veld apart opgeslagen word is hij ook 500 keer sneller.

Dan moet je die 'iedereen' heel hard slaan, want dat is de meest onzinnige uitspraak die ik ooit gehoord heb. Voor sommige tabellen (laten we zeggen tot 100 records) kan het best waar zijn, maar zodra het een beetje een fatsoenlijke grootte begint te krijgen (laten we zeggen meer dan 10.000) is het absoluut verstandig _wel_ een index te hebben op elk veld waar je specifiek in zoekt. Maar dan uiteraard wel verstandig en met mate, want teveel indices is ook niet goed.

Btw, verwar "full text index" niet met wat mysql vaak "key" noemt, dat laatste is een index, dat eerste een speciale extensie van mysql die een term-tekstveld-map aanmaakt.

Ik vind 0.0636 sec best redelijk voor 19.000 records, vooral als je bedenkt dat dat eigenlijk de enige manier is waarop je "%bla%" kan vinden. En nu zijn we volgens mij weer bij het originele probleem uitgekomen waar eigenlijk nog steeds niet de perfecte oplossing voor is gegeven.


Voor de mensen die het specifieke voorbeeld hebben gemist:
Lees dit document voor alle informatie over het 'probleem'.

[ACM]

seweso schreef op 08 maart 2004 @ 23:12:
Voor de mensen die het specifieke voorbeeld hebben gemist:
Lees dit document voor alle informatie over het 'probleem'.

Ah, ik snap eindelijk je probleem... Volgende keer toch even wat beter lezen

Je hebt het veld xml dat je verder niet gebruikt en je ook niet je like-search op doet... En die zit je zo in de weg, omdat je vergeten bent dat je tabel veel groter wordt van dat veld en dus je full-table-scan veel langer duurt (ipv dat ie eerst door een paar MB moet lopen, moet ie dan ineens door meer dan 100MB lopen).
Velden worden gewoon inline opgeslagen en daardoor is het inderdaad trager op het moment dat je text-veld erbij zit.

Je doet inderdaad niks met het veld en daardoor zou het, om deze reden, handig kunnen zijn het veld in een andere tabel te gooien, omdat je daardoor bewerkingen op de originele tabel wat vlotter uit kan voeren. Normaal, met een indexeerbare query, boeit het allemaal niet zo, omdat je dan geen complete table-scan uitvoert, maar met jouw like-query boeit dat toevallig wel. De oplossing voor een full text index is wellicht net zo bruikbaar, maar dat moet je zelf even uitzoeken.

Sowieso is het onverstandig om in een tabel van meer dan een paar duizendrecords een like '%bla%' te doen, ongeacht of dat "snel" is of niet (want het is nog altijd veel langzamer dan via een index en zeker bij veel queries/minuut ga je dat echt merken).

Er is trouwens geen default ordering op mysql-tabellen, die order by wordt namelijk niet bijgehouden.

[Olaf van der Spek]

ACM schreef op 08 maart 2004 @ 22:57:
Hmm, raar, ik meende dat het zo was en dat ik het zelfs in de mysql-docs had gelezen, ooit es. Magoed, iig versie 4.0.17 doet het netjes volgens de standaard (oftewel, geen ordering).

De standaard was toch undefined? Sortering volgende primary key voldoet aan undefined hoor.

[Olaf van der Spek]

seweso schreef op 08 maart 2004 @ 23:12:
Per tabel word opgeslagen wat de default order by clausule is.

Kijk maar eens wat er gebeurt als je: ALTER TABLE `tabel` ORDER BY `veld`; doet op een tabel

Ik lees liever gewoon de handleiding:

ORDER BY allows you to create the new table with the rows in a specific order. Note that the table will not remain in this order after inserts and deletes. In some cases, it may make sorting easier for MySQL if the table is in order by the column that you wish to order it by later. This option is mainly useful when you know that you are mostly going to query the rows in a certain order; by using this option after big changes to the table, you may be able to get higher performance.