in reply to Re-indexing a SQL resultset by id
If you could arrange things so that the query specifies ORDER BY id, then your logic here would become very simple. Every occurrence of any given id value would become, “by definition, adjacent,” and your logic could simply remember what was the value from the preceding row and notice if the value had changed.
Perl’s “auto-vivification” features can come in very handy here. If we have a hashref $foo which is to contain an arrayref of values found for each key, we can write very “streamlined” logic such as the following:
(caution... extemporaneous code ... Your Mileage May Vary™)while (my $row = $query->fetchrow_hashref) { my $key = $$row{'key'}; # PEDANTIC FOR CLARITY ... my $val = $$row{'value'}; push @{ $$foo{$key} }, $val; }
The “clever time-saving goodness” here being that, if $foo does not yet contain a hash-bucket for a given $key, then ... lo! ... magically, it does! And, it is understood to be an (empty) arrayref! And, the desired value magically gets pushed onto it!! (How convenient can you get?)
(In the example above, $$foo{'bletch'} is simply a shorthand for: $foo->{'bletch'} .)
If you combine this trick with the previously mentioned ORDER BY, the process actually becomes efficient, because the $key values will be arriving in an entirely predictable sequence. Page-faults will therefore be minimized. Even (especially...!) if you have millions of rows in your SQL table, this will make a very noticeable difference in speed.