[MySQL] zoekmachine

[Verwijderd]

Ik snap er niks van. Ik heb een zoekmachine gemaakt voor zo'n 12.000 HTML pagina's.
Dit liep allemaal supersnel, totdat er wat foutjes her en der kwamen. Toen kwam ik op het idee om alle tabellen leeg te gooien en alle pagina's opnieuw te spideren.

Nou is alles opnieuw geindexeerd, maar gaat het zoeken opeens supertraag....
Ik snap er niks van. De code is niet verandert en de database structuur ook niet. Er misschien een paar honderd nieuwe pagina's bijgekomen, maar dat moet toch niet een enorm verschil opleveren t.o.v 12.000?

Ik heb alle tabel indexen e.d. opnieuw aangemaakt/verandert en nog krijg ik het niet meer zoals het was?

De tabellen zien er als volgt uit:
CREATE TABLE `keywords` (
`Id` int(10) unsigned NOT NULL auto_increment,
`Word` varchar(255) NOT NULL default '',
PRIMARY KEY (`Id`),
UNIQUE KEY `Word` (`Word`),
KEY `Word_2` (`Word`)
) TYPE=MyISAM AUTO_INCREMENT=118230 ;

CREATE TABLE `pagekeywords` (
`PageId` int(10) unsigned NOT NULL default '0',
`WordId` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`PageId`,`WordId`),
KEY `PageWordIdIndex` (`PageId`,`WordId`)
) TYPE=MyISAM;

CREATE TABLE `pages` (
`Id` int(10) unsigned NOT NULL auto_increment,
`URL` varchar(255) NOT NULL default '',
`Title` varchar(255) NOT NULL default '',
`Datum` int(20) NOT NULL default '0',
PRIMARY KEY (`Id`),
UNIQUE KEY `URL` (`URL`),
KEY `URL_2` (`URL`)
) TYPE=MyISAM AUTO_INCREMENT=12453 ;

tabel keywords heeft 118.229 records
tabel pagekeywords heeft 1.598.817 records
tabel pages heeft 12.447 records

Het enige dat ik me kan bedenken is dat er iets mis is met de indexes. Maar ik ben niet zo'n held met indexes, dus misschien kunnen jullie er even naar kijken...

[ Voor 4% gewijzigd door Verwijderd op 03-02-2005 17:17 ]

[Creepy]

Het zou misschien iets schelen als je zou aangeven wat er fout gaat of welke foutmeldingen je krijgt?

[mimic]

Hij kreeg toch geen foutmelding maar het ging supertraag? of lees ik nu verkeerd ?

[Boss]

Met 'geindexeerd': bedoel je dan ook dat je in de tabel weer indexen hebt aangemaakt op de kolommen waarop gezocht wordt? Dat kan nog wel eens een hoop schelen!

Tenzij je de tabel alleen leeg gemaakt hebt ipv opnieuw gemaakt, dan zouden ze er nog moeten staan....

[Verwijderd]

mimic schreef op donderdag 03 februari 2005 @ 17:20:
Hij kreeg toch geen foutmelding maar het ging supertraag? of lees ik nu verkeerd ?

Nee je leest het goed. Geen foutmelding, maar opeens is de snelheid uit mijn zoekmachine.

Boss schreef op donderdag 03 februari 2005 @ 17:23:
Met 'geindexeerd': bedoel je dan ook dat je in de tabel weer indexen hebt aangemaakt op de kolommen waarop gezocht wordt? Dat kan nog wel eens een hoop schelen!

Tenzij je de tabel alleen leeg gemaakt hebt ipv opnieuw gemaakt, dan zouden ze er nog moeten staan....

Ja, sorry. Met indexeren van de pagina's bedoel het archief opnieuw maken.
Maar het lijkt net of de indexes op de velden niet meer bestaan, zo langzaam gaat het. Toen heb ik de indexes van de velden verwijdert en opnieuw aangemaakt. Maar dat maakt nog geen verschil uit. Mijn indexes staan nu zoals boven aangegeven. Misschien moet er ergens nog een bij ofzo, maar ik zie niet waar?

[ Voor 111% gewijzigd door Verwijderd op 03-02-2005 17:26 ]

[GlowMouse]

Als je zoekt door lappen tekst is een fulltext-index misschien handig. Die zie ik niet staan in de SQL create-table code die je postte.

