使用CakePHP，Mamp非常慢

Question

I have a Cake Application v2.3.1 running locally on mamp v2.1.3 (not pro so this is the latest version) from myapp.dev:port and I'm experiencing some really slow response when I have pagination on the site (about 5 seconds), here's my code in the controller:

public $paginate = array(
        'fields' => array('Artist.id, Artist.year_born, Artist.year_died, Artist.country_born'),
        'limit' => 50,
        'order' => array('Artist.id' => 'desc')
    );

I use the twitter bootstrap plugin for my pagination https://github.com/slywalker/TwitterBootstrap like this in AppController.php:

public $helpers = array('Paginator' => array('className' => 'TwitterBootstrap.BootstrapPaginator'));

I tried to set the limit of the pagination to 1 and also tried using cakes own pagination instead of bootstrap, but it's still really slow. I has to be something with the pagination because I can access an artist like myapp.dev:port/artists/view/14532 and that works good so I don't think it has anything to do with the sql query.

I tried around changing names in the /etc/hosts file 127.0.0.1 localhost myapp.dev and ::1 localhost myapp.dev but nothing seems to work. Any ideas? I'm really stuck.

Update: Feel a bit bad about not mentioning the hasMany relationship that my Artist table have. This is my Artist model:

class Artist extends AppModel {

    public $hasMany = array(
        'ArtistBiography' => array('dependent' => true),
        'ArtistSurname' => array('dependent' => true),
        'ArtistSignature' => array('dependent' => true),
        'ArtistForename' => array('dependent' => true),
        'ArtistMonogram' => array('dependent' => true));
    public $hasOne = array(
        'ArtistActive' => array('dependent' => true));
}

I used the DebugKit and I found that the paginator does 2 unnecessary querys for two left joins that both takes around 2000 ms each to execute. How can I tell the paginator to ignore this? I tried something like this to give a new relationship:

public $paginate = array(
        'fields' => array('Artist.id, Artist.year_born, Artist.year_died, Artist.country_born'),
        'limit' => 50,
        'order' => array('Artist.id' => 'desc'),
        'joins' => array(
                array('table' => 'artist_forenames', 'alias' => 'ArtistForename', 'type' `=>     'inner', 'conditions' => array('Artist.id = ArtistForename.artist_id')),`
                array('table' => 'artist_surnames', 'alias' => 'ArtistSurname', 'type' => 'INNER', 'conditions' => array('Artist.id = ArtistSurname.artist_id'))),
        'recursive' => -1
    );

But I'm not sure how to make this work. What I want basically is to join the artist_surnames and artist_fornames and ignore two tables called artist.actives and artist_monogram that both together slows down the query with 4000ms.

My second question here is why do these two joins slows it down so much? I did a test-view called artists/all where I'm listing 100 artists and there my query with all the joins is done in around 40-50ms.

Answer 1

TLDR:

1. Set $recursive to -1
2. Use CakePHP's Containable Behavior to only retrieve the associated data you want

Example of code:

//wherever you set this variable
$paginate = array(
    'recursive' => -1,
    'limit' => 50,
    'order' => array('Artist.id' => 'desc'),
    'contain' => array(
        'ArtistSurname',
        'ArtistForName'
    )
);

Remember to look through the documentation for Containable - it explains how you set your Model(s) to $actsAs Containable, set recursive to -1...etc etc.

I suggest actually setting public $recursive=-1' and public $actsAs =array('Containable'); both in your AppModel - that way ALL models are set up and ready to go with Containable whenever you want. Also, anything higher than -1 for recursive is bad IMHO - Containable is so much better in every respect.