(limit_with_subselect => 0, limit => 1) doesn't affect generated SQL

[kirill]

I'm relatively new to Rose::DB, it works fine for my project but this week i came across some misunderstanding.

I want get_objects to return me some offers with their "one to many" 'mbrs'..

Everything's ok when i'm trying to get all the offers:

my %args = (

    object_class    => 'Offer',

    nested_joins  => 0,

    with_objects => ['mbrs'],

);

Generated SQL is:

SELECT 

  t1.id,

/* ... */

  t2.id,

  t2.offer_id,

  t2.cbid,

  t2.cplace,

  t2.date_sync

FROM

  market_offers t1 

  LEFT OUTER JOIN market_bids_recommended t2 ON (t1.id = t2.offer_id)

ORDER BY t1.id

I want to get the same SQL appended with ' LIMIT 10'. I don't want any subselects, so i add 

    limit_with_subselect => 0,

    limit         => 10,

to my %args but SQL remains the same =(

The code of my 'relationship' (in case it matters):

package Offer;

use base 'Model::DBO';

__PACKAGE__->meta->table('market_offers');

# ....

__PACKAGE__->meta->add_relationship(

    mbrs => {

       type         => 'one to many',

       class        => 'MarketBidsRecommended',

       column_map   => { id => 'offer_id' },

    },

);

[John Siracusa]

Are the results correct? I ask because I believe that without a
subselect limit, the only alternative left is a "manual" limit where
the query remains the same, but fetching stops after the specified
number of objects from the primary table have been constructed.

-John

[kirill]

пятница, 24 августа 2012 г., 16:47:42 UTC+4 пользователь John Siracusa написал:

The problem is that database freezes when i run it.

But when i copy the query and manually run it in mysql console adding ' limit 10;' it gives me the right results in "0.00 sec".

Maybe it's due to some specific configuration?

mysql  Ver 14.14 Distrib 5.5.19, for Linux (x86_64)

Server version: 5.5.19-55 Percona Server (GPL), Release rel24.0, Revision 204

Protocol version: 10

The only option i have is to generate sql into a string and then manually append limits i need?

Can Rose::DB::Manager append 'LIMIT' when generating sql for drivers that support it? Or is there a way to redefine (monkey-patch?) some callback or so?

[John Siracusa]

On Fri, Aug 24, 2012 at 9:12 AM, kirill <kmat...@mgcom.ru> wrote:
> The problem is that database freezes when i run it.

That sounds like a different problem. Or maybe it's just a really
slow query due to database size and available memory and other server
resources?

> But when i copy the query and manually run it in mysql console adding '
> limit 10;' it gives me the right results in "0.00 sec".

Why are you explicitly disabling the subselect limit? Does MySQL not
support it?

> The only option i have is to generate sql into a string and then manually
> append limits i need?

That query would be incorrect. You'd be limiting it to 10 rows, not
10 Offers. If the first Offer has 7 associated MarketBidsRecommended
objects and the second has 5, a "LIMIT 10" clause on the query would
result in one Offer with its 7 associated MarketBidsRecommended
objects and a second Offer with 3 of its 5 associated
MarketBidsRecommended objects.

-John

[kirill]

On Fri, Aug 24, 2012 at 9:12 AM, kirill <kmat...@mgcom.ru> wrote:
> The problem is that database freezes when i run it.

That sounds like a different problem.  Or maybe it's just a really
slow query due to database size and available memory and other server
resources?

Yes, i think it is slow.

 

That query would be incorrect.  You'd be limiting it to 10 rows, not
10 Offers.  If the first Offer has 7 associated MarketBidsRecommended
objects and the second has 5, a "LIMIT 10" clause on the query would
result in one Offer with its 7 associated MarketBidsRecommended
objects and a second Offer with 3 of its 5 associated
MarketBidsRecommended objects.

 

Thank you, now i get it. This should be database problem.

Or i need to join MarketBidsRecommended N times to get N mbrs per 1 row.

[John Siracusa]

On Fri, Aug 24, 2012 at 9:41 AM, kirill <kmat...@mgcom.ru> wrote:
>> That query would be incorrect. You'd be limiting it to 10 rows, not
>> 10 Offers. If the first Offer has 7 associated MarketBidsRecommended
>> objects and the second has 5, a "LIMIT 10" clause on the query would
>> result in one Offer with its 7 associated MarketBidsRecommended
>> objects and a second Offer with 3 of its 5 associated
>> MarketBidsRecommended objects.
>
> Thank you, now i get it. This should be database problem.
>
> Or i need to join MarketBidsRecommended N times to get N mbrs per 1 row.

The subselect limit ensures that you get 10 Offers, but there may be
many more than 10 rows returned. Rose::DB::Object coalesces all the
redundant data for you. You don't need N joins.

-John

[kirill]

But according to explain, subselect limit uses temporary table and filesort even in a simple case:

SELECT 

  t1.id, t2.offer_id

FROM (

SELECT DISTINCT t1.id FROM

  market_offers t1 

  LEFT OUTER JOIN market_bids_recommended t2 ON (t1.id = t2.offer_id)

  

ORDER BY t1.id

LIMIT 10

  ) t1 

  LEFT OUTER JOIN market_bids_recommended t2 ON (t1.id = t2.offer_id)

ORDER BY t1.id

+----+-------------+------------+-------+--------------------+----------+---------+--------------+---------+----------------------------------------------+

| id | select_type | table      | type  | possible_keys      | key      | key_len | ref          | rows    | Extra                                        |

+----+-------------+------------+-------+--------------------+----------+---------+--------------+---------+----------------------------------------------+

|  1 | PRIMARY     | <derived2> | ALL   | NULL               | NULL     | NULL    | NULL         |      10 | Using filesort                               |

|  1 | PRIMARY     | t2         | ref   |           offer_id | offer_id | 4       | t1.id        |       5 | Using index                                  |

|  2 | DERIVED     | t1         | index | PRIMARY            | deleted  | 1       | NULL         | 1059727 | Using index; Using temporary; Using filesort |

|  2 | DERIVED     | t2         | ref   |           offer_id | offer_id | 4       | market.t1.id |       5 | Using index                                  |

+----+-------------+------------+-------+--------------------+----------+---------+--------------+---------+----------------------------------------------+

market_offers.id is primary key and market_bids_recommended.offer_id is key.

Or I'm doing it absolutely wrong =(

-John

[John Siracusa]

On Fri, Aug 24, 2012 at 11:02 AM, kirill <kmat...@mgcom.ru> wrote:
> But according to explain, subselect limit uses temporary table and filesort
> even in a simple case:

That's between you and your database…

-John