jump to navigation

Optimizing your queries August 31, 2006

Posted by rossoft in CakePHP.
trackback

I’m working on a project with CakePHP & MySQL 5, but it needs to be
DBMS independant. I don’t like putting raw queries with Model->query.
Some complex queries require some mysql custom functions. Most of that functions are available in all the common DBMS, but have a different syntax.
For that purposes I use Mysql 5 Views. CakePHP hasn’t support for them yet, but I’ve reported a ticket for fixing it.
Mysql Views with CakePHP are very powerful.
You can create a view that links several tables with complex ‘not exists’ where clausules, then rename all the tables ‘id’ fields to model_id. Now you can use standard CakePHP belongsTo relations to retrieve the other fields that you need.
With this way you can create very complex queries with minimum overhead.
For maximum performance, you must create indexes. For seeing where you need them, use the query ‘EXPLAIN SELECT ….’.
It will return several rows, with a lot of info for each operation that the DBMS must do for retrieve your query. In the column ‘type’ it says how addresses the data. If it shows the ‘all’ type, then that’s the worst system. If you see lots of them, create indexes in the fields that you use in your where clausules and execute again the ‘explain’ statement for seeing if there’re improvement.
With some indexes and good views, you can have the most complex queries with good performance and DBMS independence.

Advertisements

Comments»

1. Boris Barrroso - August 31, 2006

I have used views sometimes but the limitation are that you can not use subqueries to make views. I have found that the most dificult queries use subqueries. But your recomendation is a great one.

2. rockdeman - September 11, 2006

Is a MySQL view the equivalent of a MS-SQL ‘stored procedure’ ?

3. rossoft - September 11, 2006

@rockdeman: no, it’s equivalent to MS-SQL Views 🙂

4. Derick - November 18, 2006

I was also looking forward to utilising MySQL Views with CakePHP but I’m not quite sure what should be done. I hope you could elaborate more. 🙂

Besides MySQL Views, is there anyway to make CakePHP model just grab those columns we specify like maybe overriding beforeFind in the models or something?

5. Zachary Naiman - April 24, 2007

I found the following dirty hack to get a model to work with a mysql view:

in the file dbo_source.php, find this code:
if (isset($model->{$className}) && is_object($model->{$className})) {
$data = $model->{$className}->afterFind(array(array($key => $results[$i][$key])), false);
}

and then add this right after it

else{
$data[0][$key] = $results[$i][$key];
}

I haven’t tested this too much, but it resolved my initial data retrieval problem. I imagine that it ignores the afterFind callback.

6. DavidTan - December 18, 2007

since this was last year’s post, wondering if cakephp has been updated to support mysql views…?

7. Jens - February 28, 2008

@DavidTan:

As a view is from the query point of view a virtual table you can access, you can use the view in a model like you would use any other table.

Or do you mean something other?

8. geld lenen - September 11, 2009

I believe it’s not updated yet


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: