per_page and composite_primary_keys

Ok, I have been working on this all night and can't seem to think of an easy fix. If you need any type of data please let me know.

The plugin basically just adds composite PK support as transparently as possible.

As for the SQL syntax:

Code:

@events ||= Event.all.paginate(:page => params[:page], :per_page => 5, :order => 'timestamp DESC')

Output:

Processing EventsController#index (for 127.0.0.1 at 2009-06-25 03:53:56) [GET]
Event Load (0.3ms) SELECT * FROM event ORDER BY timestamp DESC LIMIT 0, 5 Event Columns (1.8ms) SHOW FIELDS FROM event SQL (0.7ms) SELECT count(*) AS count_all FROM event Rendering template within layouts/application
Rendering events/index
Signature Columns (1.3ms) SHOW FIELDS FROM signature Signature Load (0.4ms) SELECT * FROM signature WHERE (signature.sig_id = 37) Iphdr Columns (7.7ms) SHOW FIELDS FROM iphdr Iphdr Load (2.2ms) SELECT * FROM iphdr WHERE ((iphdr.sid=7 AND iphdr.cid=471)) Sensor Columns (2.0ms) SHOW FIELDS FROM sensor Sensor Load (1.4ms) SELECT * FROM sensor WHERE (sensor.sid = 7) Signature Load (0.2ms) SELECT * FROM signature WHERE (signature.sig_id = 29) Iphdr Load (0.2ms) SELECT * FROM iphdr WHERE ((iphdr.sid=7 AND iphdr.cid=470)) CACHE (0.0ms) SELECT * FROM sensor WHERE (sensor.sid = 7) Signature Load (0.2ms) SELECT * FROM signature WHERE (signature.sig_id = 15) Iphdr Load (0.2ms) SELECT * FROM iphdr WHERE ((iphdr.sid=7 AND iphdr.cid=469)) CACHE (0.0ms) SELECT * FROM sensor WHERE (sensor.sid = 7) SigClass Columns (1.5ms) SHOW FIELDS FROM sig_class SigClass Load (1.1ms) SELECT * FROM sig_class WHERE (sig_class.sig_class_id = 2) CACHE (0.0ms) SELECT * FROM signature WHERE (signature.sig_id = 29) Iphdr Load (0.2ms) SELECT * FROM iphdr WHERE ((iphdr.sid=7 AND iphdr.cid=468)) CACHE (0.0ms) SELECT * FROM sensor WHERE (sensor.sid = 7) CACHE (0.0ms) SELECT * FROM signature WHERE (signature.sig_id = 15) Iphdr Load (0.2ms) SELECT * FROM iphdr WHERE ((iphdr.sid=7 AND iphdr.cid=467)) CACHE (0.0ms) SELECT * FROM sensor WHERE (sensor.sid = 7) CACHE (0.0ms) SELECT * FROM sig_class WHERE (sig_class.sig_class_id = 2) Rendered events/event (36.2ms)
Rendered events/event (3.7ms)
Rendered events/event (2.9ms)
Rendered events/event (2.8ms)
Rendered events/event (2.8ms)
Rendered layouts/header (0.2ms)
Rendered layouts/_footer (0.1ms)
Completed in 57ms (View: 33, DB: 22) | 200 OK [http://0.0.0.0/]

'Where is it wrong? How it should be correct?'

Well, it seems to loop the data 5 times or whatever is set for :per_page. It should just show 5 events per page.

If this is not helpful please let me know. I would really like to help to fix this issue.

Ok that looked horrible sorry.

Processing EventsController#index (for 127.0.0.1 at 2009-06-25 03:53:56) [GET]
  Event Load (0.3ms)   SELECT * FROM `event` ORDER BY timestamp DESC LIMIT 0, 5
  Event Columns (1.8ms)   SHOW FIELDS FROM `event`
  SQL (0.7ms)   SELECT count(*) AS count_all FROM `event` 
Rendering template within layouts/application
Rendering events/index
  Signature Columns (1.3ms)   SHOW FIELDS FROM `signature`
  Signature Load (0.4ms)   SELECT * FROM `signature` WHERE (`signature`.`sig_id` = 37) 
  Iphdr Columns (7.7ms)   SHOW FIELDS FROM `iphdr`
  Iphdr Load (2.2ms)   SELECT * FROM `iphdr` WHERE ((`iphdr`.`sid`=7 AND `iphdr`.`cid`=471)) 
  Sensor Columns (2.0ms)   SHOW FIELDS FROM `sensor`
  Sensor Load (1.4ms)   SELECT * FROM `sensor` WHERE (`sensor`.`sid` = 7) 
  Signature Load (0.2ms)   SELECT * FROM `signature` WHERE (`signature`.`sig_id` = 29) 
  Iphdr Load (0.2ms)   SELECT * FROM `iphdr` WHERE ((`iphdr`.`sid`=7 AND `iphdr`.`cid`=470)) 
  CACHE (0.0ms)   SELECT * FROM `sensor` WHERE (`sensor`.`sid` = 7) 
  Signature Load (0.2ms)   SELECT * FROM `signature` WHERE (`signature`.`sig_id` = 15) 
  Iphdr Load (0.2ms)   SELECT * FROM `iphdr` WHERE ((`iphdr`.`sid`=7 AND `iphdr`.`cid`=469)) 
  CACHE (0.0ms)   SELECT * FROM `sensor` WHERE (`sensor`.`sid` = 7) 
  SigClass Columns (1.5ms)   SHOW FIELDS FROM `sig_class`
  SigClass Load (1.1ms)   SELECT * FROM `sig_class` WHERE (`sig_class`.`sig_class_id` = 2) 
  CACHE (0.0ms)   SELECT * FROM `signature` WHERE (`signature`.`sig_id` = 29) 
  Iphdr Load (0.2ms)   SELECT * FROM `iphdr` WHERE ((`iphdr`.`sid`=7 AND `iphdr`.`cid`=468)) 
  CACHE (0.0ms)   SELECT * FROM `sensor` WHERE (`sensor`.`sid` = 7) 
  CACHE (0.0ms)   SELECT * FROM `signature` WHERE (`signature`.`sig_id` = 15) 
  Iphdr Load (0.2ms)   SELECT * FROM `iphdr` WHERE ((`iphdr`.`sid`=7 AND `iphdr`.`cid`=467)) 
  CACHE (0.0ms)   SELECT * FROM `sensor` WHERE (`sensor`.`sid` = 7) 
  CACHE (0.0ms)   SELECT * FROM `sig_class` WHERE (`sig_class`.`sig_class_id` = 2) 
Rendered events/_event (36.2ms)
Rendered events/_event (3.7ms)
Rendered events/_event (2.9ms)
Rendered events/_event (2.8ms)
Rendered events/_event (2.8ms)
Rendered layouts/_header (0.2ms)
Rendered layouts/_footer (0.1ms)
Completed in 57ms (View: 33, DB: 22) | 200 OK [http://0.0.0.0/]

I don't understand. First of all, you said:

Event.all.paginate(...)

This is wrong. It should be Event.paginate(), because Event.all loads all the records and you don't want that. However, from your log I see that indeed Event.paginate is executed, so I'm guessing you pasted wrong.

Second of all, everything seems in order. These look perfectly OK:

SELECT * FROM `event` ORDER BY timestamp DESC LIMIT 0, 5
SELECT count(*) AS count_all FROM `event`

The rest of the queries are triggered in your templates while the loop goes through all 5 records. This can be kinda slow, and you can improve the load times with eager includes:

Event.paginate(:include => [:signature, ...])

Looking from what you pasted I don't see obvious errors and where at all are composite primary keys used.

You are correct the issue was unrelated. I am sorry to have wasted your time and thank you for the eager tip. This ticket is good to close.