[mySQL] een resultset hergebruiken oid.

[Spider.007]

Ik heb een database met daarin een groot aantal woorden. Nu wil ik door deze woorden heen gaan zoeken. Woorden die beginnen met de gezochte tekst moeten eerder worden laten zien dan worden waar de tekst slechts in voor komt.

Momenteel maak ik gebruik van een drietal queries;
* de eerste zoekt een exacte match
* de tweede zoekt woorden die beginnen met het gezochte; maar geen exacte resultaten zijn
* de derde zoekt woorden waar de gezochte term in voor komt; maar geen exacte resultaten; of woorden die beginnen met zijn.


Het probleem is de snelheid; de tijden zijn resp. 1.03, 0.74 en 0.81 sec. Dit wil ik versnellen.

Nu had ik het volgende bedacht; ik doe een query op woorden waar de gezochte term in voor komt. Binnen deze resultset ga ik dan zoeken met de queries zoals hierboven genoemd. De drie queries hoeven dan door minder rijen heen te zoeken en zijn dus een stuk sneller.

Alleen nu het probleem; ik krijg dit niet voor elkaar. Kan ik dit door mySQL laten doen, of moet ik alle resultaten binnen halen en met PHP gaan verwerken?

Alvast bedankt!

[ Voor 3% gewijzigd door Spider.007 op 11-01-2003 19:03 ]

[Gert]

Iets van als lengte gevonden = lengte gezoch dan een waarde 3, als begint met gezocht dan waar 2, en anders als gevonden moet het wel ergens verstopt zitten en dan waarde 1 en dan soorten op waarde desc.

Hoewel volgens mij if statements en cases erg fout zijn in sql moet het met LENGTH() INSTR() en IF() wel lukken.

[Verwijderd]

Heb je al indexes gebruikt....?

[Spider.007]

Gert schreef op 11 januari 2003 @ 21:05:
Iets van als lengte gevonden = lengte gezoch dan een waarde 3, als begint met gezocht dan waar 2, en anders als gevonden moet het wel ergens verstopt zitten en dan waarde 1 en dan soorten op waarde desc.

Hoewel volgens mij if statements en cases erg fout zijn in sql moet het met LENGTH() INSTR() en IF() wel lukken.

Je stelt dus voor om alles in een grote query te dumpen? Dat is wellicht een goed idee; de vraag is of de kwaliteit van de resultaten een beetje hoog blijft. Ik ga het iig proberen.

Verwijderd schreef op 11 January 2003 @ 21:13:
Heb je al indexes gebruikt....?

Ik maak gebruik van indexes; maar ik zie zo snel even niet hoe ik die kan gebruiken om een snelheidswinst te krijgen? Waar ik wel aan heb zitten denken is het meegeven van alle indexes die uit de 'nieuwe' querie rollen aan de queries 1, 2 en 3

[ACM]

Je resultset zou je eventueel dmv een temporary table op kunnen slaan.

Dus iets ala:

SQL:

create temporary table blaat ( 
-- vergeet niet een stel indices mee te geven voor sneller sorteren
);
insert into blaat SELECT ..., waarde_om_aan_te_geven_dat_je_volledig_zocht from je_zoek_tabel where ...;
insert into blaat SELECT ..., waarde_om_aan_te_geven_dat_je_op_begin_zocht from je_zoek_tabel where ...;
insert into blaat SELECT ..., waarde_om_aan_te_geven_dat_je_%woord%_zocht from je_zoek_tabel where ...;

select ... from blaat ORDER by laatste_veldje_voor_de_zoek_opties, zoekscore;

etc

[Spider.007]

Dat is een goed ide wat ik ook zeker ga meenemen. Komt er een oplossing uit (daar ga ik morgen dat is vandaag inmiddels aan zitten) dan zal ik hier posten wat de snelheidswinst is. De tijdelijke tabel vindt ik heel 'doenbaar'

[Spider.007]

Omdat een tijdelijke tabel te weinig tijdwinst op leverde heb ik het nu zo opgelost dat ik eigenlijk alleen (een gewijzigde) query drie uitvoer. Wat ik nu doe is namelijk een select op LIKE "%woord%"; hier komen dus de juiste resultaten uit naar voren. (deze neemt 0.82 sec in beslag; al een grote verbetering ten opzichte van de eerste tijden.)

