Schneimi’s Dev Weblog


Fast random find with CakePHP

Posted in CakePHP by schneimi on August 28, 2009
Tags: , , ,

I want to share my experience with random finds in CakePHP because they can be very slow done the wrong way.

In this example we have the model Audioplaylist which hasAndBelongsToMany Audio and we want to get a random playlist of Audios. We assume the table for Audio is pretty large and we also need the data of other related models as result, let’s say the owner’s username and the title of the related album. All models are set to $recursive = -1 and the ContainableBehavior is used to contain the data.

  • The first approach would be to make one find and get all the data at once.
  • $audios = $this->Audioplaylist->Audio->find('all', array('contain' => array('User.name',
                                                                                'Album.title'),
                                                             'order' => 'RAND()',
                                                             'limit' => $count));
    

    Happy waiting!

    You have to know that DB queries ordered by RAND() get slower the more fields are selected, and in this case we even select all fields of the Audio model.

  • Knowing that, we can improve the find in using the fields parameter to also contain the fields of the Audio model.
  • $audios = $this->Audioplaylist->Audio->find('all', array('contain' => array('User.name',
                                                                                'Album.title'),
                                                             'fields' => 'id',
                                                             'order' => 'RAND()',
                                                             'limit' => $count));
    

    This is already a lot faster than before, but still not very applicable because it still slows down with the amount of related data.

  • So let’s try another approach that looks more circumstantial but avoids the problem seen before. The idea is to make two finds, the first one just finds some random Audio Ids and the second will then use the Ids to catch all the related data.
  • $randomAudioIds = $this->Audioplaylist->Audio->find('list', array('fields' => 'id',
                                                                      'order' => 'RAND()',
                                                                      'limit' => $count));
    
    $audios = $this->Audioplaylist->Audio->find('all', array('contain' => array('User.name',
                                                                                'Album.title'),
                                                             'conditions' => array('Audio.id' => $randomAudioIds),
                                                             'order' => 'RAND()'));
    

    Sometimes more is less, especially if you need alot of data from related models, you will appreciate the performance of these two queries.

    Advertisements

    2 Responses to 'Fast random find with CakePHP'

    Subscribe to comments with RSS or TrackBack to 'Fast random find with CakePHP'.

    1. Pawan Rote said,

      how to get the same random data in desc order

      • schneimi said,

        Hi, in the second find you could try something like:

        ‘order’ => ‘title DESC, RAND()’,

        I am not sure why I used the RAND() in the second find as well, this should be not necessary because of the first find randomness.


    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: