0

When using pagination in CakePHP, how does the queries work? Does it:

a) Query the entire record set and only display the records specified by the pagination settings?

b) Query only a subset of the records based on the pagination settings?

I want to avoid the query on millions of records if it doesn't use option "b". I have been searching the web for hours and I cannot find anything.

If it is option "a", do you know of any customizations that would convert pagination to option "b"? I am working with Oracle, so the standard 

SELECT * FROM `table`
LIMIT 60 , 30

doesn't work in Oracle like it does in MySQL. But I can easily convert a MySQL sample to Oracle as needed.

UPDATE: It appears to be limiting the data correctly, but I cannot see the query to know for sure that it is. When I echo out the results in the dbo_source.php, it only returns the number of records as defined by the pagination limit. Strange that it will not show the actually query used to paginate the records. Could it be an oracle setting they pass before the query is made?

Chuck Burgess
  • 11,600
  • 5
  • 41
  • 74

3 Answers3

2

There are several ways to see what queries are actually being executed.

The first way is to turn on mysql logging from mysql itself. As long as you are the only one using the server it should be perfectly sufficient.

The second way is to edit the dbo_* file in your cake installation and add a few lines to log the queries to a file. This can be done by opening your dbo_mysql.php file. It is located:

cake/libs/model/datasources/dbo/dbo_mysql.php

Find the _execute($sql) function. All you do is add a line or 3 of php code to output the $sql to a queries.log file.

Then to see your live queries running, open a terminal and run tail -f queries.log (using a mac or linux).

This is a great way to see what queries run and a great way to optimize your code. Also if you're curious, just browse through the dbo_* files to see how oracle is executed compared to mysql, etc.

Dooltaz
  • 2,413
  • 1
  • 17
  • 16
  • Thanks for the suggestions. I will take a look at this. I really like the DBO idea as it will feed the raw query as it is being executed on the server. This will be very helpful! Thanks! – Chuck Burgess Mar 25 '11 at 22:05
  • I tried this by adding **echo 'ORACLE: '.$sql;** right after the _execute statement. There is no pagination limiting being performed. It is running the entire query. Maybe this was updated in v1.3. But as for 1.2, it appears the Oracle DBO does not limit the query for pagination. Any other ideas? – Chuck Burgess Mar 29 '11 at 16:39
1

form mysql

cake does a find with limit and offset. so it will only get what you put in 'limit'. default is something like 20

first page would be limit 20, 0 then 20, 20 then 20, 40 or something like that.

for oracle, im not to sure. it will be something similar though.

dogmatic69
  • 7,574
  • 4
  • 31
  • 49
  • Do you know how to show the query in the debug so I can confirm this? The only query I see is the SELECT * FROM ... which indicates it is pulling ALL records. – Chuck Burgess Mar 25 '11 at 00:46
  • You are correct. I was merely simplifying. It does the SELECT ModelName.Field FROM table ModelName WHERE 1 = 1. But I am not seeing where the limits are added. This is the part that causes me to wonder if it is selecting all records or if it is truly LIMITING the data during the query. – Chuck Burgess Mar 25 '11 at 01:46
  • Paste actual code from your files and actual SQL output from the SQL log stuff, I don't believe you – dogmatic69 Mar 25 '11 at 07:42
  • If you do a `$this->Model->find('all')` you're going to get the query you describe. If you use `$this->paginate()` cakePHP will use limit. Set debug to 2 and look at the generated queries to confirm – JohnP Mar 26 '11 at 09:52
0

The best solution I have found so far is this: CakePHP - get last query run

It can be built right into the framework using app_model.php and provides access to any model by simply calling it.

Community
  • 1
  • 1
Chuck Burgess
  • 11,600
  • 5
  • 41
  • 74