Hello eGw developers,
We are facing a case in the phpbrain / knowledgebase module… With
about 1500 articles, the module is working well except the advanced
search with words ($all_words, $one_word, $without_words), queries with
words are very slow (150 seconds…)
The concerned function is in class.sokb.inc.php , function
adv_search_articles. In the case of a search “$all_words” on “word1
word2”, the generated query is as follows…
SELECT DISTINCT egw_kb_articles.art_id , title , topic , views , cat_id
, published , user_id , created , modified , votes_1 , votes_2 , votes_3
, votes_4 , votes_5
FROM egw_kb_articles
LEFT JOIN egw_kb_search ON egw_kb_articles.art_id=egw_kb_search.art_id
WHERE user_id IN (7, -5, 7, 12, -6, 82, 0)
AND (keyword LIKE ‘%word1%’ OR title LIKE ‘%word1%’ OR topic LIKE
’%word1%’ OR text LIKE ‘%word1%’)
AND (keyword LIKE ‘%word2%’ OR title LIKE ‘%word2%’ OR topic LIKE
’%word2%’ OR text LIKE ‘%word2%’)
A quickier query is as built up with unions (the UPPER is probably not
necessary…)…
select * from (
select * from (SELECT DISTINCT egw_kb_articles.art_id , title , topic ,
views , cat_id , published , user_id , created , modified , votes_1 ,
votes_2 , votes_3 , votes_4 , votes_5 FROM egw_kb_articles WHERE user_id
IN (7, -5, 7, 12, -6, 82, 0) AND (UPPER(text) LIKE ‘%WORD1%’)
UNION
SELECT DISTINCT egw_kb_articles.art_id , title , topic , views , cat_id
, published , user_id , created , modified , votes_1 , votes_2 , votes_3
, votes_4 , votes_5
FROM egw_kb_articles
LEFT JOIN egw_kb_search ON egw_kb_articles.art_id=egw_kb_search.art_id
WHERE user_id IN (7, -5, 7, 12, -6, 82, 0)
AND UPPER(keyword) LIKE '%WORD1%'
UNION
SELECT DISTINCT egw_kb_articles.art_id , title , topic , views , cat_id
, published , user_id , created , modified , votes_1 , votes_2 , votes_3
, votes_4 , votes_5
FROM egw_kb_articles
WHERE user_id IN (7, -5, 7, 12, -6, 82, 0)
AND (UPPER(title) LIKE ‘%WORD1%’ OR UPPER(topic) LIKE ‘%WORD1%’)
) A
UNION ALL
select * from (SELECT DISTINCT egw_kb_articles.art_id , title , topic ,
views , cat_id , published , user_id , created , modified , votes_1 ,
votes_2 , votes_3 , votes_4 , votes_5 FROM egw_kb_articles WHERE user_id
IN (7, -5, 7, 12, -6, 82, 0) AND (UPPER(text) LIKE ‘%WORD2%’)
UNION
SELECT DISTINCT egw_kb_articles.art_id , title , topic , views , cat_id
, published , user_id , created , modified , votes_1 , votes_2 , votes_3
, votes_4 , votes_5
FROM egw_kb_articles
LEFT JOIN egw_kb_search ON egw_kb_articles.art_id=egw_kb_search.art_id
WHERE user_id IN (7, -5, 7, 12, -6, 82, 0)
AND UPPER(keyword) LIKE '%WORD2%'
UNION
SELECT DISTINCT egw_kb_articles.art_id , title , topic , views , cat_id
, published , user_id , created , modified , votes_1 , votes_2 , votes_3
, votes_4 , votes_5
FROM egw_kb_articles
WHERE user_id IN (7, -5, 7, 12, -6, 82, 0)
AND (UPPER(title) LIKE ‘%WORD2%’ OR UPPER(topic) LIKE ‘%WORD2%’)
) B
) C group by art_id HAVING count(art_id) > 1
Of course, it’s only a part of the solution since the advanced search
also treats cases with more words, cases without_words, etc. The
function is probably to be rewriten/optimized…
Do you know this issue ? Do you think this might this be treated in a
future release and/or might we help on this ?
Thank you,
–
Thomas
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers
