DIG Digital Corner
Alles wat je altijd al over computers, hardware, software, internet en elektronische gadgets had willen weten, maar niet op Tweakers.net durft te vragen.
Alle .id's zijn geindexeerd (tenminste qua opzet).
fish_id is het type vis, beetje stomme noemer, zal deze tzt overal renamen qua type!
fish_id is het type vis, beetje stomme noemer, zal deze tzt overal renamen qua type!
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Als alle genoemde velden geïndexeerd staan, kan het nog zijn dat MySQL denkt weinig voordeel te halen bij een index. Je kunt dan FORCE INDEX gebruiken op de indices die ik noemde. Met name door de LIMIT denk ik dat je daar veel voordeel door haalt op de fish-tabel (4 rijen ophalen ipv allemaal).
Bij media zou ik verwachten dat MySQL de index wel uit zichzelf benut.
Bij media zou ik verwachten dat MySQL de index wel uit zichzelf benut.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Dat had ik idd ook verwacht maar helaas, ook heb ik net geprobeerd om alle _id's te indexeren maar dat resulteerde ook niet in een snellere site... het tegenovergestelde zelfs 
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Zeg eens welke velden nu allemaal geïndexeerd staan (of beter, geef je CREATE TABLE query), en toon EXPLAIN eens met die FORCE INDEX.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Ik heb even een create table dump gemaakt:
http://www.bruggema.nl/tables.sql
Dit zijn alle tabellen die gebruikt worden.
Force index snap ik nog niet geheel, ben de documentatie nog aan het doorspitten 
http://www.bruggema.nl/tables.sql
Dit zijn alle tabellen die gebruikt worden.
Force index snap ik nog niet geheel, ben de documentatie nog aan het doorspitten
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | fish.user_id, fish.catchdate, fish_categories.name, users.username, media.id AS photo_id FROM fish FORCE INDEX(id) LEFT JOIN users ON users.id = fish.user_id LEFT JOIN media ON media.source_id = fish.id LEFT JOIN fish_categories ON fish_categories.id = fish.fish_id GROUP BY fish.id ORDER BY fish.id DESC LIMIT 0,4 |
En zoveel keys zijn het nog niet. Inserts/updates gaan wel wat trager nu, dus als je relatief veel inserts of updates hebt, moet je het aantal indices tot een minimum beperken. En indices die je helemaal niet gebruikt kun je sowieso beter helemaal weglaten.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Nu heb ik die query gedaan en kreeg opeens te zien dat na het weghalen van de FORCE het opeens geen filesystem meer gebruikt als sorteer optie!? hoe kan dat?
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Filesort hoeft niet van het filesystem gebruik te maken hoor. En hoe het kan? Indices stonden net toch verkeerd, of heb je een analyze table gedaan?
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
ik had niets aangepast, maar blijkbaar had MySQL zelf even de indexes aangemaakt oid.
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Maar toch is de site nog sloom, maar goed; dan heb ik weer wat te doen.
-edit-doethetal
-edit-doethetal
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Laat hij standaard gewoon zien. Maar ik zou er een scriptje voor maken. Voeg dan wel SQL_NO_CACHE toe aan je query, anders zul je zien dat de meest complexe queries anders in no-time worden uitgevoerd. En houd er ook rekening mee dat tabellen op een gegeven moment in het geheugen zitten, en het dan een stuk sneller gaat dan wanneer iemand je site bezoekt wanneer die tabel niet meer in het geheugen zit.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Nu we toch bezig zijn met indexes, heb ik het volgende; dit heb ik ergens gelezen op het internet en wilde eens weten of dit klopt
Stel je hebt een tabel waarin je personen zet.
voornaam,
achternaam
leeftijd,
woonplaats
en nu heb je op deze tabel heel veel queries draaien, maar je wilt zorgen dat dit alles snel gaat. De meeste queries zoeken op voornaam, achternaam en of woonplaats.
Dan moest je volgens het artikel de volgorde van je INDEXES volgen (bij gecombineerde indexes).
voorbeeld INDEX (voornaam, achternaam, woonplaats)
Volgens het artikel waren de volgende zoek mogelijkheden mogelijk:
SELECT voornaam, achternaam, leefttijd, woonplaats
FROM personen
WHERE woonplaats = 'Groningen'
AND achternaam = 'Test'
AND voornaam = 'Eric'
maar niet als je zo zocht:
SELECT voornaam, achternaam, leefttijd, woonplaats
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
AND woonplaats = 'Groningen'
Klopt dit? zit nu niet echter een machine met MySQL en of wachtwoorden van sites
en zo ja, kan iemand dit dan eens duidelijk en zo mogelijk volledig uitleggen?
Stel je hebt een tabel waarin je personen zet.
voornaam,
achternaam
leeftijd,
woonplaats
en nu heb je op deze tabel heel veel queries draaien, maar je wilt zorgen dat dit alles snel gaat. De meeste queries zoeken op voornaam, achternaam en of woonplaats.
Dan moest je volgens het artikel de volgorde van je INDEXES volgen (bij gecombineerde indexes).
voorbeeld INDEX (voornaam, achternaam, woonplaats)
Volgens het artikel waren de volgende zoek mogelijkheden mogelijk:
SELECT voornaam, achternaam, leefttijd, woonplaats
FROM personen
WHERE woonplaats = 'Groningen'
AND achternaam = 'Test'
AND voornaam = 'Eric'
maar niet als je zo zocht:
SELECT voornaam, achternaam, leefttijd, woonplaats
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
AND woonplaats = 'Groningen'
Klopt dit? zit nu niet echter een machine met MySQL en of wachtwoorden van sites
en zo ja, kan iemand dit dan eens duidelijk en zo mogelijk volledig uitleggen?
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Nee klopt niet. Lees http://dev.mysql.com/doc/(...)-column-indexes.html eens door.
Belangrijkste is "A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns"
Daarom werkt bij jou index zoeken op WHERE achternaam="blah" niet, maar op WHERE achternaam="blah" AND voornaam="hoi" wel. En daarom werkt het bij een OR niet zo goed, omdat hij alleen het tweede (en derde) stuk van een index gebruiken wanneer het eerste (en tweede) stuk constant is.
Belangrijkste is "A multiple-column index can be considered a sorted array containing values that are created by concatenating the values of the indexed columns"
Daarom werkt bij jou index zoeken op WHERE achternaam="blah" niet, maar op WHERE achternaam="blah" AND voornaam="hoi" wel. En daarom werkt het bij een OR niet zo goed, omdat hij alleen het tweede (en derde) stuk van een index gebruiken wanneer het eerste (en tweede) stuk constant is.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
In het voorbeeld dat ik net las stond het volgende:
nog een mooie quote:
MySQL cannot use an index if the columns do not form a leftmost prefix of the index gewoon index volgen in je where parameters beginnend van links naar rechts en eventueel meer toevoegen... ?
ik probeer het allemaal te begrijpen maar't gaat niet zo snel
dus dit werkt met de index:
SELECT *
FROM personen
WHERE voornaam = 'Eric'
en
SELECT *
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
en dus ook alle 3 en eventueel meer
SELECT *
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
AND woonplaats = 'Groningen'
AND leeftijd < 50
maar hoe werkt het dan met dit:
SELECT *
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
AND leeftijd < 50
zal deze dan ook de index gebruiken? ik vraag maar hoor
Nu zie ik dat de voorbeelden andersom moesten, dan klopt het!quote:The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index is used in the following queries
nog een mooie quote:
MySQL cannot use an index if the columns do not form a leftmost prefix of the index gewoon index volgen in je where parameters beginnend van links naar rechts en eventueel meer toevoegen... ?
ik probeer het allemaal te begrijpen maar't gaat niet zo snel
dus dit werkt met de index:
SELECT *
FROM personen
WHERE voornaam = 'Eric'
en
SELECT *
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
en dus ook alle 3 en eventueel meer
SELECT *
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
AND woonplaats = 'Groningen'
AND leeftijd < 50
maar hoe werkt het dan met dit:
SELECT *
FROM personen
WHERE voornaam = 'Eric'
AND achternaam = 'Test'
AND leeftijd < 50
zal deze dan ook de index gebruiken?
ik vraag maar hoor
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Via voornaam en achternaam (en bij die een na laatste ook woonplaats) haalt hij de rijen op die eraan voldoen, en daarna checkt hij bij die rijen de leeftijd. Daarom is Woonplaats niet zo nuttig om in je index te zetten: tenzij je de query SELECT woonplaats FROM table WHERE voornaam='a' AND achternaam='b' heel vaak gebruikt (zodat je een covering index hebt, maar dat is alleen nuttig als je hem heel vaak gebruikt), omdat er toch niet veel rijen overblijven na selectie op voornaam en achternaam. Want onthoud: hoe langer de index, hoe meer tijd hij kost om bij te werken, en hoe meer tekstvelden in een index, hoe trager hij wordt bij een SELECT-query omdat hij dan erg groot (veel kB's) wordt.
Overigens zie je via EXPLAIN SELECT ... welk deel van de index naar verwachting benut zal worden.
Overigens zie je via EXPLAIN SELECT ... welk deel van de index naar verwachting benut zal worden.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Als je een index hebt op (lastname, firstname) dan kun je queries doen als:quote:Op zondag 31 augustus 2008 22:04 schreef Chandler het volgende:
In het voorbeeld dat ik net las stond het volgende:
[..]
Nu zie ik dat de voorbeelden andersom moesten, dan klopt het!
nog een mooie quote:
MySQL cannot use an index if the columns do not form a leftmost prefix of the index gewoon index volgen in je where parameters beginnend van links naar rechter en eventueel meer toevoegen... ?
SELECT * FROM persons WHERE lastname="Test";
SELECT * FROM persons WHERE lastname="Test" AND firstname="Eric";
SELECT * FROM persons WHERE firstname="Eric" AND lastname="Test";
Maar niet:
SELECT * FROM persons WHERE firstname="Eric";
Duidelijk, dan zijn mijn queries toch aardig goed en indexes ook
Een andere vraag stel ik wil het volgende opslaan.
71.231
302.121
5.1231
Wat moet ik daarvoor gebruiken? gebruik nu een char, maar dat lijkt me niet echt handig
Verder heb ik een query die best wel veel tijd neemt en heb deze met analyze geprobeerd te begrijpen:
nu krijg ik het volgende resultaat:
nu heb ik op het tabel stats_ip_link een index op stat_id en stat_ip en een index op 'lastdate` maar nog steeds is deze sloom
Anyone? of is het handiger dat ik lastdate en firstdate (beiden datetime) verander naar timestamp oid?
[ Bericht 33% gewijzigd door Chandler op 01-09-2008 09:44:02 ]
en indexes ook Een andere vraag stel ik wil het volgende opslaan.
71.231
302.121
5.1231
Wat moet ik daarvoor gebruiken? gebruik nu een char, maar dat lijkt me niet echt handig
Verder heb ik een query die best wel veel tijd neemt en heb deze met analyze geprobeerd te begrijpen:
| 1 2 3 4 5 | FROM stats_ip_link LEFT JOIN stats_ip ON stats_ip.id = stats_ip_link.ip_id WHERE UNIX_TIMESTAMP( stats_ip_link.lastdate ) > ( UNIX_TIMESTAMP( NOW( ) ) - ( 60 *15 ) ) GROUP BY stats_ip_link.stat_id |
nu krijg ik het volgende resultaat:
| 1 2 3 | 1 SIMPLE stats_ip_link ALL NULL NULL NULL NULL 70168 Using where; Using temporary; Using filesort 1 SIMPLE stats_ip eq_ref PRIMARY PRIMARY 4 gfxstatcom_db.stats_ip_link.ip_id 1 Using index |
nu heb ik op het tabel stats_ip_link een index op stat_id en stat_ip en een index op 'lastdate` maar nog steeds is deze sloom
Anyone?
of is het handiger dat ik lastdate en firstdate (beiden datetime) verander naar timestamp oid?[ Bericht 33% gewijzigd door Chandler op 01-09-2008 09:44:02 ]
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Die nummers kun je het beste opslaan als een mediumint (signed/unsigned, afhankelijk van of je wel/geen negatieve nummers tegenkomt).
De index op stats_ip_link is lastig: je kunt hem op lastdate zetten voor de WHERE, je kunt hem op stat_id zetten voor de GROUP BY, maar op (lastdate,stat_id) heeft het geen zin omdat lastdate niet constant is en stat_id daarna niet meer benut kan worden. Het beste lijkt mij hier een index op lastdate, omdat hoe langer je die tabel houdt, je na toepassen van de WHERE altijd ongeveer evenveel resultaten overhoudt, zodat het in de loop der tijd niet langzamer wordt.
Die index moet wel benut kunnen worden, en als je een functie op een kolom loslaat, kan dat niet meer. Zelf werk ik altijd met unix timestamps in een database, maar jouw aanpak moet ook kunnen werken. Kun je niet iets doen als lastdate > mysqls datetime formaat van 15 minuten geleden?
De index op stats_ip_link is lastig: je kunt hem op lastdate zetten voor de WHERE, je kunt hem op stat_id zetten voor de GROUP BY, maar op (lastdate,stat_id) heeft het geen zin omdat lastdate niet constant is en stat_id daarna niet meer benut kan worden. Het beste lijkt mij hier een index op lastdate, omdat hoe langer je die tabel houdt, je na toepassen van de WHERE altijd ongeveer evenveel resultaten overhoudt, zodat het in de loop der tijd niet langzamer wordt.
Die index moet wel benut kunnen worden, en als je een functie op een kolom loslaat, kan dat niet meer. Zelf werk ik altijd met unix timestamps in een database, maar jouw aanpak moet ook kunnen werken. Kun je niet iets doen als lastdate > mysqls datetime formaat van 15 minuten geleden?
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Ook een index op lastdate werkt niet qua snelheid bevordelijk!
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Dat is gek, welke query gebruik je nu, en hoeveel rijen voldoen er aan WHERE UNIX_TIMESTAMP( stats_ip_link.lastdate ) > ( UNIX_TIMESTAMP( NOW( ) ) - ( 60 *15 ) ) ?
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
80 rijen maar dit kan per seconde veranderen en de query die ik eerder ook al gepost heb
en de query die ik eerder ook al gepost heb | 1 2 3 4 5 | FROM stats_ip_link LEFT JOIN stats_ip ON stats_ip.id = stats_ip_link.ip_id WHERE UNIX_TIMESTAMP( stats_ip_link.lastdate ) > ( UNIX_TIMESTAMP( NOW( ) ) - ( 60 *15 ) ) GROUP BY stats_ip_link.stat_ |
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Met 80 zou hij heel snel moeten zijn. Moet je wel dit in acht nemen:
En die query moet ook wachten als de tabel geüpdatet wordt, dus dat kan wel wat traagheid veroorzaken, maar met een rij of 80 is het anders heel snel als die index benut kan worden.quote:Die index moet wel benut kunnen worden, en als je een functie op een kolom loslaat, kan dat niet meer. Zelf werk ik altijd met unix timestamps in een database, maar jouw aanpak moet ook kunnen werken. Kun je niet iets doen als lastdate > mysqls datetime formaat van 15 minuten geleden?
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Zou ik ook zeggen maar helaas 
het blijft toch sloom
Er staan ruim 70.000 items in dit tabel, waarbij lastdate geindexeerd is!. Je zou denken dat de server dit binnen 0.01 seconde zou moeten kunnen uitzoeken maar helaas, dat doet het niet
het blijft toch sloom Er staan ruim 70.000 items in dit tabel, waarbij lastdate geindexeerd is!. Je zou denken dat de server dit binnen 0.01 seconde zou moeten kunnen uitzoeken maar helaas, dat doet het niet
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Bedoel je met explain of zonder explain? zonder is de zelfde als bovenstaande
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Ik quote mezelf toch niet voor niks? f(kolom) kan niet geïndexeerd worden, dus moet je je query aanpassen.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Ik snap je niet helemaal (probeer het wel hoor )
)
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
Je query moet in deze vorm staan:
MySQL is niet slim genoeg om in te zien dat UNIX_TIMESTAMP een monotone functie is.
| 1 2 3 4 5 | FROM stats_ip_link LEFT JOIN stats_ip ON stats_ip.id = stats_ip_link.ip_id WHERE stats_ip_link.lastdate > ....... GROUP BY stats_ip_link.stat_id |
MySQL is niet slim genoeg om in te zien dat UNIX_TIMESTAMP een monotone functie is.
eee7a201261dfdad9fdfe74277d27e68890cf0a220f41425870f2ca26e0521b0
Ik zie idd dat de snelheid bepaald wordt door unix_timestamp
deze query duurde 0.022 terwijl deze (vorige) ruim 0.7xxx aan tijd nam.
Echter verschillen beide queries qua uitkomst :{
| 1 2 3 4 5 | FROM stats_ip_link LEFT JOIN stats_ip ON stats_ip.id = stats_ip_link.ip_id WHERE stats_ip_link.lastdate > ( NOW( ) - ( 60 *15 ) ) GROUP BY stats_ip_link.stat_id |
Echter verschillen beide queries qua uitkomst :{
The people who lost my respect will never get a capital letter for their name again.
Like trump...
Like trump...
