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?
A Cache Solution to a Mysql Problem?
Moderator: General Moderators
-
thinsoldier
- Forum Contributor
- Posts: 367
- Joined: Fri Jul 20, 2007 11:29 am
- Contact:
A Cache Solution to a Mysql Problem?
Warning: I have no idea what I'm talking about.
- 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?
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)
Re: A Cache Solution to a Mysql Problem?
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?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.
Re: A Cache Solution to a Mysql Problem?
You can always create normalized tables from it. It won't hurt nobody (except youthinsoldier wrote:The LIST table has 126 fields. Yes I know it should be normalized/refactored but that's not an option at this time.
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
- 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?
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?
New data is added almost every hour (9-5) daily. A dozen or so records of old data get updated once a week.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.
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?
This is on an old server with mysql4mikosiko 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?
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?
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.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!
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.