Hello

mlf2_entries.time is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`time`);

By adding this index alone, together with the two indexes above which I have added previously, the query in includes/index.inc.php at line 187 went from 1 000 milliseconds to 22 milliseconds! A very huge improvement!

Wow

mlf2_read_entries.posting_id and mlf2_read_entries.user_id are joined primary keys. They have to indexed separately, see links above.

ALTER TABLE `mlf2_read_entries` ADD INDEX(`user_id`);
ALTER TABLE `mlf2_read_entries` ADD INDEX(`posting_id`);

I cannot see any benefit from these two indexes at this point, but my table mlf2_read_entries is small. I installed a new MLF forum and imported the data from an old MLF forum, so this table was clean after the update.

When the table starts to grow much bigger, the indexes will probably be much more important than now.

That's what I and apparently Milo expect. In a 2.4.x forum with many active registered users, the table will grow and it will grow fast. Because of the SELECTs for the forum entries, which JOINs this table in most cases and on both columns, it is good to have an INDEX on both fields separately.

SELECT …
FROM mlf2_entries AS ft
  LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id=ft.user_id
  LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id
    AND rst.user_id = 4

After these new indexes, the status for me is as follows:

includes/index.inc.php, line 69: about 600 milliseconds

includes/main.inc.php, line 339: about 600 milliseconds

includes/main.inc.php, line 336: about 120 milliseconds

includes/index.inc.php, line 187: about 22 milliseconds
(this was the query that before took over 1 000 milliseconds)

That's much better, even the two queries with more than 600ms execution time are still slow.

Thank you for your collaboration.

Tschö, Auge