Optimizing performance [SOLVED]

[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

[crazycoders]

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

Syntax: [ Download ] [ Hide ]

Syntax: [ Download ] [ Show ]

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...

[pytrin]

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]

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]

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