News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Strange behaviour on a search

Started by ArMaP, October 24, 2020, 03:10:22 PM

Previous topic - Next topic

ArMaP

I just had some strange behaviour from a forum I administer happening to me during a search. If I a search phrase with several words it gives the following error:

QuoteThe SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
File: /hermes/bosnaweb23a/b183/glo.zorgon/forum/Sources/Search.php
Line: 1338

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.17, while your database is at version 2.0.13. The above error might possibly go away if you execute the latest version of upgrade.php.

If I reduce the number the number of words on the search it works as expected. Is there something wrong on the forum configuration or is it some kind of bug?

As a second question, where can I find what is the database version?

Thanks in advance. :)

Shambles

A quick use of the "search" function reveals many posts which you may care to check...

Eg... https://www.simplemachines.org/community/index.php?topic=565291

Arantor

Better question: how many words in your search and what kind of index do you have?

Also ignore the database version point; it's irrelevant.

ArMaP

Quote from: Shambles on October 24, 2020, 04:03:40 PM
A quick use of the "search" function reveals many posts which you may care to check...
My problem was not knowing exactly what to search for.

QuoteEg... https://www.simplemachines.org/community/index.php?topic=565291
That thread doesn't help much, as I am left without knowing if I must change something on the configuration of MySQL or not.

ArMaP

Quote from: Arantor on October 24, 2020, 04:21:11 PM
Better question: how many words in your search and what kind of index do you have?
3 words works, 4 words doesn't. No index.

QuoteAlso ignore the database version point; it's irrelevant.
OK.

Arantor

Try making a search index, either a medium custom index, or a full text one.

The alternative is that you change the configuration of MySQL to increase the size of MAX_JOIN_SIZE to 'more'. I don't know how much more, that would depend on a number of things, like how many messages you had, how often the words came up in them.

ArMaP

Quote from: Arantor on October 24, 2020, 04:53:42 PM
Try making a search index, either a medium custom index, or a full text one.
I created an index but it's taking a long time and, at 40% of being created, it's already 3 times as big as the whole posts table.

Arantor

So what kind of index did you try to make?

ArMaP

I made a custom index (I think I chose the middle option for the size), and it has a final size of 4 times the size of the posts table. It didn't solve the problem. :(

Edited to point that it did work, I forgot to hit the "save" button when I checked the "Force the use of a search index" box. :)

Advertisement: