Profile My Little Forum - some suggestions (General)

by danielb987, Wednesday, March 22, 2017, 04:58 (2564 days ago) @ Auge

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.

I wanted to see what would happen with more data in the table mlf2_read_entries so I wrote a simple php script that reads mlf2_userdata and mlf2_entries and then add valid but random data to the mlf2_read_entries table. Both user_id, entry_id and time is random. Before, the mlf2_read_entries table had 4 800 rows, now it has 190 000 rows.

I have also removed the two suggested indexes so I now only have (user_id, posting_id) as primary key in the table and no other indexes in this table.

The strange thing is that the sql query in includes/index.inc.php at line 187 still only takes about 22 milliseconds. I thought the reason is that the query checks for user_id = 0, and there is no user with user_id = 0 in the table.

----

So in order to check my idea, I logged in to the forum using my ordinary user with user_id 7843 and then tried again. But no difference. I still only have about 22 milliseconds on this query. There are 200 rows in the table with user_id = 7843.

----

The query I am talking about here is the following query:

SELECT
  id,
  pid,
  tid,
  name,
  user_name,
  ft.user_id,
  UNIX_TIMESTAMP(ft.TIME) AS TIME,
  UNIX_TIMESTAMP(ft.TIME + INTERVAL 0 MINUTE) AS TIMESTAMP,
  UNIX_TIMESTAMP(last_reply) AS last_reply,
  subject,
  category,
  rst.user_id AS req_user
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 = 7843
  WHERE spam=0
    AND category IN (0, 4, 5, 6, 7, 8, 9, 25, 14, 23, 19, 22, 24, 10, 21, 11, 12, 13, 15, 16, 26, 18, 20, 27)
  ORDER BY ft.TIME
  DESC LIMIT 40

Best regards,
Daniel


Complete thread:

 RSS Feed of thread