Get row before SELECT query - Page 2

[riahc3]

Lets see how long this one takes....

[diafol]

Out of interest how are you going to apply the 'next' cycle_id when inserting new data? What about concurrency - could be an issue.

Also how often are you going to run this 'max' query?

If you are going to make a lot of calls to the big table, perhaps a bit of tinkering may help -e.g. creating a maxv column (tinyint) 1=max, 0=not and then it's just a simple query WHERE maxv = 1

Or perhaps set up a 1:1 table called 'max' and just store the max values there. However, with 5M records, you're probably going to end up with 1M records in that one too. :(

Does the whole data need to be stored forever or can it be archived after a certain time? If so, you could keep the 'current' table size relatively small.

[riahc3]

That query never finished BTW.

Just in case the table isnt clear:

I insert cycle each time one until it reaches n (n right now is 250) and cycle_id thruout that is the same number. Once it reaches 250, and then starts at one again, cycle_id is +1

cycle will NEVER jump numbers like your table. It will ALWAYS been 1,2,3,4 etc. I just do not know when it ends and starts at 1. I only know it always starts at 1

I hope this clears some things up.

[diafol]

just do not know when it ends and starts at 1. I only know it always starts at 1
I hope this clears some things up.

Yep, that's about as clear as mud :)

That query never finished BTW.

Which one? The update or the select?

[riahc3]

This query never finished:

SELECT times.* FROM times 
    INNER JOIN (SELECT *, MAX(cycle) AS maxcycle FROM times 
    GROUP BY cycle_id) AS grouped 
    ON times.cycle_id = grouped.cycle_id 
       AND times.cycle = grouped.maxcycle

Started at 1300 and it was still running at 1700

[diafol]

UPDATE: http://www.screencast.com/t/uoIMkRWgAX0q

SELECT: http://www.screencast.com/t/LbrMHsPLY3

Join and Group will take a long time :(

You didn't say how often you are thinking of running this query.

[riahc3]

UPDATE: http://www.screencast.com/t/uoIMkRWgAX0q
SELECT: http://www.screencast.com/t/LbrMHsPLY3
Join and Group will take a long time :(
You didn't say how often you are thinking of running this query.

Basically, someone will select a start time/date and a end time/date. A query will be run between those two dates and it will show in one list box when a cycle started (1) and when it ended (the next 1 but the row before it) in another list box. This this I can also calculate the duration of the cycle which is VERY important.

[riahc3]

This query seems to be better:

select mytable.* from mytable
    inner join (select *, max(cycle) as maxcycle from mytable
    group by cycle_id) as grouped
    on mytable.cycle_id = grouped.cycle_id
    and mytable.cycle = grouped.maxcycle
    WHERE ('2014-04-24 09:09:37' < Time_Stamp 
    OR ('2014-04-24 09:09:37' = Time_Stamp AND  765 <= Time_Stamp_ms)) 
    AND ('2014-04-24 09:10:38' > Time_Stamp 
    OR ('2014-04-24 09:10:38' = Time_Stamp AND Time_Stamp_ms <= 5))

After 76 seconds it gives me a error saying: "Error Code: 1052. Column 'Time_Stamp' in where clause is ambiguous"

[riahc3]

    select mytable.* from mytable
    inner join (select *, max(cycle) as maxcycle from mytable WHERE ('2014-04-24 09:09:37' < Time_Stamp 
    OR ('2014-04-24 09:09:37' = Time_Stamp AND  765 <= Time_Stamp_ms)) 
    AND ('2014-04-24 09:10:38' > Time_Stamp 
    OR ('2014-04-24 09:10:38' = Time_Stamp AND Time_Stamp_ms <= 5))
    group by cycle_id ) as grouped
    on mytable.cycle_id = grouped.cycle_id
    and mytable.cycle = grouped.maxcycle

Hmm this looks intresting....This gives me

    '2014-04-24 09:09:57', '685', '5555', '4444', '3333', '2222', '1111', '123', '250', 'name', '1398323397.6850', '1'
'2014-04-24 09:10:17', '685', '5554', '4443', '3332', '2221', '1110', '123', '250', 'name', '1398323417.6850', '2'
'2014-04-24 09:10:37', '685', '5555', '4444', '3333', '2222', '1111', '123', '250', 'name', '1398323437.6850', '3'
'2014-04-24 09:10:38', '5', '5554', '4443', '3332', '2221', '1110', '123', '4', 'name', '1398323438.0050', '4'

Which is indeed technically correct. Even though I know that "1" is the start of each cycle, would this work as well:

    select mytable.* from mytable
    inner join (select *, min(cycle) as mincycle from mytable WHERE ('2014-04-24 09:09:37' < Time_Stamp 
    OR ('2014-04-24 09:09:37' = Time_Stamp AND  765 <= Time_Stamp_ms)) 
    AND ('2014-04-24 09:10:38' > Time_Stamp 
    OR ('2014-04-24 09:10:38' = Time_Stamp AND Time_Stamp_ms <= 5))
    group by cycle_id ) as grouped
    on mytable.cycle_id = grouped.cycle_id
    and mytable.cycle = grouped.mincycle

???

'2014-04-24 09:09:37', '765', '5555', '4444', '3333', '2222', '1111', '123', '1', 'name', '1398323377.7650', '1'
'2014-04-24 09:09:57', '765', '5554', '4443', '3332', '2221', '1110', '123', '1', 'name', '1398323397.7650', '2'
'2014-04-24 09:10:17', '765', '5555', '4444', '3333', '2222', '1111', '123', '1', 'name', '1398323417.7650', '3'
'2014-04-24 09:10:37', '765', '5554', '4443', '3332', '2221', '1110', '123', '1', 'name', '1398323437.7650', '4'

Yup, works as well :) I would just have to discard the last row of both the start and the end (since the end one ends at "4" instead of "250")

[riahc3]

Could you explain the query? I know it first does a first query getting the maxcycle between two dates. But then Im not completely sure the grouping...

[diafol]

OK, for the explanation:

Thinking about it, try it as a simpler query...

SELECT times.* FROM times 
    INNER JOIN (SELECT cycle_id, MAX(cycle) AS maxcycle FROM times
    GROUP BY cycle_id) AS grouped 
    ON times.cycle_id = grouped.cycle_id 
       AND times.cycle = grouped.maxcycle

The derived table 'grouped':

SELECT cycle_id, MAX(cycle) AS maxcycle FROM times GROUP BY cycle_id

Returns just this...

cycle_id - maxcycle
1 - 13
2 - 6
3 - 6
4 - 3

So the INNER JOIN 'filters' all results to just rows in the 'times' table where the cycle_id and the cycle are equal to the cycle_id and the maxcycle from the 'grouped' table.

[riahc3]

The "simpler query" never gave me results as I imagine it goes thru EVERYTHING. While the one where I filter by date and time does take a while (a minute) but gives me the results.

Or do you mean something else?

BTW, I hate SQL/DBs...

[diafol]

Your table is a bit messy with all the timestamp stuff. Retrieving maxes between two datetimes should be straightforward:

BETWEEN ... AND ... (inclusive)

2014-04-24 09:09:37.725 should give: 1398326977.725

2014-04-24 09:10:38.005 should give: 1398327038.005

So a simple WHERE clause could be

WHERE Time_Stamp_Full BETWEEN 1398326977.725 AND 1398327038.005

But, I don't know how you're going to query this - from a form?

[diafol]

The "simpler query" never gave me results as I imagine it goes thru EVERYTHING. While the one where I filter by date and time does take a while (a minute) but gives me the results.

Yes well it would, obviously - no filters.

BTW, I hate SQL/DBs...

Programming too judging by your signature. Have you considered a different career? :)

[riahc3]

But, I don't know how you're going to query this - from a form?

Drop down box to make sure that a idiot doesnt insert "hi" or anything. Depending on the date and the hh:mm:ss inserted, I also get the miliseconds to make sure that there is valid entries.

Your table is a bit messy with all the timestamp stuff. Retrieving maxes between two datetimes should be straightforward:

Seeing as I dont use Time_Stamp_Full, Ill problably remove that column. It was just for testing purposes.

Now just gotta figure out a way to make sure I get a even ammount of start rows and end rows but I think that will be pretty simple (at least I hope).

Im gonna try implementing this...thank you for your help.

[diafol]

OK, good luck.