[PHP/(My)SQL] voor dummies #119

Index

» digital corner

actieve topics nieuwe topics
abonnement Unibet Coolblue Bitvavo
pi_148116268
value => image in plaats van andersom.
Volkorenbrood: "Geen quotes meer in jullie sigs gaarne."
pi_148116336
quote:
0s.gif Op maandag 29 december 2014 22:22 schreef Monolith het volgende:
value => image in plaats van andersom.
bijna een snelle edit gedaan...heb ik dus gedaan
maar dan krijg ik alleen de eerste foto van de eerste folder
  maandag 29 december 2014 @ 22:23:27 #183
91039 mstx
2x1/2 = 1/2 x 1/2
pi_148116355
Ja of je echo't gewoon $images[0] :')
Op donderdag 2 juli 2009 22:41 schreef RTB het volgende:
als ik elk rap"liedje" een kans moest geven was ik aan het eind van dit millennium nog bezig met het tempo waarin die kotshoop uitgebraakt wordt.
👾
pi_148116424
quote:
1s.gif Op maandag 29 december 2014 22:23 schreef mstx het volgende:
Ja of je echo't gewoon $images[0] :')
Laat TS ff lekker kutten met arrays, anders snapt hij het nooit :P
  maandag 29 december 2014 @ 22:25:28 #185
91039 mstx
2x1/2 = 1/2 x 1/2
pi_148116453
quote:
0s.gif Op maandag 29 december 2014 22:23 schreef MrNiles het volgende:

[..]

bijna een snelle edit gedaan...heb ik dus gedaan
maar dan krijg ik alleen de eerste foto van de eerste folder
dan moet je eerst een readdir doen van de root map, door de mappen loopen en per map de afbeeldingen ophalen en de eerste tonen.
Op donderdag 2 juli 2009 22:41 schreef RTB het volgende:
als ik elk rap"liedje" een kans moest geven was ik aan het eind van dit millennium nog bezig met het tempo waarin die kotshoop uitgebraakt wordt.
👾
pi_148116496
Even uitzoeken hoe globs werken en dan toepassen op je directory structuur. In een glob matcht ** alles inclusief directory separators en * alles behalve directory separators.
Volkorenbrood: "Geen quotes meer in jullie sigs gaarne."
pi_148116577
quote:
1s.gif Op maandag 29 december 2014 22:23 schreef mstx het volgende:
Ja of je echo't gewoon $images[0] :')
Die had ik al eens geprobeerd, maar dan krijg ik een loop van foto1 uit folder1, geen andere folders

quote:
Laat TS ff lekker kutten met arrays, anders snapt hij het nooit :P
Zeker waar
pi_148599940
Voor als iemand testgegevens nodig heeft:
http://www.generatedata.com/
When the student is ready, the teacher will appear.
When the student is truly ready, the teacher will disappear.
pi_148622805
quote:
7s.gif Op maandag 12 januari 2015 11:44 schreef Aether het volgende:
Voor als iemand testgegevens nodig heeft:
http://www.generatedata.com/
https://github.com/fzaninotto/Faker
pi_148702387
Korte vraag mbt zoeken op grote tabellen.

Voor een vriend mag ik een importeer scriptje schrijven voor het importeren van ruim 7 miljoen videos met oa de volgende gegevens titel, tags, duur

Nu heb ik al een scriptje geschreven die dit alles importeert in een database tabel waarbij ik titel, tags als fulltext indexeer en wil apart daarvan de duur (lengte van het filmpje) ook nog gaan indexeren (zodat de gebruiker daar ook op kan zoeken)

