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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

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

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post 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...
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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')
Post Reply