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

Index

» digital corner

actieve topics nieuwe topics
abonnement Unibet Coolblue Bitvavo
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."
pi_148853136
Helaas,

Ook geen profijt... :{ maar thanks voor't meedenken...

Ik speel gewoon verder ;)

-- gelijk een vraagje ;)

Kan iemand mij deze regel uitleggen? :P

CONSTRAINT `image_fk` FOREIGN KEY (`image_id`) REFERENCES `image` (`image_id`),

[ Bericht 32% gewijzigd door Chandler op 19-01-2015 22:08:23 ]
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148862022
quote:
0s.gif Op maandag 19 januari 2015 20:18 schreef Chandler het volgende:

-- gelijk een vraagje ;)

Kan iemand mij deze regel uitleggen? :P

CONSTRAINT `image_fk` FOREIGN KEY (`image_id`) REFERENCES `image` (`image_id`),
De tabel waar die regel in staat heeft een foreign key constraint met de unieke naam image_fk. De waarde in de kolom image_id verwijst naar een waarde in de kolom image_id in de tabel image. Je kunt dus geen waarde opslaan die niet ook in image.image_id staat.

Bijkomend effect is dat er een index op de kolom image_id is in de tabel waar die regel staat.

En als je je afvraagt waarom (`image_id`) tussen haakjes staat; het is ook mogelijk om een foreign key constraint te maken over meerdere kolommen. Die moeten dan wel verwijzen naar evenveel kolommen die allemaal in dezelfde tabel staan. Of dat nuttig is, weet ik niet. Het verklaart de haakjes, en het is nuttig om te weten dat een foreign key ook over meerdere kolommen kan gaan.
fokstok.nl
pi_148970529
Thanks Light! Snap er nog weinig van maar al iets meer dan ervoor..

Qua indexes loopt het nog steeds voor geen meter, heb nu eerst mijn mysql configuratie aangepast. (van 64MB naar 6GB :D) en dat scheelt al aardig wat tijd... maar nog steeds gebruikt hij een temporary filesort...

1
2
3		1 SIMPLE t range  PRIMARY,name,id name 34 NULL 2 Using index condition; Using temporary; using filesort
1 SIMPLE bt ref PRIMARY,video_id video_id 4  asexfilmzoeker1.t.id 488020 Using index 
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 asexfilmzoeker1.bt.video_id 1 NULL

Index voor video_tag_link
1
2
3
4
5		Actie    Sleutelnaam    Type    Unieke waarde    Gecomprimeerd    Kolom    Kardinaliteit    Collatie    Leeg    Opmerking
Wijzigen Wijzigen    Verwijderen Verwijderen    PRIMARY    BTREE    Ja    Nee    video_id        A    Nee    
tag_id    48802076    A    Nee
Wijzigen Wijzigen    Verwijderen Verwijderen    video_id    BTREE    Nee    Nee    tag_id        A    Nee    
video_id        A    Nee

Index voor tags
1
2
3
4
5		Actie    Sleutelnaam    Type    Unieke waarde    Gecomprimeerd    Kolom    Kardinaliteit    Collatie    Leeg    Opmerking
Wijzigen Wijzigen    Verwijderen Verwijderen    PRIMARY    BTREE    Ja    Nee    id    44810    A    Nee    
Wijzigen Wijzigen    Verwijderen Verwijderen    name    BTREE    Ja    Nee    name    44810    A    Nee    
Wijzigen Wijzigen    Verwijderen Verwijderen    id    BTREE    Nee    Nee    id    44810    A    Nee    
name    44810    A    Nee

En index voor videos
1
2
3
4
5		Actie    Sleutelnaam    Type    Unieke waarde    Gecomprimeerd    Kolom    Kardinaliteit    Collatie    Leeg    Opmerking
Wijzigen Wijzigen    Verwijderen Verwijderen    PRIMARY    BTREE    Ja    Nee    id    7158720    A    Nee    
Wijzigen Wijzigen    Verwijderen Verwijderen    seconds    BTREE    Nee    Nee    seconds    73801    A    Nee    
Wijzigen Wijzigen    Verwijderen Verwijderen    tags    FULLTEXT    Nee    Nee    tags    1        Nee    
Wijzigen Wijzigen    Verwijderen Verwijderen    title    FULLTEXT    Nee    Nee    title    1        Nee    

