On Apr 20, 2007, at 2:20 PM, John Siracusa wrote:
> On 4/20/07, James Masters <[EMAIL PROTECTED]> wrote:
>> how can I see exactly what select statement Rose is doing?
>
> Either turn on DBI->trace(...) or set these variables:
>
> $Rose::DB::Object::Debug = 1;
> $Rose::DB::Object::Manager::Debug = 1;
>
> The DBI approach is probably better, but also noisier.
>
You can also turn on query logging for mysql & postgres in their
startup configurations. I always run logging on my dev machines ,
just to know whats going on.
EXPLAIN is your best friend. the mysql implementation isn't as great
as the Postgres, but it definitely helps. i'd suggest reading the
zawodny book 'high performance mysql' or something like that. its
much less about high-performance than real world experience and
common sense.
for instance, these are all *similar*
select * from account where email_address = ?
select * from account where email_address ilike ?
select * from account where lower(email_address) = ?
select * from account where lower(email_address) = lower(?)
the fastest way to run the query under postgres is:
create index _account__email_address_idx ON account(lower
(email_address));
select * from account where lower(email_address) = lower(?)
which creates a function index on the lowercase of the email , which
the planner will use when it optimizes the query
the table structure itself is the same, but using explain you can
understand and influence how the planner plans
on a small dataset, the difference is negligble. but on a set of
40million rows, the difference is between a few milliseconds and a
few minutes.
// Jonathan Vanasco
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| FindMeOn.com - The cure for Multiple Web Personality Disorder
| Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| RoadSound.com - Tools For Bands, Stuff For Fans
| Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
