[php / MySQL] Grote database, lange zoektijd

[Verwijderd]

Hallo,

Ik ben net begonnen met php en mysql. Ik wil een database maken voor en boekwinkel met daarin alle boeken uit hun bestelbestand. Deze heb ik al in een mysql database geïmporteerd. Maar nu gaat het (verdorie?) om (+-) 175000 records.
Met php heb ik een heel eenvoudig scriptje geschreven waarmee ik in de database kan zoeken op titel, maar hoe lang duurt het zoeken. Precies, té lang. Om precies te zijn 18 seconden. Ik vind dat veel te veel. En volgens mij moet het ook sneller kunnen.

Maar waar het me om gaat om mee te beginnen is: Ligt het aan de specs van de machine waarop mysql en php (webserver) draait of aan het php script, of klopt het dat het zoeken zo lang duurt (laatste kan ik me niet voorstellen). Of tot slot de mysql database structuur, met indexen en zo.

De specs van de machine:
* freesco router
* 100 mhz cpu
* 16 mb RAM met 40 mb swap file
* Apache webserver met php en perl build-in
* MySQL

Ik draai de database via het lokale netwerk.

De kolommen tot nu toe: isbn (10), titel (40), auteur (30), prijs (, bindwijze (4), verschijningsdatum (

[ Voor 0% gewijzigd door Verwijderd op 01-09-2002 19:49 . Reden: iets vergeten ]

[Super_ik]

holey shit, die config van die bak is veel te weinig, zet er eerst maar s een lekker pctje neer
alleen al een bergje ram erbij zou veel helpen

[ACM]

Hoe zoek je?
En hoelang duurt het om de gevonden records te fetchen op ID ?
(dus alle gevonden ID's verzamelen en dezelfde query herhalen maar dan zonder je zoek-code, maar met een where dingesID IN (lijstje, van, id's) )

[Verwijderd]

De spec's van de PC zijn niet grandioos, maar bij een niet al te zwaar database gebruik (wat betreft van het aantal gebruikers) zou het sneller moeten dan 18 seconden.

Je zoekt op titel. De tabel is waarschijnlijk gesorteerd op ISBN. Afhankelijk van je exacte tabelstructuur betekent dit dat ALLE titels worden doorlopen. Al heb je een 'vette' pc dan nog hangt de zoektijd af van je tabelstructuur.

De nuttigste tip die ik je kan geven (waar je bij alle situaties wat aan hebt): lees een goed boek over Databases. Koop een boek waar niet direct een voorkeur voor een DBMS (database management system, b.v.: mysql, Ingres, Oracle, MS SQL server) wordt uitgesproken maar wat de nadruk legt op database structuren en de werking van zoek algoritmes.

[Verwijderd]

ACM schreef op 01 september 2002 @ 20:25:
Hoe zoek je?
En hoelang duurt het om de gevonden records te fetchen op ID ?
(dus alle gevonden ID's verzamelen en dezelfde query herhalen maar dan zonder je zoek-code, maar met een where dingesID IN (lijstje, van, id's) )

Sorry, ik zou graag begrijpen wat je bedoelt, maar dat zit er nog niet in. Databases (anders dan MS Access) zijn helemaal nieuw voor mij en programmeren al helemaal. Ik moet me er goed in verdiepen (wat ik ook zeker ga doen). Ik ben meer het hardware (ook netwerken) figuur tot nu toe, maar ik ga door. En dit boeit me en kan het heel erg goed gebruiken. Dus ga ik er zeker tijd in steken. Hierna zal ik, als het probleem nog bestaat, terugkomen met deze vraag. Maar bedankt voor deze reactie.

Wat de specs betreft, tsja, kan niet beter nu. Ik had extra geheugen gekocht (simm, leuk hoor). En om de 1 of andere reden pakt die pc het niet, waarschijnlijk het moederbord. Maar dat is niet jullie probleem. Ik probeer het op een andere pc.

Wat betreft de tip over het algemeen database leren. Ik vind het een goede tip, en zoals ik eerder al zei in deze post, ik ga me er zeker in verdiepen.

Ik hoop dat ik het allemaal een beetje krijg zoals ik wil. Tot nu toe weet ik waar ik het zoeken moet. Bedankt.

Wat ik nog vergat: De database gebruik ik op dit moment alleen en zal nooit door meer dan 5 mensen tegelijk gebruikt worden denk ik.

[whoami]

Gebruik je indexen op je tabellen, en zo ja, waar liggen die indexen?
Worden de indexen wel gebruikt? (Dit kan je eventueel checken door een execution plan van je query op te vragen).

Ivm het leren over databases: ik geloof dat er in de P&W FAQ wel een stukje staat....

Welkom in P&W (FAQ-13/7/2002)
Zoek hierin eens de 'inhoudelijke FAQ' op.... De 2de post in dat topic is dat.
Check daar eens de secties over SQL en Database indexering

