in reply to Much slower DBI on RHEL6

I don't know what's wrong with it -- you should try some place specialising in MySQL. It seems unlikely that Perl/DBI are at fault unless RHEL has done something wonky with the relevant packages.

Anyway, you seem to be doing a death by a thousand queries there and that means _many_ round-trips. I'm sure the script can be rewritten to perform a single query. This is how I'd do it with Postgres-specific syntax: 

SELECT DISTINCT ON (id) id, description
FROM list_index li
ORDER BY id, rpt_key DESC;

[download]
Writing it with a correlated subquery, we get a query that is terribly slow (one second on my couple-of-thousand-rows dataset): 
SELECT DISTINCT id,
    (SELECT description from list_index li_i
     WHERE li_i.id = li_o.id
     ORDER BY created_at DESC limit 1) AS description
    FROM list_index li_o;

[download]
But moving the distinctness to a subquery makes it fast (~7 milliseconds): 
SELECT id,
    (SELECT description from list_index li_i
     WHERE li_i.id = li_o.id
     ORDER BY created_at DESC limit 1) AS description
    FROM
        (SELECT DISTINCT id FROM list_index) li_o;

[download]
The SQL should work on every dialect, but of course, these speed measurements apply only to the PostgreSQL query planner. MySQL's is different. Try and see.

...My brains aren't working well enough right now to produce a way to do it without a dependent subquery.

Replies are listed 'Best First'.
Re^2: Much slower DBI on RHEL6
by Anonymous Monk on Feb 07, 2014 at 07:12 UTC
    And now that my brains are working again, a third option that is quicker than all of those on Postgres, but YMMV: 
    select li_i.id, description from list_index li_i
join (
    select id, max(rpt_key) as rpt_key from list_index group by id
) li_o
on li_i.id = li_o.id and li_i.rpt_key = li_o.rpt_key;

    [download]
    I assume here that the pair (id, rpt_key) is unique, but it should not hurt even if it isn't.
[reply]
[d/l]
[select]

      Even though I have things working in a sufficient manner, I'll give this a shot as well. I agree MySQL should be able to be responsible for more of the processing. I'll report back with my findings. THanks.

[reply]

        WOW! Your suggestion seems to work great!!! Prior to your modification I found tux's suggestion was much faster on RHEL6 compared to the original code on RHEL6. On RHEL 4, tux's suggestion seemed to be just a slight bit faster then the original code on RHEL4. Adding in your query make is very fast on both platforms. Thanks so much! Now, if possible and if it is not to much to ask, can you break down what that query is actually doing? I don't have any experience with queries that complicated. Pretty much just simple selects and updates. Thanks again!

[reply]
Re^2: Much slower DBI on RHEL6
by Anonymous Monk on Feb 06, 2014 at 10:24 UTC
    Bah. s/created_at/rpt_key/
[reply]
[d/l]

In Section Seekers of Perl Wisdom