Queries optimizing
Posted: Tue Mar 03, 2009 3:48 am
Hey-yo
SO I am working on a way to optimize some queries. Basically right now I have about 10 rooms and each gives us data that I store into 1 table (the tracking table). When a user comes to our "show data" page, it takes the rooms he has and then for each room gets the data from the table (the tracking table). This is causing problems for users who have a lot of rooms since the table has ~3million rows. I have optimized the queries and used caching as best as possible but it still just takes a long time. So I am going about optimizing stuff.
One idea I had was to cut down on the table any columns that could possibly be removed. This would help some but just not enough I think.
Another option I was thinking about was to keep the raw data being stored in the current table as-is but create a new database that would have "reporting" data for each room. That way when a user with a lot of rooms comes, his many queries will be a bit faster since each table will be optimized for that room, instead of being a big mashup of all rooms needs (some rooms don't need all the columns that other rooms need). The problem with this though is it still takes separate queries (no?) and that would be a problem to begin with I think. I suppose I could write a fancy query that would grab everything I needed from the different tables all at once but is that optimum?
Before I spend the next 2 days setting up just the basic tests to figure out what is best I was hoping you guys could possibly throw some advice and insight my way. Thanks.
SO I am working on a way to optimize some queries. Basically right now I have about 10 rooms and each gives us data that I store into 1 table (the tracking table). When a user comes to our "show data" page, it takes the rooms he has and then for each room gets the data from the table (the tracking table). This is causing problems for users who have a lot of rooms since the table has ~3million rows. I have optimized the queries and used caching as best as possible but it still just takes a long time. So I am going about optimizing stuff.
One idea I had was to cut down on the table any columns that could possibly be removed. This would help some but just not enough I think.
Another option I was thinking about was to keep the raw data being stored in the current table as-is but create a new database that would have "reporting" data for each room. That way when a user with a lot of rooms comes, his many queries will be a bit faster since each table will be optimized for that room, instead of being a big mashup of all rooms needs (some rooms don't need all the columns that other rooms need). The problem with this though is it still takes separate queries (no?) and that would be a problem to begin with I think. I suppose I could write a fancy query that would grab everything I needed from the different tables all at once but is that optimum?
Before I spend the next 2 days setting up just the basic tests to figure out what is best I was hoping you guys could possibly throw some advice and insight my way. Thanks.