[corani]

Als je je CPU niet kunt upgraden, doe er dan in ieder geval wat geheugen bij, dan wil ook wel eens schelen

[ACM]

Verwijderd schreef op 01 september 2002 @ 22:01:
Sorry, ik zou graag begrijpen wat je bedoelt, maar dat zit er nog niet in.

Nou, heel simpel

Verwijderd schreef op 01 september 2002 @ 19:47:
Ik wil een database maken voor en boekwinkel met daarin alle boeken uit hun bestelbestand. Deze heb ik al in een mysql database geïmporteerd. Maar nu gaat het (verdorie?) om (+-) 175000 records.

Mooi, vrij veel maar best nog wel te doen voor jouw hardware, ook al moet je rekening houden met enige trage respons.

Met php heb ik een heel eenvoudig scriptje geschreven waarmee ik in de database kan zoeken op titel, maar hoe lang duurt het zoeken.

En hier beginnen we dan
Je zoekt op titel, hoe doe je dat precies?

Precies, te lang. Om precies te zijn 18 seconden. Ik vind dat veel te veel. En volgens mij moet het ook sneller kunnen.

Dat kan vast wel sneller maar hangt wel ervanaf hoe je zoekt.
Je krijgt uit die zoekquery als het goed is ook een stel boeken terug (oid).
Als je nou die boeknummers allemaal in een query zet ala:

code:

SELECT * FROM boeken WHERE boeknummer IN (boeknr1, boeknr2, boeknr3);

En kijkt hoelang dat duurt weet je in ieder geval wat de absolute minimum tijd is die je ook maar KAN halen. Simpelweg omdat bovenstaande als het goed is de snelst mogelijke toegang tot je data is.
Stel dat duurt 0.5 seconde, dan is die 18 seconden om te zoeken natuurlijk erg sloom.
Maar als dit al 10 seconden duurt, dan valt die 18 seconden eigenlijk wel mee.

Maar waar het me om gaat om mee te beginnen is: Ligt het aan de specs van de machine waarop mysql en php (webserver) draait of aan het php script, of klopt het dat het zoeken zo lang duurt (laatste kan ik me niet voorstellen). Of tot slot de mysql database structuur, met indexen en zo.

De machine is erg sloom en heeft erg weinig geheugen om zowel apache+php+perl EN mysql te draaien, dat is een ding wat zeker is.
Wat echter belangrijk is, zijn dus de methode hoe je zoekt (wat voor query gebruik je?) en de exacte specificaties van je tabel (hoe heb je hem gemaakt en/of als je phpmyadmin gebruikt toon ons eens een zgn. structuur dump)

[Verwijderd]

De database heet 'boeken' en heeft tot nu toe 1 tabel, 'titel'.

# Table structure for table `titel`
#

CREATE TABLE titel (
isbn varchar(10) NOT NULL default '0',
titel varchar(40) NOT NULL default '',
auteur varchar(26) NOT NULL default '',
prijs text NOT NULL,
versweek varchar(6) NOT NULL default '',
bindwijze char(3) NOT NULL default '',
PRIMARY KEY (isbn),
KEY isbn (isbn),
KEY isbn_2 (isbn)
) TYPE=MyISAM;


De 2e key isbn_2 is erg overbodig, maar heb ik ook niet gedaan omdat ik daar nou eens zin in had

Dan mijn php script:


# <html>
# <body>
# <?php
# if($zoek){
# $db = mysql_connect("localhost", "mijnusername***", "mijnpassword***");
# mysql_select_db("boeken",$db);
# echo $zoek;
# $result = mysql_query("SELECT * FROM titel WHERE titel LIKE '%$zoek%'",$db);
# echo "<table border=1>\n";
#echo "<tr><td>ISBN</td><td>Titel</td><td>Auteur</td><td>prijs</td></tr>\n";
# while ($myrow = mysql_fetch_row($result)) {
# printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%#s</td></tr>\n", $myrow[0], $myrow[1], $myrow[2], $myrow[3]);
# }
# echo "</table>\n";
# }else
# ?>
# <form action="index.php3" method="GET">
# <input type="text" name="zoek" value="">
# <input type="submit" value="Zoeken">
# </form>
# </body>
# </html>

Dit lijkt me alle informatie die ik kan geven.
NEE, fout: Een regeltje dan uit de database.
9069743353; MARTIN LUTHER KING JR. AUTOBIOGRAFIE; KING, M.L.; 23,50; 199848; ING

(ik heb er voor nu even ; tussengezet. Om dus de veldscheiding weer te geven.
Misschien dat jullie nu gelijk snappen waarom het zo lang duurt. Of toch de specs van die half gare bak (was ook alleen bedoeld als router, maar ik kon het niet laten).

[Verwijderd]

Problemen:
1) Omdat je nieuw bent met programmeren en databases heb je hebt te weinig inzicht in wat er werkelijk moet gebeuren om dit op te zoeken, dat je niet begrijpt waarom het zo lang duurt
2) Je database is redelijk groot voor de machine config (CPU, geheugen, waarschijnlijk ook disk I/O system)
3) Zoeken met 'LIKE' is traag
4) Er wordt geen index aangemaakt op de kolom waarin je zoekt, en als die er al zou zijn, wordt die niet gebruikt door de manier waarop je LIKE gebruikt

Advies:
1) Lees wat meer over programmeren, databases en indexes (zie links en info in FAQ), en doe ervaring ermee op (d.w.z. expirimenteer wat verschillende wijzigingen in bijv. je query voor gevolgen hebben)
2) Gebruik een betere machine, of minder records in je tabel
3) Probeer liever '=' te gebruiken i.p.v. LIKE, als dat mogelijk is
4) Maak een index op de kolom titel, en pas je LIKE aan, zodat je alleen op begin van titels zoekt:

code:

SELECT * 
FROM titel 
WHERE titel LIKE '$zoek%'

HTH

offtopic:
Guess who's back ... back again .... X is back, tell a friend

[Verwijderd]

Deze query geeft supersnel resultaat:
# $result = mysql_query("SELECT * FROM titel WHERE isbn='902825062x'",$db);

Dit is dus ook de geïndexeerde kolom, maar.......... deze query
# $result = mysql_query("SELECT * FROM titel WHERE isbn LIKE 9060127706",$db);
is net zo traag als het zoeken op titel. Zou het zoeken met LIKE dan toch te veel vragen van mijn systeempje??? Helaas, naar mijn idee zit dar er wel in.

Dit bijv.
# $result = mysql_query("SELECT * FROM titel WHERE titel='paardenfluisteraar'",$db);
biedt ook geen uitkomst, okee dan misschien 1 seconde, maar dan hebben we het ook wel gehad. Deze kolom is niet geïndexeerd.

[raptorix]

$result = mysql_query("SELECT * FROM titel WHERE titel LIKE '%$zoek%'",$db);

Maakt naar mijn weten geen gebruik van een index, dat de search traag is, is dus begrijpelijk.

Ten eerste zou ik gebruikers niet te snel hier gebruik van laten maken, like "$zoek%" is dus al een stuk efficienter.

[ACM]

Voor snel zoeken in text-velden zou je dit nog kunnen gebruiken:
http://www.mysql.com/doc/en/Fulltext_Search.html

Is wel vrij complex, zeker voor een beginner.

[Verwijderd]

Zou et ook niet wat schelen als je ISBN als BIGINT, VERSWEEK als INT en PRIJS als DOUBLE oid doet?

[Alex]

ACM schreef op 02 september 2002 @ 07:51:
Voor snel zoeken in text-velden zou je dit nog kunnen gebruiken:
http://www.mysql.com/doc/en/Fulltext_Search.html

Is wel vrij complex, zeker voor een beginner.

Is dit nu de beste manier van zoeken? Hoe doet T.net het, ook met FT?

[ACM]

Verwijderd schreef op 02 september 2002 @ 08:12:
Zou et ook niet wat schelen als je ISBN als BIGINT, VERSWEEK als INT en PRIJS als DOUBLE oid doet?

Is wel zo netjes, maar de zoekperformance zal er niet door verbeteren

prog-konijn schreef op 02 september 2002 @ 08:15:
Is dit nu de beste manier van zoeken? Hoe doet T.net het, ook met FT?

Tweakers.net (zowel GoT als de frontpage) gebruiken een eigen "full-text" algoritme.
De mysql manier is lang niet altijd de beste denk ik, wat precies de afwegingen zijn geweest om niet de fulltext-engine van mysql te gebruiken weet ik eigenlijk niet, er zullen wel wat beperkingen inzitten waardoor ie niet goed met onze database overweg kan oid.

[Arnout]

Ik weet niet wat je mogelijkheden zijn, maar de combinatie Apache + PHP i.c.m. MySQL op een lichte server is verre van ideaal qua snelheid. Sinds ik de webserver heb gescheiden van de database server (MySQL draait nu nog op het trage bakkie) is zijn de prestaties met 6x toegenomen, terwijl de servers ook nog es 160km uit elkaar staan.

[xoror]

Ik heb wat linkjes met beschrijvingen over indices enzo

http://techdocs.postgresq...cs/pgsqladventuresep1.php
http://techdocs.postgresq...cs/pgsqladventuresep2.php
http://techdocs.postgresq...cs/pgsqladventuresep3.php

de voorbeelden zijn voor pgsql maar het princiepe geldt natuurlijk overal.

euhm je moet vooral naar 2 en 3 kijken

[thomaske]

