Today we used the consult for recive a pattern of data, and, with this pattern select the results:

SELECT 
pm.meta_value, MONTH(RTRIM(LTRIM(SUBSTRING(pm.meta_key,23,10)))) as month
FROM wp_postmeta pm

The problem is: The consulting are stay slowed, and, we cant change the database. How i can make this consult stay more speed? I'm yet trying put the index, but, not solved.

The pattern is filter the string in wp_postmeta and retry get the month.

For example:

• string-with-any-name-of-day-2022-01-32, so, we filtering this string with rtrim and substring for get the month.

I'm tryed put the index in table wp_postdata in column meta_key and meta_value, but this not solved my problem.

I yet think in create other table for storage this meta data, but, the problem is gonna continue.

CodePudding user response：

Your query is very close indeed to being optimal. You're selecting the whole table (you have no WHERE clause) which means a full table scan. That takes time, but it's unavoidable. You're not ORDERing your result set, so MySql delivers it progressively without having to buffer it up. The only possible room for improvement is the little nest of string functions, but that cost is trivial compared to the full table scan.

The real problem is that your query makes no sense on wp_postmeta. It can't possibly yield anything useful. Why not? The meta_key values in that table are all sorts of things, most of which will be made meaningless by your nest of string functions. Don't believe me? Run this query.

SELECT DISTINCT meta_value FROM wp_postmeta

You'll see the various meta_keys in use by Core and plugins. WooCommerce has dozens.

That means you need a WHERE meta_key LIKE 'something%' clause in your query to exclude all the rando keys from your result set. As long as the LIKE clause doesn't start with % then your query will exploit one of the existing standard indexes on that table.

CodePudding user response：

There are several potential ways to improve the performance of the query you provided. Here are a few suggestions that you might consider:

1. Use a more efficient string manipulation method: Instead of using the RTRIM, LTRIM, and SUBSTRING functions to extract the month from the meta_key column, you could use a regular expression or string search function to more efficiently match the month value in the meta_key column. For example, you could use the REGEXP_SUBSTR function to extract the month value from the meta_key column, like this:

SELECT 
    pm.meta_value,
    REGEXP_SUBSTR(pm.meta_key, '\d\d\d\d-\d\d-(\d\d)') as month
FROM wp_postmeta pm;

2. Use an index on the meta_key column: To improve the performance of the query by using an index on the meta_key column, you could create a non-unique index on the meta_key column using the CREATE INDEX statement. For example:

CREATE INDEX idx_meta_key ON wp_postmeta (meta_key);

Here's a caveat. Avoid creating unique indexes on columns like meta_key in WordPress databases, unless you are certain that the values in the column will always be unique.

You could also create a unique index on the meta_key column, which would require that each value in the meta_key column be unique and could potentially improve the performance of the query even further. For example:

CREATE UNIQUE INDEX idx_meta_key ON wp_postmeta (meta_key);

3. Use a materialized view or temporary table: To improve the performance of the query by using a materialized view or temporary table, you could create a materialized view or temporary table that pre-calculates the month values for each row in the wp_postmeta table, and then use this materialized view or temporary table in the query instead of the wp_postmeta table itself.

For example, you could create a materialized view that calculates the month values for each row in the wp_postmeta table and stores them in a new column, like this:

CREATE MATERIALIZED VIEW wp_postmeta_month AS
SELECT
    pm.*,
    REGEXP_SUBSTR(pm.