Score på MySQL-søgninger

November 13, 2008 · Posted in Udvikling · Comment 

Skal man søge i nogle store tekster i en MySQL-tabel kan man med fordel benytte sig af MySQL’s Full-Text søgninger. En funktion der er speciel anvendelig er dens mulighed for at rangordne efter hvor godt ens søgeord matcher det fundne. Jeg har lavet et lille eksempel:

Først lavede jeg en simpel tabel med et TEXT-felt med et FULLTEXT-index på:

1
2
3
4
5
6

CREATE TABLE lipsum (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  str TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY str (str)
) ENGINE=MyISAM;

Så hentede jeg en masse tekst fra lipsum.com og lagde det ind.

En søgning i tabellen kan se ud som følgende:

1
2
3

SELECT * FROM lipsum
WHERE MATCH (str)
  AGAINST ('tempus pede +lorem -ipsum' IN BOOLEAN MODE);

Hvor jeg søger efter tekster der indeholder ‘tempus’ eller ‘pede’ og ‘lorem’ men uden ‘ipsum’. Da søgningen er lidt kompliceret vil jeg måske gerne ha’ en vægtning på, så de mest relevante resultater kommer først. Dette løser jeg på følgende måde:

1
2
3
4
5

SELECT *, MATCH (str)
  AGAINST ('tempus pede +lorem -ipsum' IN BOOLEAN MODE) AS score
FROM lipsum
HAVING score > 0
ORDER BY score DESC;

Hvor jeg først laver søgningen og kalder den ‘score’ og senere indskrænker min søgning med HAVING til kun at indeholde dem hvor score er højere end 0. Bemærk at man skal bruge HAVING og ikke WHERE.

Tags: <, >

Tælle rækker, også med LIMIT

November 11, 2008 · Posted in Udvikling · Comment 

I mange tilfælde har man en webside der trækker en liste over ting fra en database. Det kan være en liste over brugere, annoncer, artikler m.m. Sådanne liste har det med at blive lange, og man vil inddele den i flere sider af fx 25 rækker.

I MySQL er det rimelig nemt at begrænse sit data. Det gøres bare ved at bruge LIMIT. Fx:

1

SELECT * FROM foo LIMIT 25;

Eller med et offset:

1

SELECT * FROM foo LIMIT 50, 25;

Hvor MySQL returnerer 25 rækker startende fra række 50.

Men så kommer problemet. Man skal jo også vise hvor mange sider ens data er inddelt i. Bruger man PHP’s mysql_num_rows() vil den kun returnerer 25 selvom man måske har flere tusinde rækker.

Jeg har desværre set en del der bare dropper LIMIT, og så beskærer dataene via PHP. Dette er selvfølgelig tåbeligt, da man arbejder med alle rækkerne hver gang.

Nogle suplerer deres SELECT med en tilsvarende query bare med SELECT COUNT(*) i stedet. Dette er bedre, men hvis ens query er meget kompliceret kan det give noget grim kode.

Den smarte løsning er at tilføje SQL_CALC_FOUND_ROWS til sin SELECT. Bruger man den kan man umiddelbart efter ens query har kørt hente antallet af rækker MySQL ville ha’ returneret, hvis den ikke var begrænset af en LIMIT. Dette gøres ved at kalde FOUND_ROWS().

Jeg har lavet et lille eksempel. Først opretter jeg en test-tabel:

1
2
3
4

CREATE TABLE letters (
    letter char(1) NOT NULL,
    PRIMARY KEY (letter)
);

Så indsætter jeg noget data (26 bogstaver):

1
2
3
4

INSERT INTO letters (letter) VALUES ('a'), ('b'), ('c'),
('d'), ('e'), ('f'), ('g'), ('h'), ('i'), ('j'), ('k'),
('l'), ('m'), ('n'), ('o'), ('p'), ('q'), ('r'), ('s'),
('t'), ('u'), ('v'), ('w'), ('x'), ('y'), ('z');

Nu vil jeg hente tre rækker, men for eksemplets skyld vil jeg springe de to første over:

1
2

SELECT SQL_CALC_FOUND_ROWS * FROM letters
ORDER BY letter ASC LIMIT 2, 3;

Hvilket returnerer c, d og e. Umiddelbart bagefter min SELECT skal jeg kalde FOUND_ROWS() for at få det totale antal rækker:

1

SELECT FOUND_ROWS() AS total_cnt;

Den returnerer en total_cnt på 26. Hvilket passer fint med det antal jeg indsatte.

Det er vigtigt at man kalder FOUND_ROWS() lige efter ens SELECT.

Tags: <, >


  • Om websmed.dk

    Jeg hedder Morten, og jeg har udviklet webapplikationer siden slutningen af 90'erne.

    Jeg vil her dele ud af min erfaring, og med jævne mellemrum poste nogle tips og tricks, samt løsningsforslag på generelle dagligdags problemstillinger.

    Jeg har en forkærlighed for PHP og MySQL, hvorfor I nok vil se flest indlæg der vedrører den gren af webudvikling.

    Jeg har en anden blog af mere personlig karakter på mbn.dk, hvor I kan finde kontaktmuligheder m.m.

    Mine indlæg vil tit være baseret på problemstillinger i mit daglige virke, eller i mit eget firma MRLYTICS, hvor jeg sælger kundeundersøgelser samt kundedatabaser.

    Denne side er hostet hos slicehost.

  • Skrevet på Twitter