Check your indexes on your columns to make sure you are not doing a table scan somewhere.
After that, you might try re-writing your query a little. I'll give this a shot, just an experiment: SELECT c.Cat, v.MC, v.CatID, v.VideoID, v.MovieTitle, SUM(s.TotalTime) FROM catagories c JOIN videos v on c.catid = v.catid JOIN salesreport s on v.videoid = s.videoid JOIN users u on u.UserID = s.UserID WHERE u.ADVID > 0 GROUP BY v.VideoID ORDER BY c.Cat I don't know whether this will make any difference at all, but it can't hurt to run it. You could also try joining in a different order (start with users since that is your select criteria table). Are you going to return a large result set from this query? Like several hundred thousand rows? Could be a long query no matter what you do. On 3/30/06, Todd Ashworth <[EMAIL PROTECTED]> wrote: > > Yes, I am being naughty and posting a technical question, but I don't > believe I am signed up to the cf-talk list right now. Besides, all the > smart folks hang out here anyway ;) > > I have been asked to get info out of a group of tables. Duh, > right? Well, > the problem is, the main table that links all of the others together > has 7.5million records. Just doing a SELECT * on it times out. > Linking it with > the other tables doesn't work very well either. Here is an example of the > query I am trying to get to work .. Is there a better way to write this > thing? We are using MySQL 4. > > SELECT c.Cat, v.MC, v.CatID, v.VideoID, v.MovieTitle, SUM(s.TotalTime) > FROM catagories c, videos v, users u, salesreport s > WHERE u.ADVID > 0 > AND u.UserID = s.UserID > AND c.catid = v.catid > AND v.videoid = s.videoid > GROUP BY v.VideoID > ORDER BY c.Cat > > -- --------------- Robert Munn www.funkymojo.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:5:202313 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
