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
Optimizing performance [SOLVED]
Moderator: General Moderators
-
crazycoders
- Forum Contributor
- Posts: 260
- Joined: Tue Oct 28, 2008 7:48 am
- Location: Montreal, Qc, Canada
Optimizing performance [SOLVED]
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
In fact, lets be realistic, i know how to do the page hits at once:
But the visit count (unique users by module) is stumping me...
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.pageindexerRe: Optimizing performance
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
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
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...
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...
Re: Optimizing performance [SOLVED]
Also take a look at the explain syntax, its useful for debugging SQL performance probs