Waiting a long time for search results? (General)
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
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.
Trenne niemals Müll, denn er hat nur eine Silbe!