A Cache Solution to a Mysql Problem?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

A Cache Solution to a Mysql Problem?

Post by thinsoldier »

I'm on a very slow machine doing a query that will find around 5,000 records from a table named LIST.

The LIST table has 126 fields. Yes I know it should be normalized/refactored but that's not an option at this time.

I'll be building an "advanced search form" for this so almost 100 of those 126 fields are eligible to wind up in the query that actually gets run.

If a user chooses to only see records with photos then I will also be joining this table with another table full of image meta data (not binary files) called Media.

Media has over 20,000 records.

1 List record owns many Media records (dozens) but I'm only interested in the Media record with the highest id number for each List record. But since I can only use LIMIT 1 in the query once I can't limit the join with the media table to 1 media item. So I wind up selecting all media which mean duplicate LIST data for each media record so I then have to use GROUP By List.id to get rid of the duplicates.

After this, when I display my Search Result Items I do 4 more queries each to turn category ids in the LIST data into their textual values pulled from 2 other tables.

Anyway, point is, no matter how I structure my queries mysql always needs to write a temporary table to disk to give me my result for the main query. This is slow. Followed by a couple more queries which might not be fastest way to do things.

Until I can hire a mysql wizard to sort this out I'm wondering if caching could help me out.

I'm thinking of :
-changing my main query to only select and return the LIST.id and 4 category id fields with no joins.
-loop through those and query for the category strings and 1 media record for each LIST record.
-build html table row search result item for each LIST record
-cache each search result item individually

So then when I do a query, as I loop through the results of the main query I can just pull the cached html based on LIST.id instead of doing more individual queries or lumping the categories into the main query as 4 more joins.

Does this sound like a plan worth trying?
Warning: I have no idea what I'm talking about.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: A Cache Solution to a Mysql Problem?

Post by Christopher »

How often does this data change? I would be tempted to have a query run when the data is updated that generated a new optimized table with only the data you need in it.
(#10850)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: A Cache Solution to a Mysql Problem?

Post by mikosiko »

thinsoldier wrote: Media has over 20,000 records.

1 List record owns many Media records (dozens) but I'm only interested in the Media record with the highest id number for each List record. But since I can only use LIMIT 1 in the query once I can't limit the join with the media table to 1 media item. So I wind up selecting all media which mean duplicate LIST data for each media record so I then have to use GROUP By List.id to get rid of the duplicates.
Have you tested creating a VIEW from Media with only the max(id) per List-id and use that view in the join with you master query?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: A Cache Solution to a Mysql Problem?

Post by VladSun »

thinsoldier wrote:The LIST table has 126 fields. Yes I know it should be normalized/refactored but that's not an option at this time.
You can always create normalized tables from it. It won't hurt nobody (except you :P )

PS: It's almost what Christopher have suggested.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: A Cache Solution to a Mysql Problem?

Post by John Cartwright »

The point is, with 100+ fields searchable, it would be impossible to create an index for that many fields. Even if you did, the performance would be horrible as it's probably using more space than the data!
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: A Cache Solution to a Mysql Problem?

Post by thinsoldier »

Christopher wrote:How often does this data change? I would be tempted to have a query run when the data is updated that generated a new optimized table with only the data you need in it.
New data is added almost every hour (9-5) daily. A dozen or so records of old data get updated once a week.
Warning: I have no idea what I'm talking about.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: A Cache Solution to a Mysql Problem?

Post by thinsoldier »

mikosiko wrote:Have you tested creating a VIEW from Media with only the max(id) per List-id and use that view in the join with you master query?
This is on an old server with mysql4
Views (including updatable views) are implemented beginning with MySQL Server 5.0.1.
Warning: I have no idea what I'm talking about.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: A Cache Solution to a Mysql Problem?

Post by thinsoldier »

John Cartwright wrote:The point is, with 100+ fields searchable, it would be impossible to create an index for that many fields. Even if you did, the performance would be horrible as it's probably using more space than the data!
I've reworked it so that with the main advanced search form the max # of fields that can be search on is about 26. Most of the other less useful 70 fields can be searched on in near-isolation so they have their own simpler form. The query from that form is only 5 or 6 fields and only administrators use it so it doesn't matter too much if it winds up being slow.

But the main admin form with 25 fields can still be a bit slow at times and the public search form with about a dozen fields again can still be a bit slow sometimes.
Warning: I have no idea what I'm talking about.
Post Reply