Caching queries

[tavenger5]

I took a look at my slow queries log and I'm seeing things like this:

# Time: 140222  8:50:25
# User@Host: database_user[database_user] @  [10.0.0.4]
# Query_time: 7.076817  Lock_time: 0.000065 Rows_sent: 3  Rows_examined: 4174934
use cellphon_forum;
SET timestamp=1393077025;
SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbenterprisetranslator_cache_medium_es help, vbenterprisetranslator_cache_medium_es cache WHERE help.originaltext='U.S. Supreme$
# User@Host: database_user[database_user] @  [10.0.0.4]
# Query_time: 14.198858  Lock_time: 0.000056 Rows_sent: 18  Rows_examined: 4174934
SET timestamp=1393077025;
SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbenterprisetranslator_cache_medium_es help, vbenterprisetranslator_cache_medium_es cache WHERE help.originaltext='******* Xtre$
# User@Host: database_user[database_user] @  [10.0.0.4]
# Query_time: 13.591001  Lock_time: 0.000274 Rows_sent: 1  Rows_examined: 4174934
SET timestamp=1393077025;
SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbenterprisetranslator_cache_medium_es help, vbenterprisetranslator_cache_medium_es cache WHERE help.originaltext='(Espa&ntilde$

Is there any way to cache queries like this? These queries load on nearly every page load.

Yes, I have the guest cache on.

[tavenger5]

Also, if you're to run EXTRA on these queries, there's this note: "Impossible WHERE noticed after reading const tables"

[vBET]

Please go to Admin CP -> vBET Cache -> Memory Cache you can set there on usage of memory cache (4 engines supported: Memcache, APC, XCache eAccelerator).

Does this fits your needs?

PS.
One question - what is the time measure for query time in your report?

[tavenger5]

Doesn't the memory cache function like the normal cache but stores the data in memory? Would that eliminate some of those queries?

The query time is listed in the first post before the query.

[vBET]

Using Guest Cache will definitively eliminate lot of queries, since for guests results will be stored in plain HTML as files and streamed from files (until file expires - then refreshed).
Guest Cache will eliminate LOT of queries, since most of traffic on forum is from guests (including spiders).

I just checked our sources about Memory Cache. It cooperates with our Guest Cache - so recently used results will be taken from memory not from file. In this case it will not eliminate any queries (Guest Cache already did it).
Still vBulletin itself as I remember (not sure) has support for memory cache and maybe this will eliminate some of queries.

I know where is listed query time - I was asking about time measure. Perhaps I wasn't clear - what is the unit of time? (s, ms, ns?)
We have indexes on our cache tables so time should be short.

Also you can try to disable option Admin CP -> vBET Cache -> Database Cache -> Select grouped translations. When disabled, then queries will be simpler (no taking by serie), but there will be much more queries (something for something) - perhaps on your forum it will be better to query more often.
For example - looking on your results you had 3 queries which gave 22 results. If you disable taking results in groups then you will have 22 queries giving 1 result each, but the query will be easier (simpler 'WHERE' section) so also faster. If you have database on other server then definitively you shouldn't try this. It you are taking results by localhost, then maybe you will see improvement. Cannot say - have to check it.

[tavenger5]

Okay, thanks for explaining. I'm using guest cache and memory cache (xcache), but I'm still astounded at how many SELECT's are coming from the database.

The time measure above is in seconds.

[vBET]

It took your database 14 seconds for query? Really? It is definitively something wrong there. Please try to repair tables by Admin CP, maybe there is something wrong. It shouldn't take so long - those data are indexed.

[tavenger5]

I have a feeling that some tables are locking and/or waiting for the query cache, which is why they are taking so long to execute. Not to mention I could use some more memory on my database server - I'm working on that as well.

[vBET]

vBET is using cache tables without any transactions (MYISAM) so blocking shouldn't be the issue. Perhaps you have broken indexes and MySQL is making full search. Once again please use your Admin CP to repair all your tables and indexes (Admin CP -> Maintenance -> Repair / Optimize Tables).