Query Locks Major Conflict

@Mark

I have had this isue being flagged up by our hosts which cause catastrophic spiking of RAM and server crashing – verbatim:

The following query:

SELECT meta_key FROM wp_postmeta WHERE meta_key !=’_wp_page_template’ AND meta_key !=’_edit_last’ AND meta_key !=’_edit_lock’ AND meta_key !=’_encloseme’ AND meta_key !=’_pingme’ GROUP BY meta_key;

is found in several themes in the file */framework/class/class_core.php and is running slow for several sites.

The reason why this query is slow, the more data the database tables have, is because the query is doing a “NOT” comparison on every value.
This means it has to process the entire table and cannot use an index.
The slowness is probably not as much of an issue when the number of rows examined is 10,000 or 50,000, but when it is 1,000,000 rows or even more, this type of query does not scale well.

From our slow queries log, some examples of query times and locked table
times:

# Query_time: 5.585152 Lock_time: 0.000072 Rows_sent: 263
Rows_examined: 1530792
SELECT meta_key FROM wp_postmeta WHERE meta_key !=’_wp_page_template’ AND meta_key !=’_edit_last’ AND meta_key !=’_edit_lock’ AND meta_key !=’_encloseme’ AND meta_key !=’_pingme’ GROUP BY meta_key;

# Query_time: 5.536516 Lock_time: 0.000078 Rows_sent: 263
Rows_examined: 1530792
SELECT meta_key FROM wp_postmeta WHERE meta_key !=’_wp_page_template’ AND meta_key !=’_edit_last’ AND meta_key !=’_edit_lock’ AND meta_key !=’_encloseme’ AND meta_key !=’_pingme’ GROUP BY meta_key;

# Query_time: 10.905617 Lock_time: 0.000119 Rows_sent: 263
Rows_examined: 1530792
SELECT meta_key FROM wp_postmeta WHERE meta_key !=’_wp_page_template’ AND meta_key !=’_edit_last’ AND meta_key !=’_edit_lock’ AND meta_key !=’_encloseme’ AND meta_key !=’_pingme’ GROUP BY meta_key;

# Query_time: 10.565695 Lock_time: 0.000070 Rows_sent: 263
Rows_examined: 1530792
SELECT meta_key FROM wp_postmeta WHERE meta_key !=’_wp_page_template’ AND meta_key !=’_edit_last’ AND meta_key !=’_edit_lock’ AND meta_key !=’_encloseme’ AND meta_key !=’_pingme’ GROUP BY meta_key;

This is the most particularly troublesome query for sites with large
database tables. I don’t presume that this is a “magic bullet” that, if
fixed, would be the end of all issues, but it is the most urgent item to
fix right now. There may be other queries that need examination later if
this one gets fixed and exposes other issues.

Would appreciate urgent attendance to this or comment of how to cure it?

It is fearsomely consuming RAM and causing our servers to crash. Cant imagine I am the only one with this issue.

I know you have been working on optimization furiously – so maybe the latest framework as a resolution? I have issued a help request as well.

thanks in advance