Queries optimizing

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Queries optimizing

Post by shiznatix »

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.
Dinosoles
Forum Newbie
Posts: 8
Joined: Fri Feb 20, 2009 1:07 am

Re: Queries optimizing

Post by Dinosoles »

Hi,

Are you using MySql? If so are your tables InnoDB and do you have the field that identifies the user indexed?

This can make a huge difference in performance.

Hope this helps.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Re: Queries optimizing

Post by shiznatix »

Yes all those things are done. I have started splitting it up into multiple tables since it is 1 room that is really huge and the rest are quite small so for users who don't need that really big room, this will for sure save on loading times. I am having problems with the query though. Here is what I have right now:

Code: Select all

SELECT
    MAX(table.rt_aaa.date) AS aaaDate,
    SUM(table.rt_aaa.net) AS aaaNet,
    
    MAX(table.rt_bbb.date) AS bbbDate,
    SUM(table.rt_bbb.net) AS bbbNet
FROM
    table.rt_aaa,
    table.rt_bbb
WHERE
    table.rt_aaa.username = "574968"
AND
    table.rt_aaa.date LIKE "2009-01%"
AND
    table.rt_bbb.username = "tester"
AND
    table.rt_bbb.date LIKE "2009-01%"
This returns null for all the selected rows but when I do this:

Code: Select all

SELECT
    MAX(table.rt_aaa.date) AS aaaDate,
    SUM(table.rt_aaa.net) AS aaaNet
FROM
    table.rt_aaa
WHERE
    table.rt_aaa.username = "574968"
AND
    table.rt_aaa.date LIKE "2009-01%"
I get the real numbers, no problemo.

Basically I want to select from all the tables needed for a specific month, this could be up to 18 tables at once to grab the SUM(net) and maybe a few other fields. Is it possible to combine these queries like that so I can do just 1 query instead of 18 queries and if so, how do I do it?
Post Reply