[php] sneller queries uitvoeren

[Mark Wegener]

Hi,

ik heb een tabel met de volgende structuur:

code:

CREATE TABLE releases (
  releaseid int(8) NOT NULL auto_increment,
  artist varchar(100) NOT NULL default '',
  album varchar(100) NOT NULL default '',
  genre varchar(50) NOT NULL default '',
  date timestamp(14) NOT NULL,
  KEY id (releaseid)
) TYPE=MyISAM;

er zitten ongeveer 400.000 records in, de gemiddelde querytijd is met een eenvoudige query 1 seconde en met een iets uitgebreidere query rond de 2 á 2.5 seconde.

het grootste probleem is dat ik de query 2x moet uitvoeren omdat er een pagina navigatie op de pagina staat.

Dus hoe druk ik de querytime een beetje??

Thanks
- Martin

[Super_ik]

kun je ook een query ofzo laten zien? kijk ook eens naar normalisering ed

[Verwijderd]

Kun je dan misschien ook de SQL van een 'eenvoudige' query geven?

[Sybr_E-N]

Heb je wel een index op releaseid.? Dat scheelt ook heel veel tijd.

[Mark Wegener]

eenvoudige query: 'SELECT * FROM `releases` LIMIT 0, 30' (geef toe, eenvoudig)

Sleutelnaam: id
Type: INDEX
Kardinaliteit: Geen (???)
Veld: releaseid

[ Voor 13% gewijzigd door Mark Wegener op 11-01-2003 21:06 ]

[Super_ik]

hmmm, is je db structuur wel goed?
k zie dat je artiest en album ed. allemaal in 1 tabel hebt zitten
is t nie beter als je 1 tabel met artiesten, 1 tabel met albums, 1 tabel met genres pakt
en dat een tabel waarin deze gegevens gelinkt worden

[Verwijderd]

Je kunt iig de * veranderen door een opsomming van de veldnamen, dat zal al wel wat schelen

[Verwijderd]

Verwijderd schreef op 11 January 2003 @ 21:08:
Je kunt iig de * veranderen door een opsomming van de veldnamen, dat zal al wel wat schelen

Hoeveel scheelt dat dan als je stel je voor alle velden wilt lezen? niets toch?

[Mark Wegener]

owh, ik zie net dat ik een verkeerde query heb genomen.

de simpele query is: SELECT artist, album, genre, DATE_FORMAT(date,'%M %D, %Y') AS datem FROM releases WHERE date = 20020528000000
deze selecteert 405 records in 1.1149380207062 seconde

die echt eenvoudige query duur de ook maar 0.0042691230773926 seconde.

[Sybr_E-N]

Ja, als je die * gebruikt moet MySql eerst je hele tabel doorzoeken naar kolomen. Als je zelf kolommen opgeeft hoeft dat niet. Scheelt weer een paar tienden van een seconde.

[Mark Wegener]

super_ik: meer tabellen is ten eerste irritant om te vullen (dat is inprinciepe geen probleem), de tabellen zijn dan wel iets kleiner, maar uiteindelijk moeten wel al de 3 tabellen aangesproken worden.

[Super_ik]

jeetje, irritanter om te vullen, dan ben ik uitgesproken hoor

[Mark Wegener]

Super_ik schreef op 11 January 2003 @ 21:23:
jeetje, irritanter om te vullen, dan ben ik uitgesproken hoor

allow! er moeten dan nogsteeds 3 tabellen aangesproken worden. en daarnaast heb ik maar net genoeg ruimte om deze ene tabel naast de rest van de content opteslaan op de server

[Verwijderd]

Verwijderd schreef op 11 January 2003 @ 21:10:
[...]


Hoeveel scheelt dat dan als je stel je voor alle velden wilt lezen? niets toch?

Sybr_E-N schreef op 11 January 2003 @ 21:13:
Ja, als je die * gebruikt moet MySql eerst je hele tabel doorzoeken naar kolomen. Als je zelf kolommen opgeeft hoeft dat niet. Scheelt weer een paar tienden van een seconde.

daarom dus

[ACM]

Martin.Duane schreef op 11 January 2003 @ 21:12:
owh, ik zie net dat ik een verkeerde query heb genomen.

de simpele query is: SELECT artist, album, genre, DATE_FORMAT(date,'%M %D, %Y') AS datem FROM releases WHERE date = 20020528000000
deze selecteert 405 records in 1.1149380207062 seconde

die echt eenvoudige query duur de ook maar 0.0042691230773926 seconde.

Je doet een select op de date met een = vergelijking. Je hebt echter geen index/key op je date veld...

Kortom, mysql moet _alle_ records bij lang om te kijken welke voldoet. Dmv "create index ..." of "alter table add key.." (zie mysql manual voor meer info) kan je een index op date aanleggen zodat er een stuk sneller op gezocht kan worden.

Dat gekletst over alle velden opgeven ipv de select * is maar een marginale versnelling tov deze.

[Mark Wegener]

ik heb nu een index op veld date gezet, en bij die date query scheelt dat extreem, van 1.1429229974747 naar 0.013577938079834 sec.

Ik heb dus ook gelijk een index op artiest gezet, bij een query als: SELECT * FROM releases WHERE artist = 'Joe', is ie ook heel snel, maar bij een query als: SELECT * FROM releases WHERE artist LIKE '%joe%' , niet. Is hier ook nog een oplossing voor?

[chem]

je moet dan op joe% en niet %joe% zoeken.

[ACM]

Nee, helaas.

Bij een like-search waarvan het begin niet vaststaat is er geen index te gebruiken.
Je kan eventueel kijken naar mysql's fulltext-indexing system, of gewoon proberen zoveel mogelijk dat soort like-searches te voorkomen.

bij de variant die chem geeft kan je idd nog wel een index gebruiken.

[ Voor 14% gewijzigd door ACM op 11-01-2003 23:13 ]

[Mark Wegener]

Like searches voorkomen wordt erg lastig, gezien het zoveel records zijn, en er bij album titels heel vaak bij staat [single] [import] etc.

Full Text heb ik al naar gekeken ja, maar dan heb je dus een textveld nodig. Zou ik dan album als artiest veld moet maken, en dan een full text index over artiest en album leggen??

[Mark Wegener]

ik heb nu de volgende indices:

naam: id
type: UNIQUE
kardinaliteit: 383407
veld: releaseid

naam: search
type: INDEX (Fulltext)
kardinaliteit: 127802
veld: artist, album

horen beide kardinaliteiten niet gelijk te zijn?

als ik de query: SELECT artist, album FROM releases WHERE MATCH (artist,album) AGAINST ('joe'); werkt deze niet.

[ACM]

Nee, die kardinaliteiten hoeven niet gelijk te zijn

Als je hebt:
1, jasper
2, jasper
3, martin
4, martin
5, acm
6, ...

dan is de eerste 6voudig uniek, de tweede maar 3voudig.

En waarom je query niet werkt, wat bedoel je met "werkt niet" ?
'joecocker' vindt ie zo alsnog niet he?

[Mark Wegener]

sorry, ik bedoel er mee, dat er geen resultaten worden geretuneerd.

op 'joecocker' of 'joe cocker' vind ie niets. ' Joe Cocker' komt wel 9 keer voor.

1 kleine notitie, alle artiesten beginnen met een spatie dus vel Joe Cocker is ' Joe Cocker', dit was een foutje bij het rippen, maar lijkt me niet dat dit problemen geeft bij fulltext search.

de enige manier dat ik tot op heden goed door de database kan zoeken is met een query als: SELECT * FROM `releases` WHERE artist like '%joe%' and artist like '%cocker%'

[ Voor 15% gewijzigd door Mark Wegener op 12-01-2003 02:18 ]

[ACM]

Wat je denk ik veel beter kan doen is de artiesten in een aparte tabel stoppen...
en er met een getalletje naar verwijzen ipv gewoon de naam in je tabel proppen.

Dan hoef je niet _alle_ releases af te zoeken, maar enkele de artiest-tabel en daar zal de like-search ook niet zo heel erg zwaar zijn. Vooral omdat je door de index op het artistid heel snelle toegang hebt in de releases tabel.

Zelfde geldt eigenlijk ook voor je genre, als je alles in genre X wilt hoef je dan alleen het genre-id te zoeken en kan je daar prima alles mee vinden.

Het wordt dan zoiets:
genre: genreid, genrenaam
artist: artistid, artistnaam, ...
releases: releaseid, album(id?), date, genreid, artistid

Bovenstaand principe heet normaliseren, mocht je er meer over willen opzoeken

[Verwijderd]

Verwijderd schreef op 11 januari 2003 @ 22:21:
daarom dus

Klopt, ik heb het zelfs ff getest

[Verwijderd]

Maakt het uit wanneer je de varchar veranderd in 'text'? Misschien dat mysql hier anders mee omgaat..

[momania]

Martin.Duane schreef op 11 January 2003 @ 21:29:
[...]
allow! er moeten dan nogsteeds 3 tabellen aangesproken worden. en daarnaast heb ik maar net genoeg ruimte om deze ene tabel naast de rest van de content opteslaan op de server

Lijkt mij dat als je de genres in een aparte tabel zet, dat die dan vrij klein blijft.
Het scheelt dan iig een hoop extra onnodige voorkomens in je huidige tabel en dus ruimte.
Daarnaast kan je dan in je huidige tabel op een genre_id gaan zoeken, want betekend dat je daarmee zoekt op een integer ipv een varchar, wat ook weer meer performance opleverd.

[ Voor 17% gewijzigd door momania op 12-01-2003 21:54 ]