MySQL Statements For BW Accounting

[Steve]

I just thought I would start a thread for SQL challenged people like myself to share some SELECT queries....

My Contributions:

Todays download for a specific IP (in megs)

select SUM(datacount)/1024/1024 from bwusage
where dataclient = "192.168.1.1"
and timemark >= UNIX_TIMESTAMP(CURDATE());

Todays upload for a specific IP (in megs)

select SUM(datacount)/1024/1024 from bwusage
where datahost = "192.168.1.1"
and timemark >= UNIX_TIMESTAMP(CURDATE());


Total download for 2 IP blocks in megs

select dataclient, SUM(datacount)/1024/1024 from bwusage
WHERE dataclient LIKE "192.168.1.%"
OR dataclient LIKE "192.168.2.%"
GROUP by dataclient ORDER by dataclient;


Total upload for 2 IP blocks in megs

select datahost, SUM(datacount)/1024/1024 from bwusage
WHERE datahost LIKE "192.168.1.%"
OR datahost LIKE "192.168.2.%"
GROUP by datahost ORDER by datahost;



And could someone explain to me why the above two SELECTS will run in ~30 seconds but the following one takes 9 minutes!!!!

select dataclient, SUM(datacount) from bwusage
WHERE dataclient LIKE "192.168.1.%"
GROUP by dataclient ORDER by dataclient;


I used EXPLAIN on the SELECT statement and can definately see a difference, but it didn't EXPLAIN much.

I would appreciate any feedback.

Steve
Northcc.net

[Anonymous]

just a few quick questions about the data structures.

1. Does the queried table contain a index that covers all of the columns selected.

2. the group by statement forces a table scan most likely.