Page 1 of 1

Database Query taking forever... okay it's three seconds.

Posted: Fri Aug 11, 2006 3:43 pm
by Todd_Z
I have a table that keeps track of the changes to the other tables, and I would like to query that tracking table for a result set of all the chances that one user made during a time period, and whether that entry was the first of its kind or not. Here is the DB schema to be more clear:

Code: Select all

| type      | varchar(8)
| parent_id | int(32) unsigned
| variables | text
| values    | text
| user_id   | int(10) unsigned
| timestamp | datetime
| id        | int(36) unsigned
'type' is the name of the table that the entry is referencing. parent_id is the id in that table.
variables are the fields, values are values.

Query input: user_id, type, time_start, time_end
Query output: list of parent_ids added and updated during the time period, with notification of whether it was an add or an update.


What I've got so far:

Code: Select all

SELECT DISTINCT(parent_id), ( SELECT COUNT(*) FROM tracking AS t WHERE type = 'property' AND parent_id = tracking.parent_id AND timestamp < tracking.timestamp ) > 0 AS new FROM tracking WHERE type = 'property' AND timestamp > NOW()-INTERVAL 30 DAY AND user_id = 1 LIMIT 10;
It takes 3 seconds on a database size of 30,000 entries

Posted: Fri Aug 11, 2006 3:56 pm
by feyd

Code: Select all

SELECT `parent_id`, COUNT(`parent_id`) as `new` FROM `tracking` WHERE `type` = 'property' AND `timestamp` BETWEEN NOW()-INTERVAL 30 DAY AND NOW() AND`user_id` = 1 GROUP BY `parent_id` LIMIT 10
maybe.

Posted: Fri Aug 11, 2006 5:16 pm
by nickvd
NOW()-INTERVAL 30 DAY AND NOW()
8O 8O

Was not aware of this ability, i mean, i knew about now(), but INTERVAL is new to me.. and seemingly VERY VERY useful in the right circumstances...

Posted: Sat Aug 12, 2006 9:45 am
by Todd_Z
That query works great [and real fast], however there is one problem.

If there are two entries for the same object, one in july 2006, and one in august 2006, then when you do a query on the month of july, then the query will return that there is a count of 2, even though the timestamp of the second one is after the range in the BETWEEN statement.

Posted: Sat Aug 12, 2006 9:51 am
by feyd
If the July entry is within the 30 day window, it will be included. Unless you want to make it only look at the current month (or inject some PHP rendered DATETIME strings) there's not much that can be done.

Posted: Sat Aug 12, 2006 9:57 am
by Todd_Z
Essentially I want to be looking at only the tracking entries which we entered before the August 1 at 12am, then use the query you provided. Is there are way to have the COUNT statement only count the entries which were entered before the timestamp specified for the later part of the between statement?

Posted: Sat Aug 12, 2006 10:09 am
by feyd
Before a given DATETIME:

Code: Select all

WHERE ... `timestamp` < '$dateTime'
Before NOW()

Code: Select all

WHERE ... `timestamp` < NOW()
Current month:

Code: Select all

WHERE ... EXTRACT(YEAR_MONTH FROM `timestamp`) = EXTRACT(YEAR_MONTH FROM NOW())
Specific month (from a DATETIME string):

Code: Select all

WHERE ... EXTRACT(YEAR_MONTH FROM `timestamp`) = EXTRACT(YEAR_MONTH FROM '$dateTime')
All previous months:

Code: Select all

WHERE ... EXTRACT(YEAR_MONTH FROM `timestamp`) < EXTRACT(YEAR_MONTH FROM NOW())
All previous months from a specific month (provided via a DATETIME string):

Code: Select all

WHERE ... EXTRACT(YEAR_MONTH FROM `timestamp`) < EXTRACT(YEAR_MONTH FROM '$dateTime')