Nu heb ik net een query uitgevoerd op deze tabel en dat duurde echt erg lang
Tabel: 7,217,116 MyISAM latin1_swedish_ci 4,8 GiB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20		CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` int(10) unsigned NOT NULL,
  `video_id` varchar(64) NOT NULL,
  `tags` varchar(255) NOT NULL,
  `uploaddate` datetime NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `embed` varchar(255) NOT NULL,
  `seconds` int(10) unsigned NOT NULL,
  `lastview` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ratingcount` int(10) unsigned NOT NULL,
  `ratingtotal` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `site_id` (`site_id`),
  FULLTEXT KEY `tags` (`tags`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7217117 ;

Nu duurt de volgende query:
1
2		SELECT COUNT( 1 ) 
FROM (SELECT * FROM  `videos` WHERE  `tags` LIKE  '%home%') t
erg lang (zo'n 10 seconden).
Met limit 0,30 duurt deze query 0.0240 secs

Is er een mogelijkheid om deze query sneller te maken? eventueel tags in een apart tabel?
The people who lost my respect will never get a capital letter for their name again.
Like trump...
  donderdag 15 januari 2015 @ 10:17:15 #191
363995 Reemi
Zeg maar Remi.
pi_148702438
Ik zou sowieso de tags los opslaan in een andere tabel, in plaats van als één string in een kolom. Dus tag-video_id paren. Dat zal inderdaad al een stuk sneller worden.
Smile like you mean it
www.wefut.com
  donderdag 15 januari 2015 @ 10:17:58 #192
91039 mstx
2x1/2 = 1/2 x 1/2
pi_148702452
Een LIKE beginnend met % is altijd traag. Tags in een aparte tabel en een koppeltabel tussen tags en videos is de beste oplossing. Dat scheelt je ook nog een boel dubbele data.
Op donderdag 2 juli 2009 22:41 schreef RTB het volgende:
als ik elk rap"liedje" een kans moest geven was ik aan het eind van dit millennium nog bezig met het tempo waarin die kotshoop uitgebraakt wordt.
👾
  FOK!mycroftheld donderdag 15 januari 2015 @ 10:54:05 #193
128465 verified  bondage
Ingewikkeld
pi_148703416
quote:
0s.gif Op donderdag 15 januari 2015 10:14 schreef Chandler het volgende:
Korte vraag mbt zoeken op grote tabellen.

Voor een vriend mag ik een importeer scriptje schrijven voor het importeren van ruim 7 miljoen videos met oa de volgende gegevens titel, tags, duur

Nu heb ik al een scriptje geschreven die dit alles importeert in een database tabel waarbij ik titel, tags als fulltext indexeer en wil apart daarvan de duur (lengte van het filmpje) ook nog gaan indexeren (zodat de gebruiker daar ook op kan zoeken)

Nu heb ik net een query uitgevoerd op deze tabel en dat duurde echt erg lang
Tabel: 7,217,116 MyISAM latin1_swedish_ci 4,8 GiB
[ code verwijderd ]

Nu duurt de volgende query:
[ code verwijderd ]

erg lang (zo'n 10 seconden).
Met limit 0,30 duurt deze query 0.0240 secs

Is er een mogelijkheid om deze query sneller te maken? eventueel tags in een apart tabel?
Ik heb zelf slechte ervaringen met zoeken in grote tabellen, zowel MyISAM als InnoDB.

Is dit misschien een optie? http://astellar.com/2011/(...)-search-with-sphinx/
FOK!stats | Mycroft | DTS!stats
pi_148704887
Ik ga eens een import draaien die tags in een apart tabel zetten + een tabel voor video/tag links. Eens kijken hoe lang dit gaat duren... :D
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148707977
LIKE negeert sowieso de fulltext index. Wil je daar gebruik van maken, dan kun je bijvoorbeeld MATCH gebruiken, zie ook de documentatie.

Zoals echter al aangegeven kun je beter een search platform gebruiken als SOLR, ElasticSearch, Sphinx, etcetera.
Volkorenbrood: "Geen quotes meer in jullie sigs gaarne."
pi_148722045
quote:
0s.gif Op donderdag 15 januari 2015 13:17 schreef Monolith het volgende:

Zoals echter al aangegeven kun je beter een search platform gebruiken als SOLR, ElasticSearch, Sphinx, etcetera.
:Y Een tijd terug voor het eerst met ElasticSearch gewerkt :o gaat inderdaad écht bloedsnel. Automatisch aanvullen en suggesties geven snel.
pi_148722300
Het duurde ff maar dan heb je ook wat.

304k aan tags
kleine 69miljoen tag links :@

Nu nog een leuke query browsen...
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148735784
quote:
0s.gif Op donderdag 15 januari 2015 10:14 schreef Chandler het volgende:

Nu duurt de volgende query:
[ code verwijderd ]

erg lang (zo'n 10 seconden).
Met limit 0,30 duurt deze query 0.0240 secs

Is er een mogelijkheid om deze query sneller te maken? eventueel tags in een apart tabel?
Je kunt het iets beter krijgen door geen subquery te gebruiken:
1SELECT COUNT( * ) FROM `videos` WHERE  `tags` LIKE  '%home%'
Maar de winst die je zo kunt behalen is beperkt. Je kunt niet efficient zoeken als je niet weet waar in de string je moet kijken. Het is immers ook niet handig om in een woordenboek alle woorden waar een Q in staat te markeren.

quote:
0s.gif Op donderdag 15 januari 2015 13:17 schreef Monolith het volgende:

Zoals echter al aangegeven kun je beter een search platform gebruiken als SOLR, ElasticSearch, Sphinx, etcetera.
Da's zeker een optie die het overwegen waard is, helemaal omdat het over redelijk grote datasets gaat.
fokstok.nl
pi_148738520
Klopt Light! :)

Echter heeft de hosting waar dit straks moet gaan draaien blijkbaar geen zoekplatformen of de mogelijkheid daarvoor, ik ga eerst eens spelen met tags en kijken hoe ik daar het meeste uit kan halen. Eventueel het tijdelijk opslaan van data sets... zodat hergebruik stukken sneller gaat...
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148749556
quote:
0s.gif Op vrijdag 16 januari 2015 08:05 schreef Chandler het volgende:
Klopt Light! :)

Echter heeft de hosting waar dit straks moet gaan draaien blijkbaar geen zoekplatformen of de mogelijkheid daarvoor, ik ga eerst eens spelen met tags en kijken hoe ik daar het meeste uit kan halen. Eventueel het tijdelijk opslaan van data sets... zodat hergebruik stukken sneller gaat...
Die tags in een aparte tabel opslaan is alvast een stap, mits je de indexes goed zet en gebruikt.

Maar je zit tegen de grenzen van wat met MySQL mogelijk is en als je spannendere dingen wilt, moet je op zoek naar een hoster die wel een zoekplatform aanbiedt (of zelf de hosting doen).
fokstok.nl
pi_148798465
Ik ben bang dat hij dan naar een andere hoster opzoek moet, ben zelf nu aan het spelen de database zonder zoekplatform en heb in eerste instantie mijn tags maar eens beperkt.. En wat video's geskipped met onzin titels..

Eerst een kleine 8m aan videos, nu 7,2m.
Eerst 188k aan tags, nu 44k.
Eerst 70m aan tag links, nu 48m.

Scheelt toch wat... nu weer verder klooien met die queries...
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148835523
ff een queries gespeeld, is niet gemakkelijk zeg :P (zonder specifieke zoekplatformen, maar moet toch echt wel sneller kunnen).

Query result:
quote:
MySQL gaf een lege resultatenset terug (0 rijen). (query duurde 157.0733 sec)
Query:
1
2
3
4
5
6
7		SELECT b . * 
FROM video_tag_link bt, videos b, tags t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('home',  'video'))
AND b.id = bt.video_id
GROUP BY b.id
HAVING COUNT( b.id ) =2

En met bestaande velden home en alone is het resultaat:
quote:
Showing rows 0 - 29 (213 total, query duurde 51.7825 sec)
Dat kan sneller toch? :P

Deze pagina gaf me aardig wat info: http://tagging.pui.ch/pos(...)ms-performance-tests

-edit-

Koppel ik ze los dan heb ik dit
SELECT * FROM tags WHERE name in ('home','alone') == 0,000 secs (2 resultaten)
SELECT video_id FROM video_tag_link WHERE tag_id IN ( 2316, 290 ) LIMIT 0 , 300000 == 20 secs (15292 resultaten).
SELECT * FROM videos WHERE id IN (763,) == 1 secs (15292 resultaten).

Is een stukje sneller... :{

[ Bericht 13% gewijzigd door Chandler op 19-01-2015 12:23:37 ]
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148835835
quote:
5s.gif Op maandag 19 januari 2015 12:08 schreef Chandler het volgende:
ff een queries gespeeld, is niet gemakkelijk zeg :P (zonder specifieke zoekplatformen, maar moet toch echt wel sneller kunnen).

Showing rows 0 - 29 (213 total, query duurde 51.7825 sec)
Dat kan sneller toch? :P
Voer de query eens uit met EXPLAIN aan het begin.
Dan kun je zien waar het beste indices geplaatst kunnen worden.
When the student is ready, the teacher will appear.
When the student is truly ready, the teacher will disappear.
pi_148835932
quote:
7s.gif Op maandag 19 januari 2015 12:21 schreef Aether het volgende:

[..]

Voer de query eens uit met EXPLAIN aan het begin.
Dan kun je zien waar het beste indices geplaatst kunnen worden.
1
2
3		1 SIMPLE t range PRIMARY,name,id name 34 NULL 2 Using index condition; Using temporary; using filesort
1 SIMPLE b ALL PRIMARY NULL NULL NULL 7158720 Using join buffer (Block Nested Loop)
1 SIMPLE bt eq_ref video_id video_id 8 a.b.id,a.t.tag_id 1 Using index

:)
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148837350
Het ziet er in ieder geval uit als een hele kromme query. Gebruik je nou IN en HAVING om enerzijds te selecteren op twee tag names en anderzijds te checken of ze allebei gevonden worden? In dat geval zou ik eens kijken of je middels WHERE EXISTS niet sneller resultaat krijgt.

Verder valt er vrij weinig te zeggen over je query zonder de onderliggende db structuur te kennen.
Volkorenbrood: "Geen quotes meer in jullie sigs gaarne."
pi_148846213
quote:
0s.gif Op maandag 19 januari 2015 13:13 schreef Monolith het volgende:
Het ziet er in ieder geval uit als een hele kromme query. Gebruik je nou IN en HAVING om enerzijds te selecteren op twee tag names en anderzijds te checken of ze allebei gevonden worden? In dat geval zou ik eens kijken of je middels WHERE EXISTS niet sneller resultaat krijgt.
Ik zal het eens bekijken en uitproberen ;)

