Database Query taking forever... okay it's three seconds.
Posted: Fri Aug 11, 2006 3:43 pm
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:
'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:
It takes 3 seconds on a database size of 30,000 entries
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) unsignedvariables 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;