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