table structure

[drifter]

OK I have this table set up...

franshises ->agency ->office->agent->listing

where every listing has an id that relate to an agent. every agent related to an office.....and so on.

now when I query listings, I join them all (indexes on all the join columns - unique where possible.) Most of my statements in my where clause are from listings - no problem. But sometimes I need to do something like t10.agency_id=4 or t7.franchise_id=3 - as soon as I do this my query bombs. With out this, queries in .001 seconds - with in 4-5 seconds. NOT GOOD.

So i had the idea to take the 6 columns from them 4 tables that I ever use in the query and copy them to the listings table and use that for this query. so every listing has a franshise_id, agency_id, etc.

Now to me this just sounds like bad structure, is there another way?

[artacus]

Sounds like your indexes are wrong. Try to optimize the table to be sure the indexes are current. If that doesn't help look for a field that you're joining on that isn't indexed.

[drifter]

well I have been working on this all day - the query is a bit better - 1.5 seconds - Still terrible though. Using explain, all my joins are on PRIMARY except one that is a UNIQUE. - so they are all listed as eq_ref - I have no file sorts under the extra.

In the 4th table I add when I say where id=34 - It shows in explain as "using where" at that point it is all over. I even tried adding an index to that id column with no difference.

[drifter]

big break through - I added an extra where for the first table. According to explain, that lowered the number of possible matches from 80,000 to about 5,000 - this cut the time down to .3 seconds, but not the first table has a range and using filesort - This I can work on more.

My bigger concern is what happens when my DB grows and I am back at 80,000 possible matches - will I be back this slow again?

[fenway]

I'll need to see the structures, the query  & the explain.