quote:
Verder valt er vrij weinig te zeggen over je query zonder de onderliggende db structuur te kennen.
Bij deze ;)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22		CREATE TABLE IF NOT EXISTS `tags` (
  `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `name` (`name`),
  KEY `id` (`tag_id`,`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=200938 ;

CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tags` varchar(255) NOT NULL, -- tijdelijk voor andere tests (zelfde inhoud als beide andere tabellen)
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `tags` (`tags`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7158721 ;

CREATE TABLE IF NOT EXISTS `video_tag_link` (
  `video_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  UNIQUE KEY `video_id` (`video_id`,`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- 48miljoen records
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148851299
quote:
14s.gif Op maandag 19 januari 2015 17:09 schreef Chandler het volgende:

[..]

Ik zal het eens bekijken en uitproberen ;)

[..]

Bij deze ;)
[ code verwijderd ]

Je mist een index in je koppeltabel
1ALTER TABLE `video_tag_links` ADD INDEX `tag_id` (`tag_id`);

Voor video_id heb je geen aparte index nodig, dat wordt afgehandeld door de unique key. Je zou ook de unique key kunnen aanpassen naar (`tag_id`, `video_id`) en dan een aparte index op `video_id` zetten. In beide gevallen wordt je tabel groter (want meer index-ruimte nodig) en worden queries een stuk sneller.
fokstok.nl
pi_148851533
Volgens mij doet hij altijd een query op beide velden in de index, dus zou dat op zich goed moeten gaan. Hoewel het lang geleden is dat ik me in detail met indices in MySQL heb beziggehouden.
Volkorenbrood: "Geen quotes meer in jullie sigs gaarne."
pi_148851875
quote:
0s.gif Op maandag 19 januari 2015 19:41 schreef Monolith het volgende:
Volgens mij doet hij altijd een query op beide velden in de index, dus zou dat op zich goed moeten gaan. Hoewel het lang geleden is dat ik me in detail met indices in MySQL heb beziggehouden.
Er wordt een full table scan gedaan bij een query waar je dat niet verwacht, dus de indices staan niet goed. Dat die full table scan op de video's-tabel wordt gedaan en niet op de koppeltabel, komt omdat die laatste tabel veel groter is (7mln vs 48 mln rijen).
fokstok.nl
pi_148852059
quote:
0s.gif Op maandag 19 januari 2015 19:49 schreef Light het volgende:

[..]

Er wordt een full table scan gedaan bij een query waar je dat niet verwacht, dus de indices staan niet goed. Dat die full table scan op de video's-tabel wordt gedaan en niet op de koppeltabel, komt omdat die laatste tabel veel groter is (7mln vs 48 mln rijen).
Ah, ik had de explain even gemist. ;)
Volkorenbrood: "Geen quotes meer in jullie sigs gaarne."
actieve topics nieuwe topics
abonnement Unibet Coolblue Bitvavo
[PHP/(My)SQL] voor dummies #119
Index

» digital corner

Forum Opties
Forumhop:
Hop naar:
(afkorting, bv 'KLB')