March 26th, 2013, 10:36 PM
Mysql search engine tables multi words search
I inted to optimize the php search on the sphyder search engine. The original code
have several queries to accomplish the following problem. I would like to put it in one sql statement which I think
1 or more words are provided for the query. Select the kw_ids from kws (dictionarry).
lnks [l_id,url,site_id] TABLE having link id (l_id), url-name and site-id (more links can have same site_id)
kwds0..32[kw_id, l_id,weight] 32 TABLES with words ids kw_id (many)<->l_id(unique) (link_ids) and weight of the word for that page link
kws [kw_id, wrd] dictionary, with word as text and kw_id
Get the record set. from the word(s) calcc the table key (obtain table name) [0..32] which is not a problem,
From each kwds# TABLE select all the links l_id(s), with UNIQUE / DISTINCT site_id.
With the result set, match only the links that have all the words (if many are provided),
and order the records by the sum of all weight(s) of the words.
In the query I use,
- I see multiple times links from same site
- I see the records are not ordered by weight
Here it is.
where kwds0 and kwds4 are the tables where the 'xxxx' and 'yyyy' are located (simple hashing)
SELECT DISTINCT lnks.url,lnks.s_id,lnks_l_id,kwds0.wg,kwds4.wg
FROM wss, lnks
INNER JOIN kwds0 ON lnks.l_id=kwds0.l_id
INNER JOIN kwds4 ON lnks.l_id=kwds4.l_id
INNER JOIN kws ON kws.kw_id=kwds0.kw_id OR kws.kw_id=kwds4.kw_id
WHERE (kws.wrd='xxxx' OR kws.wrd='yyyy')
AND (kwds0.wg>0 OR kwds4.wg>0)
ORDER BY kwds0.wg AND kwds4.wg DESC LIMIT 0, 5