Ik heb een tabel van 127 miljoen records. 5 kolommen, 4 maal -> INT(9) en 1 maal -> VARCHAR(255)
Ze zijn allemaal geindexeerd. Maar als ik bijv een query als dit uitvoer
SELECT * FROM `spec_product` where TRIM(waarde) = '&;' group by waarde limit 10
de INDEXen
Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Edit Drop Drop PRIMARY BTREE Yes No id 126157109 A No
Edit Edit Drop Drop child_id BTREE Yes No child_id 151631 A No
waarde 12615710 A No
product_id 126157109 A No
Edit Edit Drop Drop ind BTREE No No product_id 21026184 A No
waarde 126157109 A No
header_id 126157109 A No
child_id 126157109 A No
Edit Edit Drop Drop waarde BTREE No No waarde 14017456 A No
Edit Edit Drop Drop product_id BTREE No No product_id 18022444 A No
Edit Edit Drop Drop header_id BTREE No No header_id 55723 A No
Dan krijgt hij de foutmelding in de titel.
my.cnf is dit:
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
innodb_lock_wait_timeout = 100
key_buffer_size = 3G
max_allowed_packet = 128M
innodb_buffer_pool_size=6G
wait_timeout=28800
interactive_timeout = 28800
innodb_log_file_size = 228M
datadir = /var/lib/mysql
Wat zegt het error log van je MySQL server?
Je kan ook tijdens het draaien van die query eens in de gaten houden wat je geheugengebruik doet (`htop` is daar erg handig voor).
SELECT * FROM `spec_product`
where TRIM(waarde) = '&;'
group by waarde
limit 10
Group-by gebruik je in combinatie met aggregatie functies, zoals sum(), min(), count().
Bijvoorbeeld om te achterhalen dat er 10 auto's in je tabel staan en 4 fietsen.
Mysql accepteert bij bepaalde settings, dat je toch group-by in een query duwt, en probeert er dan iets van te maken, waarbij soms aanwijsbare onzin het resultaat is (ik zoek zo nog eens naar een tutorial die ik lang geleden eens maakte).
Soms is het effect van group-by in deze setup in mysql dat er een soort van uniek wordt afgedwongen. Wat je ook met DISTINCT had bereikt.
Maar aangezien je al in de query de waarde van de kolom Waarde op &; zet (even vanuit gaande dat TRIM() niets toevoegt), lijkt dat ook niet logisch.
kortom:
Wat doet je query met
SELECT * FROM `spec_product`
where TRIM(waarde) = '&;'
limit 10
En beter nog: zijn er rijen in je tabel waar ook echt spaties voor of achter je Waarde staan?
Nu laat je Mysql eerst die 127M rijen langs gaan om eventuele spaties te verwijderen van Waarde en daarna kijkt hij of er &; overbleef.
En zit hier nog een index op?
?Onbekende gebruiker
08-06-2021 16:33
En MySQL sorteert ook nog eens alle GROUP BY kolommen...
If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns.
SELECT * FROM `spec_product`
where TRIM(waarde) = '&;'
limit 10
Dit heb ik gedaan. Hetzelfde probleem. #2006 - MySQL server has gone away.
Nu laat je Mysql eerst die 127M rijen langs gaan om eventuele spaties te verwijderen van Waarde en daarna kijkt hij of er &;
Ja ik hoopte dat dit nog steeds mogelijk was, maar dit duurt idd te lang.
En beter nog: zijn er rijen in je tabel waar ook echt spaties voor of achter je Waarde staan?
Nu laat je Mysql eerst die 127M rijen langs gaan om eventuele spaties te verwijderen van Waarde en daarna kijkt hij of er &; overbleef.
Ja, die zijn er echt wandaar de TRIM. Maar zonder trim, doet hij het wel snel. Maar hij vind ze dan niet allemaa;
SELECT * FROM `spec_product`
where waarde like '%&;%'
limit 10
Dit duurt ook te lang maar hij stopt er niet mee 86 seconde.
Bij vele rijen zitten al vele spaties, links en rechts van een waarde.
Als ik deze spaties allemaal wil trimmen, gaat mysql ook weg. De database lijkt te groot.
Desalniettemin. Ik weet dat vele waardes hetzelfde zijn. daarom wil ik die waarde in een andere tabel zetten voor normalizatie. en zo de tabelgrootte te beperken.
?Onbekende gebruiker
08-06-2021 22:34
Als je een eenmalige actie wilt doen, waarom gebruik je dan phpMyAdmin met time-outs op een PHP script en de webserver? Kan je niet gewoon via de CLI de mysql client starten, die maakt z'n klus gewoon af.
Over je query: LIKE '%naald%' duurt lang omdat er geen index gebruikt kan worden, dat kan alleen met LIKE 'naald%', dus zonder dat het begint met een procentteken.
heb je die spaties rond Waarde echt nodig? Of zijn ze er bij het invoeren ingeslopen, en zitten ze nu eigenlijk alleen maar in de weg?
UPDATE tabel SET waarde = TRIM(waarde);
eventueel in moten opgedeeld met WHERE waarde LIKE ' %' of '% '
en eventueel op id eindigt op 1, 2 , 3 etc
Maar via een niet-php oplossing (command line, HeidiSQL etc) moet je ook langdurende query's uit kunnen voeren.
Hiermee schoon je dan eenmalig je data op, en daarna zou je er wat aan hebben.
Kijk ook naar de invoerkant, want kennelijk is er een proces dat extra spaties aanlevert.