[Verwijderd]

GlowMouse schreef op donderdag 03 februari 2005 @ 17:36:
Als je zoekt door lappen tekst is een fulltext-index misschien handig. Die zie ik niet staan in de SQL create-table code die je postte.

Ik zal even de werking uitleggen.

In tabel keywords staan alle woorden die in de HTML pagina's voorkomen 1x in. In tabel pages staan alle HTML pagina's 1x in.
De tabel pagekeywords is een combinatie tussen de keywords en pages.

Stel ik zoek op woord "blaat", dan wordt het id van het woord "blaat" uit de tabel keywords gehaald. Vervolgens worden alle pageid's uit de koppeltabel pagekeyword gehaald aan de hand van de id van het zoekwoord. Vervolgens haal ik alle pagina's op aan de hand van alle pageid's.

Ik hoop dat het een beetje helder is zo...

[djluc]

Post je query eens en je tabel-sql inclusief de indices.

[Verwijderd]

Sorry, ben even ziek geweest en had het druk de laatste tijd...
Maar ik heb het probleem nog niet opgelost, dus ik zou het fijn vinden als jullie er nog even naar kijken

Dit is mijn query:

code:

SELECT 
pages.Id AS id FROM keywords, pagekeywords, pages 
WHERE 
keywords.Word LIKE '%$keyword%' 
AND pagekeywords.WordId=keywords.Id 
AND pages.Id=pagekeywords.PageId 
ORDER BY 
pagekeywords.PageId 
DESC LIMIT 100

Database tabellen:

code:

CREATE TABLE `keywords` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `Word` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `Word` (`Word`),
  KEY `Word_2` (`Word`)
) TYPE=MyISAM AUTO_INCREMENT=118231 ;

CREATE TABLE `pagekeywords` (
  `PageId` int(10) unsigned NOT NULL default '0',
  `WordId` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`PageId`,`WordId`),
  KEY `PageWordIdIndex` (`PageId`,`WordId`)
) TYPE=MyISAM;

CREATE TABLE `pages` (
  `Id` int(10) unsigned NOT NULL auto_increment,
  `URL` varchar(255) NOT NULL default '',
  `Title` varchar(255) NOT NULL default '',
  `Datum` int(20) NOT NULL default '0',
  PRIMARY KEY  (`Id`),
  UNIQUE KEY `URL` (`URL`),
  KEY `URL_2` (`URL`)
) TYPE=MyISAM AUTO_INCREMENT=12454 ;

[LuCarD]

Kan je de volgende query voor ons draaien? wel even $keyword vervangen voor een normale zoekterm.

code:

EXPLAIN 
SELECT 
pages.Id AS id FROM keywords, pagekeywords, pages 
WHERE 
keywords.Word LIKE '%$keyword%' 
AND pagekeywords.WordId=keywords.Id 
AND pages.Id=pagekeywords.PageId 
ORDER BY 
pagekeywords.PageId 
DESC LIMIT 100

[Verwijderd]

LuCarD schreef op maandag 21 februari 2005 @ 13:04:
Kan je de volgende query voor ons draaien? wel even $keyword vervangen voor een normale zoekterm.

code:

EXPLAIN 
SELECT 
pages.Id AS id FROM keywords, pagekeywords, pages 
WHERE 
keywords.Word LIKE '%$keyword%' 
AND pagekeywords.WordId=keywords.Id 
AND pages.Id=pagekeywords.PageId 
ORDER BY 
pagekeywords.PageId 
DESC LIMIT 100

code:

Array
(
    [0] => keywords
    [table] => keywords
    [1] => range
    [type] => range
    [2] => PRIMARY,Word,Word_2
    [possible_keys] => PRIMARY,Word,Word_2
    [3] => Word
    [key] => Word
    [4] => 255
    [key_len] => 255
    [5] => 
    [6] => 1
    [rows] => 1
    [7] => where used; Using temporary; Using filesort
    [Extra] => where used; Using temporary; Using filesort
)

Array
(
    [0] => pages
    [table] => pages
    [1] => index
    [type] => index
    [2] => PRIMARY
    [possible_keys] => PRIMARY
    [3] => PRIMARY
    [key] => PRIMARY
    [4] => 4
    [key_len] => 4
    [5] => 
    [6] => 12434
    [rows] => 12434
    [7] => Using index
    [Extra] => Using index
)