Het veranderen van een indexen etc (meerdere keren gedaan, kost me zo'n 5 uur per index...) :{
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148986741
Een ander type database gebruiken? :P

Zou je ook eens een explain van je query kunnen posten?
pi_148988456
quote:
19s.gif Op vrijdag 23 januari 2015 18:47 schreef TwenteFC het volgende:
Een ander type database gebruiken? :P

Zou je ook eens een explain van je query kunnen posten?
Die staat in mijn 1e quote.

Het rare is namelijk dat zoeken in tabel videos, veld tags (like %tag%) vele malen sneller is dan het uitlezen van tags tabellen (normaliseren). :{ op dit moment geen mogelijkheid om een andere server/scripts te gebruiken anders dan MySQL.......

Wil wel even een dump maken hoor :@ strip ik alle onnodige informatie, heb je een import sql bestand van 4GB :D
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_148991877
Denormaliseren? Gewoon om te kijken wat het doet.
pi_148991882
Ja :{ dan maar :P
The people who lost my respect will never get a capital letter for their name again.
Like trump...
  vrijdag 23 januari 2015 @ 21:57:21 #218
63192 ursel
"Het Is Hier Fantastisch!
pi_148992856
Zit je stiekem sexfilms te zoeken? :P
pi_149002821
quote:
0s.gif Op vrijdag 23 januari 2015 21:57 schreef ursel het volgende:
Zit je stiekem sexfilms te zoeken? :P
7 miljoen? beetje te veel, maar je zit aardig in de buurt :+
The people who lost my respect will never get a capital letter for their name again.
Like trump...
  FOK!mycroftheld zaterdag 24 januari 2015 @ 10:45:27 #220
128465 verified  bondage
Ingewikkeld
pi_149004280
quote:
0s.gif Op vrijdag 23 januari 2015 19:47 schreef Chandler het volgende:

[..]

Die staat in mijn 1e quote.

Het rare is namelijk dat zoeken in tabel videos, veld tags (like %tag%) vele malen sneller is dan het uitlezen van tags tabellen (normaliseren). :{ op dit moment geen mogelijkheid om een andere server/scripts te gebruiken anders dan MySQL.......

Wil wel even een dump maken hoor :@ strip ik alle onnodige informatie, heb je een import sql bestand van 4GB :D
Welke storage engine gebruik je? Heb je bijvoorbeeld al InnoDB geprobeerd?

En: https://blogs.oracle.com/(...)ncement_in_full_text

[ Bericht 6% gewijzigd door bondage op 24-01-2015 10:52:25 ]
FOK!stats | Mycroft | DTS!stats
pi_149005352
Sowieso MariaDB in plaats van MySQL
pi_149005580
quote:
1s.gif Op zaterdag 24 januari 2015 11:47 schreef robin007bond het volgende:
Sowieso MariaDB in plaats van MySQL
En waarom zou dat gaan helpen? Zoals al aangegeven is de beste optie om gewoon een fatsoenlijk zoekplatform te gebruiken, maar die mogelijkheid heeft hij niet.
Volkorenbrood: "Geen quotes meer in jullie sigs gaarne."
pi_149005664
Nee :( al ligt het volgens mij en Light voornamelijk aan de foutieve indexen... :{ alleen die goed krijgen kost per test 8 uur :( LOL
The people who lost my respect will never get a capital letter for their name again.
Like trump...
pi_149005681
quote:
1s.gif Op zaterdag 24 januari 2015 11:58 schreef Monolith het volgende:

[..]

En waarom zou dat gaan helpen? Zoals al aangegeven is de beste optie om gewoon een fatsoenlijk zoekplatform te gebruiken, maar die mogelijkheid heeft hij niet.
Het was meer een zijspoor. :P Niet echt relevant aan zijn probleem.
pi_149007705
Jeej,

Volgende query doet het best :)

1
2
3
4
5
6
7
8		explain
SELECT * 
    FROM videos o
    LEFT OUTER JOIN video_tag_link ot ON ot.video_id = o.id
    LEFT OUTER JOIN tags t ON t.tag_id = ot.tag_id
   WHERE t.name IN ('home','alone')
GROUP BY o.id
  HAVING COUNT(DISTINCT t.name) = 2

Doet ook aan filesort maar vind in totaal 5638 resultaten in ruim 1 seconde... :D
The people who lost my respect will never get a capital letter for their name again.
Like trump...
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')