News:

SMF 2.0.19 has been released! Please update. Read more.

Main Menu

custom index ... which one to choose?

Started by grandseb59, August 18, 2020, 03:58:39 PM

Previous topic - Next topic

grandseb59

hello
in the smf admin panel options, you can make a custom index.
you can choose between a small, medium or large index.
on the net they say it doesn't work or not well with mysql, and SMF is based on mysql.
So why offer it?
And if I didn't understand what was said on the net, does it really bring anything, and if so, which one to choose?

Arantor

Custom was long recommended over fulltext (and is *designed* for MySQL) to be faster than fulltext for searching. No-one has benchmarked it since to verify if that's still true.

Large takes more space but if you have a really large forum it will give you better results than medium - unless you're at 100k posts or more, medium is fine.
No good deed goes unpunished / All helpful urges should be circumvented

I have something to say: it's better to burn out than to fade away. There can be only one.

grandseb59


grandseb59

I created an index, average because less than 100 000 topics (77 000 topics). And when I checked "custom index" in "search method" and do a search...  it returns me, that I have a database error.

QuoteYou have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 1200' at line 21
Fichier: /home/*********/public_html/****/Sources/Search.php
Ligne: 1120

Note: Your database may need to be updated. Currently the version of the forum files is SMF 2.0.17, while your database is in SMF 2.0.3. The error reported above could perhaps be fixed by running the latest version of upgrade.php.

But I couldn't find a database update on the SMF site.

what can I do? please

Kindred

there is no database update.  that is a generic message which matches the current version against the database version and is incorrect, since there were no database changes.

the actual problem is MariaDB, I think....    although MariaDB most often works, technically, I don't believe that we officially support it... and sometimes MariaDB has changed just enough from MySQL to need some custom coding.
Слова
Украина

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

grandseb59

so, I have to make a modification in the search.php file?
And if yes, which one?
And if not what should I do?

shawnb61

First thing I would try is to remove the entry for 'Maximum results to show' under Search | Settings.  Set it to 0 to see if that clears the issue.

If you are on 2.0.17, that line # doesn't make sense in Search.php.  If you have any mods that alter search, I would uninstall those & try again.

SMF version?
PHP version?
MariaDB version?
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

#7
Last thing I'd do is get & share the actual DB query.  You can enable logging of the query under Admin | Security & Configuration | General. 

Enable the setting is 'Include database query in the error log'. 

Try the search again, go to your error log, & share the query here. 


We have seen this issue reported a couple times over the last few years.  I don't think we've gotten to the bottom of it.  I actually suspect something is wrong with your Search.php somehow...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

grandseb59

Quote from: shawnb61 on August 25, 2020, 12:57:15 PM
First thing I would try is to remove the entry for 'Maximum results to show' under Search | Settings.  Set it to 0 to see if that clears the issue.

Same error

Quote from: shawnb61 on August 25, 2020, 12:57:15 PMIf you are on 2.0.17, that line # doesn't make sense in Search.php.  If you have any mods that alter search, I would uninstall those & try again.

here are my installed mods.
I don't think one of them has made any modifications in the search part...
or else tell me which one, that I uninstall it to see the difference.
[spoiler]
1.    Separate the sticky topics    1.0.1
2.    Enhancements to purge inactive members    1.3.8
3.    Tidy Child Boards    2.0
4.    Space Between Posts    2.0    
5.    Force Read Unread Pms    1.0    
6.    SimpleColorizer    1.1    
7.    Say Thanks    1.3.6
8.    Quick Spoiler    1.2.5
9.    Dougiefresh's Reorganized Admin Area    1.3
10.    Users Online Today    2.1
11.    Yet Another YouTube BBCode Tag    4.14
12.    Profile Moderator Management    2.0
13.    Invitation System    1.3
14.    Alert    v.1.0
15.    BBCode with style    1.5.1
16.    Hide Content    1.1.4
17.    SMFPacks Shoutbox Pro    1.4.6
18.    6 Custom buttons / tabs with Sub Menus (4)    0.5
19.    SMF Post Prefix    2.1.1
20.    SimplePortal    2.3.7
21.    SP-Shoutbox Options    1.74
[/spoiler]

Quote from: shawnb61 on August 25, 2020, 12:57:15 PMSMF version?

SMF 2.0.17

Quote from: shawnb61 on August 25, 2020, 12:57:15 PMPHP version?

PHP 7.3

Quote from: shawnb61 on August 25, 2020, 12:57:15 PMMariaDB version?
MariaDB 10.1.46

Quote from: shawnb61 on August 25, 2020, 01:08:27 PM
Last thing I'd do is get & share the actual DB query.  You can enable logging of the query under Admin | Security & Configuration | General. 

Enable the setting is 'Include database query in the error log'. 

Try the search again, go to your error log, & share the query here. 

here is the result found in the error log

[spoiler]Error type: database

https://******/index.php?action=search2

Database error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 6000' at line 23


INSERT IGNORE INTO smf_log_search_results
(id_search, id_topic, relevance, id_msg, num_matches)
SELECT
17,
t.id_topic,
1000 * (
15 / (t.num_replies + 1) +
0 * CASE WHEN t.id_first_msg < 539465 THEN 0 ELSE (t.id_first_msg - 539465) / 231200 END +
0 * CASE WHEN t.num_replies < 200 THEN t.num_replies / 200 ELSE 1 END +
55 +
0 * t.is_sticky
) / 100 AS relevance,
t.id_first_msg,
1
FROM smf_topics AS t
INNER JOIN smf_log_search_subjects AS subj1 ON (subj1.id_topic = t.id_topic)
INNER JOIN smf_log_search_subjects AS subj2 ON (subj2.id_topic = subj1.id_topic)
WHERE t.approved = 1
AND subj1.word LIKE '%top%'
AND subj2.word LIKE '%gun%'
AND t.id_board != 49
AND t.id_prefix =
LIMIT 6000

Fichier: /home/******/public_html/*****/Sources/Search.php
Ligne: 1120 [/spoiler]


shawnb61

Quote from: grandseb59 on August 25, 2020, 01:30:54 PM
AND t.id_prefix =
LIMIT 6000

THAT's a problem...   

Could you share your version of Search.php? 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

grandseb59

my  version of Search.php is :  @version 2.0.9

Arantor

Seems like the post prefix mod might have a bug.
No good deed goes unpunished / All helpful urges should be circumvented

I have something to say: it's better to burn out than to fade away. There can be only one.

shawnb61

Quote from: Arantor on August 25, 2020, 01:55:58 PM
Seems like the post prefix mod might have a bug.

Yes, it introduces that line in the search query. 

This needs an entry over in that mod's support thread:
https://www.simplemachines.org/community/index.php?topic=536401.0
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

grandseb -  I suggest you followup over in the support thread for SMF Post Prefix, link above.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

grandseb59

I removed 2 mods:
"Profile Moderator Management"
because I had forgotten to remove it because it was unusable with SMF 2.0.17 but it doesn't seem to interfere with this problem and the "SMF post prefix".
And now the search works again, without errors.

but I'm going to follow carefully the subject on the mod prefix because I'm very interested in it.

On the other hand I tried the same search on my production forum where I didn't create a custom index... and I don't see any difference ... is it really useful to make a custom index?

For info on my forum it is:
Total posts:273850
Total subjects:76696

thank you very much for your help ;)



Arantor

Yes, yes it is; beyond 100k posts you definitely need something. At 250k plus, we tend to suggest Sphinx but it's not easy to set up.
No good deed goes unpunished / All helpful urges should be circumvented

I have something to say: it's better to burn out than to fade away. There can be only one.

grandseb59


Advertisement: