CODEWITHSUNDEEP
mysqlsqlcakephpcakephp-2.0
emersonthis
emersonthis

Reputation: 33408

CakePHP: Is it possible to force find() to run a single MySQL query

I'm using CakePHP 2.x. When I inspect the sql dump, I notice that it's "automagic" is causing one of my find()s to run several separate SELECT queries (and then presumably merging them all together into a single pretty array of data).

This is normally fine, but I need to run one very large query on a table of 10K rows with several joins, and this is proving too much for the magic to handle because when I try to construct it through find('all', $conditions) the query times out after 300 seconds. But when I write an equivalent query manually with JOINS, it runs very fast.

My theory is that whatever PHP "magic" is required to weave the separate queries together is causing a bottleneck for this one large query.

  1. Is my theory a plausible explanation for what's going on?
  2. Is there a way to tell Cake to just keep it simple and make one big fat SELECT instead of it's fancy automagic?

Update: I forgot to mention that I already know about $this->Model->query(); Using this is how I figured out that the slow-down was coming from PHP magic. It works when we do it this way, but it feels a little clunky to maintain the same query in two different forms. That's why I was hoping CakePHP offered an alternative to the way it builds up big queries from multiple smaller ones.

Upvotes: 1

Views: 274

Answers (1)

Guillermo Mansilla
Guillermo Mansilla

Reputation: 3889

In cases like this where you query tables with 10k records you shouldn't be doing a find('all') without limiting the associations, these are some of the strategies you can apply:

  1. Set recursive to 0 If you don't need related models
  2. Use Containable Behavior to bring only the associated models you need.
  3. Apply limits to your query
  4. Caching is a good friend
  5. Create and destroy associations on the fly As you need.

Since you didn't specify the problem I just gave you general ideas to apply depending on the problem you have

Upvotes: 0

Related Questions