Waiting a long time for search results? (General)

by Dee Kitty, Sunday, June 20, 2021, 21:12 (34 days ago)

Can you advise? In the last few days I've taken over admin of a MyLittleForum installation that has around 60K postings in about 20 categories. When I do a search using "All categories" on average it normally takes over ten seconds to return results! Then if I have to click on page two, three, four of the search results it is another ten seconds each page to load (20 results each page)

If I search individual categories the results are almost instant. Is this expected with that number of postings to wait that long when searching "All categories"?

Avatar

Waiting a long time for search results?

by Alfie ⌂ @, Vienna, Austria, Monday, June 21, 2021, 08:16 (33 days ago) @ Dee Kitty

Hi Dee Kitty,

that’s strange. In my installation (v1.8beta) I have 22K posts in 27 categories. Results in less than one second and no difference between searches. I expect that limiting the search to one category to be a bit slower because of the additional condition in the SQL-command.
Can you give us more information? Version of MlF, PHP, server…

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Waiting a long time for search results?

by Dee Kitty, Monday, June 21, 2021, 11:50 (33 days ago) @ Alfie

Can you give us more information? Version of MlF, PHP, server…

Thanks for your response.

MLF version 2.4.19
PHP version says 7.1.30
Server is your unlimited standard shared hosting type deal

I timed one search at around 37 seconds when searching "all categories" before the results showed. Other times it can be about 5-25 seconds

Then when I click on page 2 of the search results it takes the same amount of time again as it did to search, to load page 2.

Avatar

Waiting a long time for search results?

by Auge ⌂ @, Monday, June 21, 2021, 14:52 (33 days ago) @ Alfie

Hello Alfie

that’s strange. In my installation (v1.8beta) I have 22K posts in 27 categories. Results in less than one second and no difference between searches.

There are a few not to neglect differences.

This is the search query of MLF 1.7.11 …

 
// mlf 1
SELECT
 id,
 pid,
 tid,
 UNIX_TIMESTAMP(TIME + INTERVAL 1 HOUR) AS Uhrzeit,
 subject,
 name,
 email,
 hp,
 place,
 text,
 category
FROM forum_table
WHERE concat(subject, name, place, text, email, hp) LIKE '%term-1%'
 OR concat(subject, name, place, text, email, hp) LIKE '%term-2%'
 OR concat(subject, name, place, text, email, hp) LIKE '%term-3%'
ORDER BY tid DESC, TIME ASC
LIMIT 0, 20;
 

… and these are the count-search-results query and the search query of MLF 2.4.19.

 
// mlf2
SELECT
 COUNT(DISTINCT forum_table.id)
FROM forum_table
 LEFT JOIN entry_tags_table ON entry_tags_table.bid = forum_table.id
 LEFT JOIN tags_table ON entry_tags_table.tid = tags_table.id
WHERE concat(subject, name, text) LIKE '%term-1%'
 OR concat(subject, name, text) LIKE '%term-2%'
 OR concat(subject, name, text) LIKE '%term-3%'
 
SELECT DISTINCT
 ft.id,
 ft.pid,
 ft.tid,
 ft.user_id,
 UNIX_TIMESTAMP(ft.time) AS TIME,
 UNIX_TIMESTAMP(ft.time + INTERVAL 60 MINUTE) AS TIMESTAMP, 
 UNIX_TIMESTAMP(last_reply) AS last_reply,
 name,
 user_name,
 subject,
 IF(text='', TRUE, FALSE) AS no_text,
 category,
 marked,
 sticky,
 rst.user_id AS req_user
FROM forum_table AS ft
 LEFT JOIN userdata_table ON userdata_table.user_id = ft.user_id
 LEFT JOIN read_status_table AS rst ON rst.posting_id = ft.id AND rst.user_id = 23
 LEFT JOIN entry_tags_table ON entry_tags_table.bid = ft.id
 LEFT JOIN tags_table ON entry_tags_table.tid = tags_table.id
WHERE concat(subject, name, text) LIKE '%term-1%'
 OR concat(subject, name, text) LIKE '%term-2%'
 OR concat(subject, name, text) LIKE '%term-3%'
ORDER BY tid DESC, TIME ASC
LIMIT 0, 20
 

There are mainly two differences. Meanwhile MLF 1 searches only in the forum table, MLF 2 searches in the forum table, and joins addtitional tables to take tags into the account and to mark the search results as read if the searching user is registered and logged in (I took user_id 23 as example). Additionally there is a second, simplified query (here above the search query) to determine the number of search results.

The WHERE-conditions look very similar but the FROM part is much more complex in case of MLF 2. Nevertheless I would expect the queries to be much more faster because all mentioned columns in the JOINs are indexed.

*btw* Comparing the two search queries I ask myself why the heck MLF 1 ever took (and takes) the columns place, email, hp into account. I absolutely see no reason to do this. :-|

I expect that limiting the search to one category to be a bit slower because of the additional condition in the SQL-command.

Dependig, what the My SQL optimiser does, it is possible, that the query is faster because the additional condition can, if incorporated as first, narrow down the amount of data to be taken into account a lot. But to be fair, I don't know what the optimiser does without taking a deep look into how MySQL works.

Long story short:

I have no clue why the search is so slow (according to Dee Kitty's second posting with up to nearly 40 seconds). That the search looks into a conglomerate of subject, text and the posters name with LIKE '%search-term%' makes the use of a table or column index impossible. But that is known and should have led to earlier reports. But I can't find one with the forum search.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

Waiting a long time for search results?

by Alfie ⌂ @, Vienna, Austria, Monday, June 21, 2021, 15:22 (33 days ago) @ Auge

Hi Auge,

THX for the explanations!

*btw* Comparing the two search queries I ask myself why the heck MLF 1 ever took (and takes) the columns place, email, hp into account. I absolutely see no reason to do this. :-|

Doesn’t make sense indeed. Removed in my installation. ;-)

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Avatar

Du hast Post

by Auge ⌂ @, Tuesday, June 22, 2021, 12:25 (32 days ago) @ Alfie

Hallo Alfie

Ich habe dich soeben wegen eines Problems in MLF1 per E-Mail über deine Büroadresse kontaktiert.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

Du hast Post

by Alfie ⌂ @, Vienna, Austria, Tuesday, June 22, 2021, 13:14 (32 days ago) @ Auge

Dankeschön!

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Waiting a long time for search results?

by Dee Kitty, Wednesday, June 23, 2021, 02:13 (31 days ago) @ Auge

Maybe I try updating to a newer version? Can I update directly from 2.4.19 to the latest 2.4.24?

Avatar

Waiting a long time for search results?

by Auge ⌂ @, Wednesday, June 23, 2021, 07:49 (31 days ago) @ Dee Kitty

Hello Dee Kitty

Maybe I try updating to a newer version? Can I update directly from 2.4.19 to the latest 2.4.24?

Meanwhile it's a good idea to update to the latest stable version because of several fixes for bugs and security issues as well as a few featurettes, I doubt, that it will fix this particular issue. There was simply no change in the respective code sections.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

RSS Feed of thread

powered by my little forum