[mysql-php] snelste total count id met limit zoals bv google

[Verwijderd]

Ik vroeg mij af wat de beste manier is om het volgende te realiseren:

stel ik heb een database met een miljoen entries.
daar laat ik een zoekactie op los met bepaalde criteria.
de uitkomt is bv. 2500 records die voldoen aan mijn zoekcriteria.
Nu wil ik ze natuurlijk niet allemaal op beeld hebben dus wil ik alleen de eerste 10 op beeld hebben. Maar ik wil wel graag het totaal van gevonden records weten.

concreet qua code had ik het volgende verzonnen:

code:

$total_hits = 0;
$limit_nr = 10;

$sql = "SELECT id, naam FROM db WHERE criteria = 'zoekoptie'";
$result = mysql_query ($sql);
if ($row = mysql_fetch_array($result)) {
    $result_line = "Gevonden:<br><br>";
    do {
        $total_hits++;
        if ($total_hits <= $limit_nr) {
            $db_id = $row["0"];
            $db_naam = $row["1"];
            $result_line = $result_line.$db_naam." (id ".$db_id.")<br>\n";
        }
    } while($row = mysql_fetch_array($result));
} else {
    $result_line = "niets gevonden";
}

print ("Totaal aantal gevonden entries: ".$total_hits."<br><br>".$result_line);

wat als resultaat iets laat zien als:

code:

Totaal aantal gevonden entries: 2500

Gevonden:

naam 1 (id 1)
naam 15 (id 15)
naam 26 (id 26)
naam 65 (id 65)
naam 566 (id 566)
naam 673 (id 673)
naam 77565 (id 77565)
naam 87455 (id 87455)
naam 93377 (id 93377)
naam 104455 (id 104455)

Is dit de snelste en beste manier om ziets te doen? Als een zoekquery zo'n 5 seconden duurt is het niet makkelijker om eerst een 'count(id) from xx where xx' en daarna een 'select xx from xx where xx limit 10' te doen, toch?

iemand anders nog ideeen ter verbetering of doe ik iets totaal overbodigs?

Google is hier nl zo snel in

[Verwijderd]

Ik zou in ieder geval de LIMIT van MySQL gebruiken, in plaats van veel records op te vragen en daar maar in een loopje een paar bruikbare records uitfilteren.
Verder heb je maar 1 query nodig, je zegt gewoon LIMIT beginpunt, aantal. Zijn er minder records dan het opgegeven aantal, dan krijg je gewoon een kleiner aantal records terug.

[Verwijderd]

Je dient je database structuur aan te passen. Bijvoorbeeld door nodes aan te leggen. En je moet zo snel mogelijk van tekst af. Dus je zoek optie moet in de database heel snel een id (integer) worden als je het zaakje snel wilt laten gaan...

[Verwijderd]

Mijn zoek optie kan geen integer zijn omdat juist op tekst gezocht moet worden. Dus in feite net als google zoeken op alleen maar tekst. Eigenlijk had ik een "LIKE '%zoekoptie%'" erin moeten zetten.

als ik een limit van mysql gebruik krijg ik er nooit in dezelfde query het totaal aantal gevonden records uit. Hij laat de eerste 10 goed zien wat sneller is omdat hij dan helemaal stopt.
De overige resultaten zal hij nooit laten zien dus ik zou niet kunnen weten dat het totaal aantal gevonden entries 2500 is. Je zou dan wel een alleen count(id) kunnen doen zoals ik al aangaf maar dan zouden er 2 min of meer dezelfde queries gedaan worden en het totaal 2 maal 5 seconden duren terwijl mijn voorbeeld sneller zal gaan ten koste van system resources.

het gaat mij om de snelheid, in veel mindere mate het aantal systemresources dat gebruikt wordt.

[Verwijderd]

Als je weet wat voor system-resources Google heeft, dan begrijp je ook wel waarom dat zo snel gaat

[ Voor 1% gewijzigd door Verwijderd op 14-01-2003 08:00 . Reden: typo ]

[whoami]

Volgens mij gaat jouw oplossing hoe dan ook trager zijn dan een select count() en dan nog eens een gewone select.
Gewoon omdat je alle rijen al gaat gaan doorlopen met je lus, terwijl als je met limit werkt de rijen pas worden gefetched als ze nodig zijn.

[raphidae]

Dat denk ik ook. Ik heb even een benchmark gedaan op een db met 700k records, en een select count() en dan een limited select is (veel) sneller dan 1 volledige select en dan een loopje.

[Janoz]

Verwijderd schreef op 14 January 2003 @ 00:58:
Mijn zoek optie kan geen integer zijn omdat juist op tekst gezocht moet worden. Dus in feite net als google zoeken op alleen maar tekst. Eigenlijk had ik een "LIKE '%zoekoptie%'" erin moeten zetten.

Owh? sinds waneer kan met een database geen nummertje meer aan een woord gehangen worden? Denk je werkelijk dat ze bij google een select blaat from blaat where paginainhoud like "%zoekwoord%" uitvoeren?

Ik moet me trouwens bij whoami aansluiten. Een count query gaat trouwens sowieso een stuk sneller dan een query waarin veel meer rows moeten worden aangemaakt. Daarnaast kan de DB gebruik maken van caching waardoor de 2e keer al veel voorwerk gedaan is.

[Verwijderd]

of doen ze misschien een
SELECT blaat FROM blaat WHERE MATCH(tekstindb) AGAINST("zoekwoord1 zoekwoord2")

?

[[]InTeR[]]

Goed indexeren helpt altijd

[Mickman]

Meeste mensen denken dat SQL alleen 'select' is.
Als je je in SQL verdiept kan je al je gewenste gegevens naar voren krijgen.
LIMIT is nog een begin!

[Verwijderd]

Verwijderd schreef op 14 januari 2003 @ 00:58:
Mijn zoek optie kan geen integer zijn omdat juist op tekst gezocht moet worden. Dus in feite net als google zoeken op alleen maar tekst. Eigenlijk had ik een "LIKE '%zoekoptie%'" erin moeten zetten.

Nee je moet dus een nummer aan een woord hangen en een woord kan in meerdere teksten voorkomen. En dan zoek je dus wel op nummers, dat is wat ik zeg. Je ontwerp is fout en zal dus nooit snel worden. Je moet gaan indexeren. Je kunt het het behoorlijk ver doorvoeren, wat vaak gedaan wordt bij grote informatie systemen. Bijvoorbeeld door een tabel van woorden te hebben van aaa* t/m bbb*, en een tabel bbc* t/m ccc, etc.

En dat bedoel ik met nodes aanmaken. Op die manier wordt het wel sneller. maar zorg dat je zo snel mogelijk je select met integers uitvoert...

[Verwijderd]

Je moet denk ik geen like gebruiken voor dit soort dingen (dat lukt nooit met 1 miljoen entiries) ... Hiervoor heb je meestal (met de db meegeleverde functionaliteiten) ... Anders moet je gewoon zelf de woorden in de entries indexeren... De kans namelijk dat woorden terug komen is erg groot... Mijn dikke van dale heeft iets van 3000 pagina's met zo'n 30 woorden per pagina (dus totaal 90.000 woorden waarschijnlijk wat meer). Du s waarschijnlijk komt je tabel met woorden hier nooit bovenuit. Deze kan je koppelen met je entries, hierdoor hoef niet meer 1 miljoen entries tekst door te ploegen meer slecht een tabel met 90.000 entries die je ook weer kan indexeren. (Heb dit geloof net al ergens in een topic gezegd)

edit:
markvleth was me voor ... na ja ik was nog bezig met woorden tellen in mijn van dale

[ Voor 8% gewijzigd door Verwijderd op 14-01-2003 21:54 ]

[Verwijderd]

Als je MySQL 4 of hoger hebt kan je found_rows() gebruiken.

http://www.mysql.com/doc/en/Miscellaneous_functions.html

FOUND_ROWS()
Returns the number of rows that the last SELECT SQL_CALC_FOUND_ROWS ... command would have returned, if it had not been restricted with LIMIT.

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. Note that if you are using SELECT SQL_CALC_FOUND_ROWS ... MySQL has to calculate all rows in the result set. However, this is faster than if you would not use LIMIT, as the result set need not be sent to the client. SQL_CALC_FOUND_ROWS is available starting at MySQL version 4.0.0.

succes

[Verwijderd]

Mickman schreef op 14 januari 2003 @ 20:47:
Meeste mensen denken dat SQL alleen 'select' is.
Als je je in SQL verdiept kan je al je gewenste gegevens naar voren krijgen.
LIMIT is nog een begin!

Toch moet je niet veel meer aan de db overlaten. Eigelijk moet alles met een paar basis statements uitgevoerd worden. Het ontwerp moet eigelijk de snelheid opleveren.

De implementatie van LIMIT is trouwens ook belangrijk om in afweging te nemen bij het antwoord op de vraag of hij efficient is. Bij een efficiente implementatie zijn namelijk join bewerkingen af te kappen. Verder is LIMIT volgens mij geen standaard sql procedure dus al eigelijk af te raden.

[Janoz]

Verwijderd schreef op 14 januari 2003 @ 21:47:
Bijvoorbeeld door een tabel van woorden te hebben van aaa* t/m bbb*, en een tabel bbc* t/m ccc, etc.

Het heeft niet zo heel veel nut om zelf op deze manier proberen de boel sneller te maken. Dit gebeurt namelijk intern al waneer je een key definieerd op dat veld.

[Verwijderd]

En is dat gegarandeerd Jn2k? Misschien met een inferieur db systeem als MySQL

[whoami]

Verwijderd schreef op 14 January 2003 @ 23:40:
[...]

Toch moet je niet veel meer aan de db overlaten. Eigelijk moet alles met een paar basis statements uitgevoerd worden. Het ontwerp moet eigelijk de snelheid opleveren.

De meeste snelheid haal je toch volgens mij door database-taken aan de db over te laten.
Maak gebruik van de kracht van de database.

De implementatie van LIMIT is trouwens ook belangrijk om in afweging te nemen bij het antwoord op de vraag of hij efficient is. Bij een efficiente implementatie zijn namelijk join bewerkingen af te kappen. Verder is LIMIT volgens mij geen standaard sql procedure dus al eigelijk af te raden.

Het is waarschijnlijk geen standaard keyword nee, maar SQL Server heeft ook TOP. Trouwens, bij een grote applicatie ga je je SQL statements niet in je code gaan proppen, maar ga je gebruik maken van stored procedures. Als je dan naar een andere db overstapt, moet je die SP's toch ook porten.

[ Voor 3% gewijzigd door whoami op 15-01-2003 10:51 ]

[Verwijderd]

Verwijderd schreef op 14 januari 2003 @ 21:47:
[...]

Nee je moet dus een nummer aan een woord hangen en een woord kan in meerdere teksten voorkomen. En dan zoek je dus wel op nummers, dat is wat ik zeg. Je ontwerp is fout en zal dus nooit snel worden. Je moet gaan indexeren. Je kunt het het behoorlijk ver doorvoeren, wat vaak gedaan wordt bij grote informatie systemen. Bijvoorbeeld door een tabel van woorden te hebben van aaa* t/m bbb*, en een tabel bbc* t/m ccc, etc.

En dat bedoel ik met nodes aanmaken. Op die manier wordt het wel sneller. maar zorg dat je zo snel mogelijk je select met integers uitvoert...