Array
(
    [0] => pagekeywords
    [table] => pagekeywords
    [1] => eq_ref
    [type] => eq_ref
    [2] => PageId,PageWordIdIndex
    [possible_keys] => PageId,PageWordIdIndex
    [3] => PageId
    [key] => PageId
    [4] => 8
    [key_len] => 8
    [5] => pages.Id,keywords.Id
    [ref] => pages.Id,keywords.Id
    [6] => 1
    [rows] => 1
    [7] => Using index
    [Extra] => Using index
)

[LuCarD]

Paar kleine dingetjes ...

- Kan je de query opnieuw draaien vanuit de mysql client, dan krijg je een beter overzicht schematje terug. Nu is het bijna niet te lezen.
- Verder gebruikt hij de index van pagekeywords niet goed.
- Misschien kan je de filesort voorkomen als je order op page.pageid wat als resultaat het zelfde moet zijn.

dus de query moet volgens mij worden:

code:

SELECT 
pages.Id AS id FROM keywords, pagekeywords, pages 
WHERE 
keywords.Word LIKE '%$keyword%' 
AND pages.Id=pagekeywords.PageId 
AND pagekeywords.WordId=keywords.Id 
ORDER BY 
pages.Id 
DESC LIMIT 100

btw. meer info over optimizing: http://dev.mysql.com/doc/mysql/en/query-speed.html

[ Voor 8% gewijzigd door LuCarD op 21-02-2005 13:34 ]

[djluc]

Kan je sowieso niet beter "echt" joinen met een on-clausule? Misschien dat dit ook in performance scheelt. Dat weer ik niet zeker. Wil je verder eens meten of het query ook daadwerkelijk de vertragende factor is?

[LuCarD]

djluc schreef op maandag 21 februari 2005 @ 13:38:
Kan je sowieso niet beter "echt" joinen met een on-clausule? Misschien dat dit ook in performance scheelt. Dat weer ik niet zeker. Wil je verder eens meten of het query ook daadwerkelijk de vertragende factor is?

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table are joined to all rows in the second table).

Dus nee. Maar met JOIN on clausule kan je wel makkelijker een index gebruik afdwingen. Maar dat is niet nodig in zijn geval.

[Verwijderd]

LuCarD schreef op maandag 21 februari 2005 @ 13:33:
Paar kleine dingetjes ...

- Kan je de query opnieuw draaien vanuit de mysql client, dan krijg je een beter overzicht schematje terug. Nu is het bijna niet te lezen.
- Verder gebruikt hij de index van pagekeywords niet goed.
- Misschien kan je de filesort voorkomen als je order op page.pageid wat als resultaat het zelfde moet zijn.

dus de query moet volgens mij worden:

[html]
SELECT
pages.Id AS id FROM keywords, pagekeywords, pages
WHERE
keywords.Word LIKE '%$keyword%'
AND pages.Id=pagekeywords.PageId
AND pagekeywords.WordId=keywords.Id
ORDER BY
pages.Id
DESC LIMIT 100[/code]

btw. meer info over optimizing: http://dev.mysql.com/doc/mysql/en/query-speed.html

Je nieuwe query is niet echt sneller. Er lijkt een groot probleem met de wildcards te zijn. Ik weet dat natuurlijk trager is dan zonder, maar zoveel verschil?

Als ik de query zonder wildcards uitvoer, dan duurt de query 0,5 sec.
En met wildcards 69 sec.

Dat is wel erg veel verschil. En die wildcards had ik eerst ook.

Dit is de explain van je nieuwe query:

table	type	possible_keys	key	key_len	ref	rows	Extra
pages	index	PRIMARY	PRIMARY	4	NULL	12434	Using index
pagekeywords	ref	PageId,PageWordIdIndex	PageWordIdIndex	4	pages.Id	129	Using index
keywords	eq_ref	PRIMARY	PRIMARY	4	pagekeywords.WordId	1	where used

edit:
Ik krijg de border van de tabel ff niet aan de praat...

Pff, hoe krijg ik de hele tabel zichtbaar

Dit hoort er nog bij:

Extra

Using index

Using index

where used

[ Voor 10% gewijzigd door Verwijderd op 21-02-2005 14:39 ]