[edit]Proloque: I reformatted the SQL-queries for improvoed readability.

Auge
[/edit]

My forum has 192 000 entries. If I simply reloads the main page of the forum, one single query takes 33 ms, while the rest takes less than 1 ms. However, if I go to an entry, then clear the profile data, and then go to the main page of the forum by clicking on the head line, I get a number of VERY slow SQL queries.

includes/index.inc.php, line 187. Time 1 062 milliseconds!

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

includes/main.inc.php, line 336. Time 581 milliseconds!

SELECT
  COUNT(*)
FROM mlf2_entries
  WHERE pid = 0
    AND 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)

includes/index.inc.php, line 69. Time 578 milliseconds!

SELECT
  id,
  tid
FROM mlf2_entries
  WHERE pid = 0
    AND 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 sticky DESC,
    TIME DESC
  LIMIT 0, 30

includes/main.inc.php, line 339. 528 milliseconds!

SELECT
  COUNT(*)
FROM mlf2_entries
  WHERE 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)
    AND spam = 0

includes/main.inc.php, line 344. 517 milliseconds!

SELECT
  COUNT(*)
FROM mlf2_entries
  WHERE spam = 1

Best regards,
Daniel