Profile My Little Forum - some suggestions (General)

by danielb987, Tuesday, March 21, 2017, 20:30 (36 days ago) @ Milo

The test I currently do is that I go to an entry, then clear the profile data, then go to the main page and then show the result here. The reason for me to do this test is that there are many people reading the forum but only a few posting to the forum. At 20:30 local time in Sweden, my forum has 60 visitors but there have only been 36 posts in the last 24 hours. So it is more important that the forum is fast to read than it is fast to post.

mlf2_entries.user_id is not indexed:

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

(as already suggested)

This I have already done in my database.

mlf2_entries.spam is not indexed:

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

(as already suggested)

This I have already done in my database.

------

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!

----

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.

----------

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)

Best regards,
Daniel


Complete thread:

 RSS Feed of thread

powered by my little forum