This is with MySQL 5.5.22 and InnoDB table. Agreed that the primary_key [ :ide, :rev, :typ ] is there, even on my old version. My memory is a little fuzzy, but I think that the query was ORDER BY typ, ide, rev without any where clause. I'm pretty sure that explain showed no index and file sort, which made total sense to me once I saw the table definition. I had to add an index, changing the primary key just turned up a different set of queries in the slow query log. At any rate, once I added the index, my ruote got a lot more stable, and faster. Beats me why pg would say that the primary key is usable - I would think it would at least have to sort the index - but for MySQL, there are definitely some common queries where the primary key isn't used.
On Wednesday, July 25, 2012 9:10:15 PM UTC-4, John Mettraux wrote: > > On Wed, Jul 25, 2012 at 08:08:41AM -0700, Steve Sexton wrote: > > > > I am also trying to bring up a new ruote implementation using > > Ruote::Sequel. On my last post John recommended to use edge Ruote > rather > > than official 2.2.0 release. We still haven't done that, but we did > find, > > when we were doing SQL performance tuning for the app, that > Ruote::Sequel > > does a TON of queries on documents using (typ, ide, rev) as keys. These > > showed up in the MySQL slow query log, too. There is no index for that > > query! Once we added the index we've had much better performance with > > ruote. > > > > So, you might want to try adding an index on documents table and see if > > that helps. We use rails, so we did it with a migration: > > add_index :documents, [:type, :ide, :rev] > > Hello Steve, > > that's weird. Even the 2.2.0 version you're using has > > primary_key [ :ide, :rev, :typ ] > > > https://github.com/jmettraux/ruote-sequel/blob/a815d93e55af59e82250614e0cddfd9c68334638/lib/ruote/sequel/storage.rb#L49 > > > Playing with PostgreSQL and explain, the index associated with the primary > key makes it in. > > Anyway, I changed a bit: > > > https://github.com/jmettraux/ruote-sequel/commit/61e09fe0c96c1dc25a17c9d0a74ae155851b8a0b > > > What database are you using? Any other tip that could benefit us? > > (I'll anyway take some time to a) make sure ruote queries the storage less > (for example there are too many get('configuration', 'engine') calls b) > implement some simple optimization in the storages themselves). > > > Best regards, > > -- > John Mettraux - http://lambda.io/jmettraux > -- you received this message because you are subscribed to the "ruote users" group. to post : send email to [email protected] to unsubscribe : send email to [email protected] more options : http://groups.google.com/group/openwferu-users?hl=en