Verwijderd schreef op 02 september 2002 @ 08:12:
Zou et ook niet wat schelen als je ISBN als BIGINT, VERSWEEK als INT en PRIJS als DOUBLE oid doet?

Jep, goed idee, alleen ISBN kan ook eindigen met een x, dus dat moet een varchar zijn..

[Janoz]

Verwijderd schreef op 02 september 2002 @ 08:12:
Zou et ook niet wat schelen als je ISBN als BIGINT, VERSWEEK als INT en PRIJS als DOUBLE oid doet?

En prijs als decimal aangezien het altijd maar 2 decimalen heeft

[HenkS]

en probeer ook select * te vermijden als t ff kan

liever : select isbn, title from ...... ofzo


en als je alle velden toch moet hebben, weet ik niet of dit sneller is:

select * from ...


of een select isbn from ....

en dan daarna : select * from .. where isbn=$myrow["isbn"];

zou ik wel eens willen weten... volgens mij is bij een hele grote db de 2e manier sneller ook al doe je 2 queries, of vergis ik me daarin?

[cameodski]

HenkS schreef op 02 september 2002 @ 14:42:
en probeer ook select * te vermijden als t ff kan

liever : select isbn, title from ...... ofzo


en als je alle velden toch moet hebben, weet ik niet of dit sneller is:

select * from ...


of een select isbn from ....

en dan daarna : select * from .. where isbn=$myrow["isbn"];

zou ik wel eens willen weten... volgens mij is bij een hele grote db de 2e manier sneller ook al doe je 2 queries, of vergis ik me daarin?

Ik ben bang dat je je inderdaad vergist. In het meest ongunstige geval, zal MySQL hetzelfde doen als wat je probeert te bereiken met aparte queries. Maar dan in één query, wat ten allen tijde efficiënter is.

Is er trouwens ook iemand die mij kan vertellen waarom bij een filter met LIKE waarbij alleen aan het eind een wildcard staat, geen gebruik gemaakt wordt van een eventuele index. In MSSQL gebeurt dit bijvoorbeeld wel.

[[ash]]

Is een index op het veld "titel" geen idee

[whoami]

cameodski schreef op 02 september 2002 @ 15:14:
[...]


Is er trouwens ook iemand die mij kan vertellen waarom bij een filter met LIKE waarbij alleen aan het eind een wildcard staat, geen gebruik gemaakt wordt van een eventuele index. In MSSQL gebeurt dit bijvoorbeeld wel.

Is dat zo bij MySQL? Dat zou ik zeer vreemd vinden.
Mocht er geen filter gebruikt worden indien je een wildcard gebruikt aan het begin van je filtercriteria dan kan ik er in komen.
Zo dus:

code:

LIKE '%abc'

Ik geloof dat er in dat geval bij SQL Server geen gebruik kan gemaakt worden van indexen.
Als je LIKE 'abc%' doet, en er worden dan geen indexen gebruikt, zou ik dat straf vinden....

[cameodski]

whoami schreef op 02 september 2002 @ 15:22:

[...]


Is dat zo bij MySQL? Dat zou ik zeer vreemd vinden.
Mocht er geen filter gebruikt worden indien je een wildcard gebruikt aan het begin van je filtercriteria dan kan ik er in komen.
Zo dus:

code:

LIKE '%abc'

Ik geloof dat er in dat geval bij SQL Server geen gebruik kan gemaakt worden van indexen.
Als je LIKE 'abc%' doet, en er worden dan geen indexen gebruikt, zou ik dat straf vinden....

Uit het bericht van Hanzje van 00:45 lijkt het er inderdaad op dat er bij LIKE nooit gebruikt gemaakt wordt van indexen.
Bij MSSQL kan er inderdaad geen gebruik gemaakt worden van een index als de zoekstring met een wildcard begint.

[xoror]

ehm ik heb net even getest met mysql met een tabel van paar duizend records.
er wordt wel degelijk een index gebruikt als er gezocht wordt op het patroon 'patroon%'.

er wordt geen gebruik gemaakt van een index als het patroon '%patroon%' is.
er moet natuurlijk wel een index gedefinieerd zijn op de betreffende kolom that is.

je kan dit zelf testen met explain. je moet even in de gaten houden dat je genoeg records in je tabel heb. bij lage aantal records is een sequential scan (full table scan) meestal sneller, waardoor de query planner een sequential scan gaat doen. Laat je daar dus niet door misleiden.

[ACM]

xoror schreef op 02 september 2002 @ 16:14:
bij lage aantal records is een sequential scan (full table scan) meestal sneller, waardoor de query planner een sequential scan gaat doen. Laat je daar dus niet door misleiden.

Ik weet niet of mysql dat onderscheid wel kan maken...

Dat postgres dat doet is me bekend Maar mysql valt kwa planner nog wel es tegen.