Is it worth storing database results in a session

[NotionCommotion]

Let's say I have the following query, all tables are properly indexed, the resultant data is needed for every initial load and ajax request, and the associated data rarely changes:

SELECT * FROM a JOIN b ON b.id=a.b_id JOIN c ON c.id=b.c_id JOIN d ON d.id=c.d_id JOIN e ON e.id=d.e_id JOIN f ON f.id=e.f_id WHERE a.something=123;

Instead of running the query every request, I decided to store the results in a session.  To know if the session was stale, I added a datetime column to one of the tables, and updated the value using the application whenever one of the six tables changes.  I then would just first execute SELECT updated FROM a  WHERE something=123;, and only use the big query if the updated value was different than the session value.

 

Recently found things not working as expected, and later found that I forgot to update the a.updated value when one of the column values changes in one of the joined tables.

 

Any advice from seasoned professionals whether it is worth trying to take steps such as these?  If so, would you recommend using triggers?  Don't think this would have even helped me on my earlier faux pas as the column which changed was also a new column, but probably better than using the application.  Or maybe I should be doing this completely some different way?

[Barand]

My first advice would be "Don't use SELECT * ".

 

Every one of those ids in the joins will be duplicated so you are getting a lot of data you don't need and therefore slowing your query. Specify just the columns you need

[NotionCommotion]

My first advice would be "Don't use SELECT * ".

 

Every one of those ids in the joins will be duplicated so you are getting a lot of data you don't need and therefore slowing your query. Specify just the columns you needOnly

 

Wouldn't dream of doing so in practice, and only did so to limit the clutter for this post and focus the responses.  Guess it didn't work...

SELECT a.x, a.y, b.x AS b_x, c.c, e.bla, f.bla AS blabla FROM a JOIN b ON b.id=a.b_id JOIN c ON c.id=b.c_id JOIN d ON d.id=c.d_id JOIN e ON e.id=d.e_id JOIN f ON f.id=e.f_id WHERE a.something=123;

Edited June 5, 2015 by NotionCommotion

[QuickOldCar]

I have some bigger queries that I cache the mysql results.

For some items I do only id's, other things is full arrays of all the rows.

 

I've tried it in json format and also php and seems to work fine.

[fastsol]

I put results in a session for my product list.  I also put the query string in a session var.  The query string would change based on different vehicle selections by the client.  So I just check if the query string is different from the one in the session and only run the query if it is different.  In todays web market with so many users on mobile devices, it's really best to minimize load time in any logical method you can, this method is certainly one of those methods.

[mac_gyver]

To know if the session was stale, I added a datetime column to one of the tables, and updated the value using the application whenever one of the six tables changes.

 

 

the query cache, in the database engine, will do this for you.

 

for any particular query statement, if the tables referenced in the query haven't changed (insert, update, delete), the result is gotten from the query cache, assuming the result is already in the query cache. if any table referenced by the query statement has changed, the new data is retrieved and cached.

[QuickOldCar]

 

 

the query cache, in the database engine, will do this for you

I agree, can even devote more memory to it if need to.

 

 

 

for any particular query statement, if the tables referenced in the query haven't changed (insert, update, delete), the result is gotten from the query cache, assuming the result is already in the query cache. if any table referenced by the query statement has changed, the new data is retrieved and cached.

 

Also agree.

When doing many new inserts constant it becomes useless, depends how often the data is changed.

So if doing complex queries such as sorting or searches might want to look into caching it yourself to lessen doing the query.

[NotionCommotion]

the query cache, in the database engine, will do this for you.

 

for any particular query statement, if the tables referenced in the query haven't changed (insert, update, delete), the result is gotten from the query cache, assuming the result is already in the query cache. if any table referenced by the query statement has changed, the new data is retrieved and cached.

 

Wow!  Never knew that the DB would cache queries, but I guess it makes sense.

 

So, do I "check" the query cache to see if it was changed?

 

Or just do my big query every time, and rely on the DB to optimize the results?

[mac_gyver]

you just execute the query. the result you get back will either be from the query cache, if the result is in the cache and it is current, or the database engine will actually run the query against the database table(s) to get the result (and put it in the cache as well.)

 

the advantage of doing this at the database level will mean that common and current things on your site - configuration, menus, the current state of forum threads, ... will be in the cache and all the visitors viewing that common/current information will get results from the cache.

Edited June 6, 2015 by mac_gyver

[NotionCommotion]

Thanks MacGyver,

 

Looks like I went to a lot of trouble for nothing.

 

How can I confirm the results are in fact being cached?

[mac_gyver]

i don't know precisely, but everything about the query cache would be found in the documenation - http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

 

it looks like checking the value of Qcache_hits, before and after a query is ran will tell you if the query got the result from the cache -

 

If a query result is returned from query cache, the server increments the Qcache_hits status variable

 

Edited June 6, 2015 by mac_gyver