Optimizing performance [SOLVED]

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
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Optimizing performance [SOLVED]

Post by crazycoders »

Hi, i'm doing a stat report for a client and the database is really taking a lot of time to compile the data. Its not complex data at all, i optimized the query from 22 secs to 13 secs but it's still very long.

Tables:
Stats_Requests (id, sys_date, sys_hour, sys_quarter, req_page, req_user, querystring)
Stats_Pages (id, hostname, path, moduleindex)
Stats_Users (id, sys_dateuser, ip2long, sessionkey)

All dates are integers
Links between tables: SR.req_page = SP.id, SR.req_user = SU.id

I need to produce a report of the number of visits on the site between two dates, per module (using moduleindex) and a number of pages seen. If possible in a fast way and in one query. I have been stumped so far as to do both counts in one single query. I think it's not possible but i'll keep trying while i wait for answers.

Thanks
Last edited by crazycoders on Tue Nov 04, 2008 7:09 am, edited 1 time in total.
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Optimizing performance

Post by crazycoders »

In fact, lets be realistic, i know how to do the page hits at once:

Code: Select all

SELECT COUNT(stats_requests.id) AS pagehits, stats_pages.pageindexer FROM stats_requests LEFT JOIN stats_pages ON stats_pages.id = stats_requests.req_page WHERE sys_date BETWEEN 1199163600 AND 1230699600 GROUP BY stats_pages.pageindexer
But the visit count (unique users by module) is stumping me...
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Optimizing performance

Post by Eran »

to do another count, join again against the stats_pages using an alias. It's hard to tell more without seeing the full query (which you didn't post).

Also, 13 seconds is a very long time for a query... if you can, post the EXPLAIN results on the query
crazycoders
Forum Contributor
Posts: 260
Joined: Tue Oct 28, 2008 7:48 am
Location: Montreal, Qc, Canada

Re: Optimizing performance

Post by crazycoders »

It's always when you have searched for 2 hours and posted a thread on a forum that everything becomes clear... this is the query:

SELECT COUNT(DISTINCT stats_requests.req_user) AS pagevisitors, COUNT(stats_requests.id) AS pagehits, stats_pages.pageindexer FROM stats_requests LEFT JOIN stats_pages ON stats_pages.id = stats_requests.req_page WHERE sys_date BETWEEN '.$_GET['from']['stamp'].' AND '.$_GET['to']['stamp'].' AND stats_pages.pageindexer IS NOT NULL GROUP BY stats_pages.pageindexer

I just learned that i can put a COUNT(DISTINCT ...) in there, proof that you always learn stuff everyday... :)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Optimizing performance [SOLVED]

Post by josh »

Also take a look at the explain syntax, its useful for debugging SQL performance probs
Post Reply