I have this table:
I need to query the table and ask the following questions:
How many rows matching uri 'x' exist with unique IP addresses between 'y' and 'z' dates.
I was experimenting with COUNTs but I couldn't seem to get the result I needed...
+5
A:
Try using COUNT(DISTINCT())
Returns a count of the number of rows with different non-NULL expr values.
So your query would be something like
SELECT COUNT( DISTINCT( user_ip ) )
FROM table
WHERE timestamp BETWEEN Y and Z
ConroyP
2010-09-02 15:29:43
If so, then:SELECT COUNT( DISTINCT( user_ip ) ) FROM table WHERE uri = 'x' ANDWHERE timestamp BETWEEN Y and Zand to make this efficient don't forget to create index like:`idx_myindesx` (user_ip, uri, timestamp)
c64ification
2010-09-02 15:34:00
If I create an index do I need to change the query at all?
Plasticated
2010-09-02 15:57:07
@Plasticated no, the query will still work fine, creating an index may help to speed up how MySQL processes it internally - http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
ConroyP
2010-09-02 16:03:40
+1
A:
The DISTINCT keyword enforces that only unique entries are returned in the query. As the above answer by ConroyP mentions you can nest DISTINCT within other functions in SQL. Note that this is has a different function from 'UNIQUE' which enforces uniqueness constraints in the Data Definition Language part of SQL. Some databases also allow you to substitute the keyword UNIQUE for DISTINCT in the query - but this doesn't seem to work everywhere.
Richard Warburton
2010-09-02 15:35:28