Hi,

I only wants to advice against the creation of indexes for every single column, that is in use for JOINs.

I think, we should do it. Indexing all columns that are used for constrains or that are used to combine two (or more) tables because we join the tables (or restrict the result) by these columns, so, they are more important than other ones. Lets take a look to the slow SQL statement:

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 = 0
  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

The following columns are important to join or restrict the output:

• mlf2_userdata.user_id
  
• mlf2_entries.id
  
• mlf2_entries.category
  
• mlf2_entries.user_id
  
• mlf2_entries.spam
  
• mlf2_entries.time
  
• mlf2_read_entries.posting_id
  
• mlf2_read_entries.user_id

Following this line of reasoning, we can optimise this query by:

mlf2_userdata.user_id is primary key, thus, nothing to do
mlf2_entries.id is primary key, thus, nothing to do
mlf2_entries.category is indexed, thus, nothing to do

mlf2_entries.user_id is not indexed:

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

(as already suggested)

mlf2_entries.spam is not indexed:

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

(as already suggested)

mlf2_entries.time is not indexed:

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

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`);

That's my suggestion. I cannot check the benefit, because I don't have a forum that has so many threads/postings.

/Micha