Multiple primary keys in wp_post_views table

  • K9Heaven

    (@k9heaven)


    7 years, 5 months ago

    I’ve been looking at the way the post counts are stored so I can extract an informative report and I’ve found a couple of issues.

    Firstly, id, type & period are all set as primary keys; consequently whenever I browse that table, the sort by drop down is flashing and the whole database freezes up.

    Secondly, the column ‘id’ should really be named something else because it’s the same as the column name in wp_posts meaning that any query involving both tables won’t run because the column ‘id’ is seen as ambiguous.

    Can you address this please?

Viewing 2 replies - 1 through 2 (of 2 total)
  • superlapin

    (@superlapin)

    7 years, 5 months ago

    From what I can see, there are 2 unique keys, one composite key on the first 3 columns and one on all 4 columns.

    What is the purpose of this 2nd unique key?
    I am trying to use mysql replication and my logs are full of this message: The important part is “INSERT… ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe”

    [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. 
INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO wp_post_views (id, type, period, count)
				VALUES (264430, 1, '201851', 1)
				ON DUPLICATE KEY UPDATE count = count + 1

    Can we safely delete this 2nd index? Or make it non unique ?
    I can’t see how it is being used.

    • This reply was modified 7 years, 5 months ago by superlapin.
    • This reply was modified 7 years, 5 months ago by superlapin.
    • This reply was modified 7 years, 5 months ago by superlapin.
    Thread Starter K9Heaven

    (@k9heaven)

    7 years, 5 months ago

    That’s odd; in my database, id, type & period are all set as primary and index keys, count is just an index key. You can’t have more than one primary key in a table. Moreover a field that is set as a primary key cannot have duplicate values. This table doesn’t have any unique values. It’s poorly designed. IMHO the column ID should be renamed post_id because those values are the posts ID from wp_posts table and then the table should have its own unique ID for each entry and this should be the primary key. This then allows database queries to run correctly and without errors.

    Some input from the developer wouldn’t go amiss but they’re clearly not taking this seriously which is a pity.

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘Multiple primary keys in wp_post_views table’ is closed to new replies.