Question 2202 in Wordpress Question posted in
The official Wordpress documentation can be found here.

How increase mysql speed in SELECT in postdata in WORDPRESS with RTRIM

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    i'm solve this problem. I'm need create a new table for stored this register.

    New table:

    • id
    • meta_key
    • meta_data
    • date

    And, i constructed a script for get the data in postmeta and put in for new table.

    The wordpress was slowed because when retrieve posts with metadata, the get_post stay very slowed for read because the number of register.

    The better solution is gonna parse data for a new table and cleaner postmeta table.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search