Het probleem wat ik hier echter bij heb is dat de resultaten niet goed worden gesorteerd. Het wordt momenteel op alfabet gerangschikt, maar wat ik eigenlijk wil is dat de exacte match bovenaan komt te staan; en daaronder de woorden die beginnen met het gezochte woord; en daaronder de rest. Is dit te doen met een soort van Reguliere Expressie aan mySQL's kant? Ik heb al zitten proberen het met WHERE MATCH te doen; maar dat lukt niet.

[ Voor 5% gewijzigd door Spider.007 op 12-01-2003 23:12 ]

[ACM]

select ... (IF woordveld = 'woord' THEN 1 ELSE IF woordveld LIKE 'woord%' THEN 2 ELSE IF woordveld LIKE '%woord%' THEN 3 END IF) as sorter from ... where ... ORDER BY sorter, woordveld

oid kan misschien nuttig zijn?
Zou kunnen dat mysql dit "WHEN" of iets van CASE noemt.

[Verwijderd]

of een union all, met 2 queries..

code:

select 
  woord as Match,  
  cast(1 as integer) as Sortering,
from
  mytable
where
  mytable.woord='woord'

union all

select 
  woord as Match, 
  cast(2 as integer) as Sortering
from
  mytable
where
  mytable.woord 'like '%woord%" and 
  mytable.woord<>'woord'


order by Sortering, Match

//edit
wat veranderingen aan query

[ Voor 80% gewijzigd door Verwijderd op 13-01-2003 01:56 ]

[Verwijderd]

Als je het helemaal snel wilt hebben (en ruimte hebt!) kan je van te voren je text indexeren met woorden en via een koppel tabel linken naar de eigenlijke text. (bedenk wel dat dan de insert veel meer tijd kost en dat fulltext search ook een goede optie is). Heb dit nog nooit in mysql geprobeerd maar weet van andere DB dat het veel sneller is dat dan met 'likes' door de text gaan. Verder is, denk ik, het indexeren van woorden (voor nog snellere lookup) waarschijnlijk nogeens kostbaar qua ruimte. Maar probeer het eens. Misschien bevalt je het.

[Spider.007]

ACM schreef op 13 January 2003 @ 01:43:
select ... (IF woordveld = 'woord' THEN 1 ELSE IF woordveld LIKE 'woord%' THEN 2 ELSE IF woordveld LIKE '%woord%' THEN 3 END IF) as sorter from ... where ... ORDER BY sorter, woordveld

oid kan misschien nuttig zijn?
Zou kunnen dat mysql dit "WHEN" of iets van CASE noemt.

Dit ziet er uit als een hele goede oplossing Deze ga ik implementeren en ik laat het uiteraardt even horen wat ervan geworden is.

[Glimi]

Spider.007 schreef op 12 januari 2003 @ 23:10:
Omdat een tijdelijke tabel te weinig tijdwinst op leverde heb ik het nu zo opgelost dat ik eigenlijk alleen (een gewijzigde) query drie uitvoer. Wat ik nu doe is namelijk een select op LIKE "%woord%"; hier komen dus de juiste resultaten uit naar voren. (deze neemt 0.82 sec in beslag; al een grote verbetering ten opzichte van de eerste tijden.)

Even een opmerking tussendoor gooiend:
LIKE '%woord%' zal _NIET_ gebruik maken van je indexen en is bere! traag!. Dit omdat %woord niet als constante gezien/geoptimaliseerd kan worden. Zie hiervoor de MySQL manual bij LIKE

[Spider.007]

Met dank aan jullie hulp presenteer ik:

code:

SELECT DISTINCT WoordNr,(CASE
   WHEN Woord = "GoT" THEN 1
   WHEN Woord LIKE "GoT%" THEN 2
   WHEN When LIKE "%GoT%" THEN 3
END) AS RANK
FROM Woorden
WHERE Woord LIKE "%GoT%"
ORDER BY RANK, Woord
LIMIT 250;

documentatie van een CASE

Deze query duurt 0.850119 sec. op 60.000 woorden; op een VIA C3 533

Hardstikke bedankt allemaal voor het meedenken

[ACM]

Evt kan je nog de optie bieden aan je gebruiker om de boel al dan niet "fuzzy" te doorzoeken, kortom om de beide LIKE's weg te laten als ie kiest voor non-fuzzy. Gegarandeerd dat ie dan _nog_ veel sneller wordt