database - Improving wpdb queries with large data

admin管理员组

文章数量:1122826

My plugin will handle large amounts of data that could could result in the queries reading over 5000 items in a database query. The admin will provide a set of CSV files that get stored/organized in the database. The user will have some common results when they go to specific pages. I can predict what that query is in most cases. I wanted to store the array of results in cache so that query is faster.

I looked into wp_cache_get, wp_cache_set, wp_cahce_delete, etc but it seems this may not be the best option since it's not persistent. The codex says I would need an extra plugin to handle persistent caching but I'd rather not require ANOTHER plugin. I could store the array serialized in the database but not sure if that's the best route for efficiency or even an acceptable workaround to the cache issue.

My plugin will handle large amounts of data that could could result in the queries reading over 5000 items in a database query. The admin will provide a set of CSV files that get stored/organized in the database. The user will have some common results when they go to specific pages. I can predict what that query is in most cases. I wanted to store the array of results in cache so that query is faster.

I looked into wp_cache_get, wp_cache_set, wp_cahce_delete, etc but it seems this may not be the best option since it's not persistent. The codex says I would need an extra plugin to handle persistent caching but I'd rather not require ANOTHER plugin. I could store the array serialized in the database but not sure if that's the best route for efficiency or even an acceptable workaround to the cache issue.

• database
• cache

Share Improve this question asked Feb 12, 2016 at 21:36 dcp3450dcp3450 46533 gold badges1212 silver badges2424 bronze badges 3

• 2 You could use custom tables with indexes optimized for your queries. Have you tried that? – fuxia ♦ Commented Feb 13, 2016 at 5:34
• Storing the post-ids in transient variables could help, but as you've mentioned it might not be the most efficient. 5000 results aren't that many though & using transients may be a more economical alternative to custom tables. Reddis might do the trick too. You could also maybe bypass the database and just work from the csv data. – admcfajn Commented Dec 3, 2021 at 5:26
• with an object cache installed those functions are persistent! Even without one though they give a performance boost by avoiding duplicate queries during the same request – Tom J Nowell ♦ Commented Jan 4, 2024 at 14:46

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Caching is Your Friend

I understand your frustration with adding another plugin, but a method for extending WordPress cache is highly recommended for any WordPress sites getting any kind of traffic. These plugins allow WordPress to store cache somewhere better suited for storing and quickly accessing this data, such as Memcache, Redis, or even in the local file structure.

1. W3 Total Cache offers the widest variety of destinations for cache.
2. WP Super Cache offers very quick access to file cache.
3. Batcache offers direct access to Memcache.
4. Redis Cache offers direct access to Redis cache.

At the end of the day, it all depends on what's available on your server.

In my opinion, wp_cache is the way to go for what you need. It will allow you to store these values for quick access later using a much faster technology than storing them in the database.

My plugin will handle large amounts of data that could could result in the queries reading over 5000 items in a database query.

If your rows are small and your tables are indexed correctly this may not be an issue.

Until you've actually measured this and found a problem though I think this is premature.

I looked into wp_cache_get, wp_cache_set, wp_cache_delete, etc but it seems this may not be the best option since it's not persistent.

With an object cache dropin and the right software it can and will be persistent. This is usually the case on proper enterprise hosting or if you run your own server, occasionally on managed hosts via Redis/Memcached/APCu.

Caching plugins can also do this though you should measure performance as sometimes file based caches are slower not faster.

But even if it is not persistent you should still consider them as a means of avoiding duplicate queries. Calling get_post_meta 10x in a row with the same arguments won't create 10 queries because it's using WP_Cache behind the scenes via those functions.

And if in doubt, there are always transients that store temporary/expiring data in options.

本文标签： databaseImproving wpdb queries with large data