Cache specific SQL queries

  • Resolved sx1001

    (@sx1001)


    3 years, 2 months ago

    Hi Marko,

    great plugin! Really what I wanted.
    I have a specific question about MySQL caching:

    I have a page which ahs a lot of components and plugins activated.
    I see in Query Monitor, that on nearly every page load, some specific repetitive SQL queries are ran by those plugins, which ALWAYS return the same results (I can assure that because they simply read configurations done by the admin and we never change those parts).

    My idea is:
    Log the sql queries which are ran, identify them by md5’ing the SQL string, count how often than ran, and than cache specific queries, i.e. the results of $wpdb->query()/get_results().

    Can the plugin already do so?
    I mean, I have already written my own code which logs all wpdb-queries (SAVEQUERIES) with md5 and run-counter.

    I just do not know where to hook into to make sure that calls to wpdb are avoided because results are taken from a file-cache…

    Seems this can be done only using wpdb dropin, but W3TC is using this already. So is it possible to achieve that functionality using W3TC?

    Thanks

Viewing 7 replies - 1 through 7 (of 7 total)
  • Plugin Contributor Marko Vasiljevic

    (@vmarko)

    Hello @sx1001

    Thank you for your question and I am happy to assist you with this.
    Database Cache, when enabled, caches all queries. Some specific queries may be excluded from caching by default.
    In W3 Total Cache, you can exclude specific pages, query stems, query words or reject specific constants from being cached.
    However, it’s not possible to cache only specific queries.
    Let me know if I understood your question correctly and if this answers your questions.
    Thanks!

    Thread Starter sx1001

    (@sx1001)

    Hi Marko, thanks for your answer.
    How does the plug-in decide which objects it uses for in memory object caching, and how, which sql queries are cached?

    Can you please let you know which function is responsible here? I’m also a plug-in developed, maybe I’ll find a hook or can patch those functions myself locally – as I’m seeing nearly 100 queries which query static stuff from the WP options table which I do wanna exclusively include into caching.

    unfortunately I can’t turn on object caching like it is implemented right now, because strangely, some wpml translations are broken for me afterwards. That’s why somehow I need to control it.

    And as your plug-in already is such a great starting point, I thought about just changing the inclusion logic maybe directly in the code.

    Plugin Contributor Marko Vasiljevic

    (@vmarko)

    Hello @sx1001

    Thank you for your feedback.
    Just to clarify, do you want to exclude specific queries from the DB caching or your idea is to manually add only specific queries to the cache without adding anything else to the cache?

    THanks!

    Thread Starter sx1001

    (@sx1001)

    Hi Marko,

    I think the easiest way would be to have an include instead of an exclude list.

    This way, I could decide on my own which specific queries shall be cached, I could find those queries which run over and over again using Query Monitor in the frontend.

    Can you let me know in which function the decision is done whether a specific query is cached or not? Maybe I can modify that function to take the approach of an include instead of an exclude-list. I guess you simply have a loop checking against a list of substrings via strpos or fnmatch – I guess I could simply invert the logic here.

    Thanks so much!

    Plugin Contributor Marko Vasiljevic

    (@vmarko)

    Hello @sx1001

    Thank you for the information.
    I understand that in this use case this would be more beneficial to you. However, in most cases, when DB is cached, excluding a specific query or a page is preferred.
    You can check the /plugins/w3-total-cache/DbCache_WpdbInjection.php
    And see if anything may be helpful to create a hook for your case.
    Thanks!

    Thread Starter sx1001

    (@sx1001)

    For me it looks like I need to modify DbCache_WpdbInjection_QueryCaching.php:

    _check_sql() and _can_cache()

    Correct?

    Plugin Contributor Marko Vasiljevic

    (@vmarko)

    Hello @sx1001

    Thank you for the reply.
    Yes, it could be what you need.
    I am not sure since I have not been able to test this.
    Thanks!

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Cache specific SQL queries’ is closed to new replies.