SQL - get data filtered by conditions in that same table
Hi folks,
I hope you'll be able to help me solve this issue, that I'm trying to solve: We have a MySQL (don't ask) database with a bunch of tables. One of these tables is growing a bit large, and I wish to put all records older than 3 months into another table (I'm going to use select into backuptable once I'm sure I have the correct data here) The table that is growing too large is permtable, which holds quite some fields for each record; Among which are start_time (which holds the date/time when the record was inserted), event (which is 'C' for each record in a "good" situation, and another value in any other situation),group_id which combines the records that belong together and should be seen as a whole and repair_id which is set when an attempt was made to repair the non-good situation. I wish to extract from this table the dataset that covers these constraints: - all records older than 3 months - where all records with the same group_id are C, or in the case they're not C have repair_id set. The query I thought of was this one: Code:
select * from permtable p1 -- get all I also had another query, but that one fails due to deadlocking: Code:
select * from permtable Anyone who can help out pointing out what I'm doing wrong? |
Hi
Sure that #2... Code:
select * from permtable As an alternative, did you try to use an inline view (hopefully supported in MySQL) for #2 like... Code:
select * from |
Hi, thanks for the quick response! (And a happy 2013! :-))
The query might not be deadlocking, after all; it's just too slow and doing too many full table scans to be manageable. Haven't tried inline views (yet), it is actually one of the options I am looking into (not sure whether MySQL supports them or not, and what the implications might be... who knows) I'm currently trying out your second query. For figures: the table holds about 10M records, the subset < DATE(NOW() - INTERVAL 3 MONTH) yields 800K records; Just an indication about how tablescans will affect performance. I think the first query would cause 10M * 10M = lots of tablescans... it might be a tad too much, even 800K * 10M = still too many tablescans (sorry, I'm losing my head on the numbers, I don't even know how they'd be called... :-o Anyway, I realize I forgot to mention this, while it is a relevant part of the question. Also I was thinking if this would help me solve the riddle: Code:
create temporary table badgroup |
I ran this query:
Code:
select * from Since the table holds plus or minus 10M records the answer cannot be correct. This seems to do a full outer join, rather than an inline view. Thinking whether or not a function would solve the problem as well (rather than the temp table): something along these lines: Code:
create function is_completed(f_group_id varchar(36)) Code:
select * from permtable |
The function, along with adding an index made the query run fast enough and yield the correct results.
So marking this thread as solved. |
All times are GMT -5. The time now is 01:06 PM. |