How to put index on post_date

  • Hi,

    I am currently trying to optimize my database, because sql makes my server crash couple times per day.

    Someone on stackoverflow said:

    You have to create an index on the ordering column (post_date), without the index all rows will be fetched, filesorted and then most of them discarded.

    Someone else says to put index on post_type, post_status, post_date.
    Should I do the same for post_modified_gmt and possibly some comment fields?

    Is the Index name relevant? Does this work:
    CREATE INDEX INDEX_DATE on wordpress_posts (post_date)
    CREATE INDEX INDEX_TYPE on wordpress_posts (post_type)
    CREATE INDEX INDEX_STATUS on wordpress_posts (post_status)

    ==> Will it automatically used by SQL to look up the rows? Or do I have to modify anything. I wasn’t able to find the index in PhpMyAdmin ??

    —-
    I have a crapload of these in my slowsql.log:

    # Query_time: 8  Lock_time: 0  Rows_sent: 9  Rows_examined: 1705
SELECT SQL_CALC_FOUND_ROWS  wordpress_posts.* FROM wordpress_posts  WHERE 1=1  AND wordpress_posts.post_type = 'post' AND (wordpress_posts.post_status = 'publish')  ORDER BY wordpress_posts.post_date DESC LIMIT 0, 9;

# Query_time: 8  Lock_time: 0  Rows_sent: 1  Rows_examined: 1706
SELECT post_modified_gmt FROM wordpress_posts WHERE post_status = 'publish' AND post_type = 'post' ORDER BY post_modified_gmt DESC LIMIT 1;

    I also read that SQL_CALC_FOUND_ROWS might not be the most efficient way, but that might be another issue, I just don’t want my server to crash anymore.
    ——–

    Thanks for any input! Why does WordPress not automatically put an index on the fields, wouldn’t that be efficient for smaller sites?

Viewing 1 replies (of 1 total)

  • Ok, so the index name does not matter.

    Here’s a discussion about this issue:

    One guys says:

    Hmm, that was unexpected. If I add an index only on the ordering column and tell MySQL to use it using “USE INDEX (post_date_idx)”, the performance is an order of magnitude better. Thanks!

    There is a multi-column index type_status_date in the default installation.. is that one not sufficient?

    Did anyone here actually optimize the default indexes for their large sites?

Viewing 1 replies (of 1 total)
  • The topic ‘How to put index on post_date’ is closed to new replies.