Re: Slow Query stuck on "Sending Data"
Wow, this is great stuff. Thanks so much!
FYI: `category` represents 1:For Sale, 2:For Rent, 3:Sold, and so on. You were right in that the majority of the queries are for `category` = 1, or `category` = For Sale. Probably about 80% of listings have `category` set to 1 (For Sale).
And, to be honest, `visible` is getting close to 50/50 1 or 0. You are right though in thinking that easily 95% of listings are set to 1, as that was originally how our system was setup, however, we have started keeping listings after their expiry date, but have them set `visible` = 0, so that Google doesn't complain that listings/URL's are constantly going missing. We are doing this until we can figure out another way to keep things kosher with Google.
The reason I initially setup the 3 `regions_x` tables like that is because I thought that queries would execute quicker because each row was being referenced by a number instead of a string (`city` = 597 instead of `city` = 'Toronto'). But I'm starting to understand that it's more about making the data accessible than how it is being referenced.
I have an existing website (all the above queries and such are taking over the existing site), and I have everything squished into the `listings` table. I designed it ages ago, and I even have a `pictures` field which has image url's stored, separated by a pipe (image.jpg|image2.jpg|image3.jpg). I know, you're cringing, I can sense it. The table is getting out of hand which is why I'm looking to better organize things this time around. I just assumed that I should put property addresses in its own table, cities, states, countries, too. Obviously, I took photos out and put them in their own table.
I am going to implement your further suggestions, and see where that takes us!
- Marc (sorry, I ramble on sometimes...)