Ik heb engelse en nederlandse tekst die doorzocht moeten worden (in totaal is de sqldb 1.3gb groot). Dus stel ik heb 100.000 woorden, elk woord kan in meerdere teksten zitten dus. Het woord de bv zal vast wel in 90% van de teksten zitten. Dus ik denk zelfs dat je nu juist veel meer records krijgt dan het aantal teksten dus dat schiet nog niet op. Een tekst met gemiddeld 100 woorden zou dus voor elke tekst (dus record) 100 entries krijgen met een nummertje.

Ik denk dat het omtoveren naar een fulltext search of upgraden naar 4.0 met found_rows() het snelste zal gaan.

Janoz schreef op 14 January 2003 @ 09:36:

[...]


Owh? sinds waneer kan met een database geen nummertje meer aan een woord gehangen worden? Denk je werkelijk dat ze bij google een select blaat from blaat where paginainhoud like "%zoekwoord%" uitvoeren?

Het kan wel, maar is in mijn geval niet handig bedoel ik dus. En ik heb niet zoveel ruimte en machines als google om per letter een aparte bak te gebruiken zodat de totale calculatietijd nog steeds snel gaat

[jvdmeer]

Verwijderd schreef op 15 januari 2003 @ 14:03:
Ik heb engelse en nederlandse tekst die doorzocht moeten worden (in totaal is de sqldb 1.3gb groot). Dus stel ik heb 100.000 woorden, elk woord kan in meerdere teksten zitten dus. Het woord de bv zal vast wel in 90% van de teksten zitten. Dus ik denk zelfs dat je nu juist veel meer records krijgt dan het aantal teksten dus dat schiet nog niet op.

Volgens mij bedoeld markvleth het anders. Hij bedoelt (volgens mij) een tabel met alle voorkomende woorden met een id.
Daarnaast heb je dus nog een tabel nodig die de woorden koppelt met de teksten.

In tegenstelling tot wat je denkt zal de ruimte in de db juist afnemen, en de snelheid toenemen. Jij noemt als voorbeeld het woordje 'de', dat in 90% van de teksten voorkomt. Dat houdt dus in dat er een record voorkomt in de woordtabel, en daarnaast per tekst 1 record in de woord<->tekst-koppeltabel. Voor dit specifieke woordje 'de'lijkt het een hoop overhead, maar bij een wat langer woord, dat meer dan 1 keer voorkomt in een tekst werkt het besparend.

Verwijderd schreef op 15 januari 2003 @ 14:03:Een tekst met gemiddeld 100 woorden zou dus voor elke tekst (dus record) 100 entries krijgen met een nummertje.

In de meest teksten komen woorden meer dan 1 keer voor. Je zou zelfs kunnen overwegen om in de koppeltabel, de frequentie van het woord op te nemen, om dus zelfs nog significantie mee te kunnen nemen.

Verwijderd schreef op 15 januari 2003 @ 14:03:Ik denk dat het omtoveren naar een fulltext search of upgraden naar 4.0 met found_rows() het snelste zal gaan.

Wat denk je dat er gebeurt zodra je gaat ombouwen naar fulltextsearch? Dan woorden de eerdergenoemde tabellen toch aangemaakt, omdat dit een van de eenvoudigste methoden is voor snelle full-text-search.

Verwijderd schreef op 15 januari 2003 @ 14:03:Het kan wel, maar is in mijn geval niet handig bedoel ik dus. En ik heb niet zoveel ruimte en machines als google om per letter een aparte bak te gebruiken zodat de totale calculatietijd nog steeds snel gaat

Ik herhaal even een eerder bericht van jou in dit draadje:

Verwijderd schreef op 14 januari 2003 @ 00:58:het gaat mij om de snelheid, in veel mindere mate het aantal systemresources dat gebruikt wordt.

[ Voor 2% gewijzigd door jvdmeer op 16-01-2003 10:14 . Reden: lange post, even wat witregels er tussenuit. ]