CODEWITHSUNDEEP
mysqlcakephpindexing
Patcouch22
Patcouch22

Reputation: 912

Speeding up a mysql query?

The query uses cakephp to make the call to the database, here is the query:

find('all', array(
    'conditions' => array(          
    'OR' => array(
        'PdfFolder.is_global' => true,
        'PdfFoldersUser.user_id' => $this->Auth->user('id'))
    ),
    'recursive' => 0,
    'order' => array('PdfFolder.is_global DESC', 'PdfFolder.name')

The pdffolder table has about 2100 however the relationship table that contains pdf_folder_id and user_id contains nearly 30000 records. This query at times will take up to 35 seconds to run at certain times. I did not set up the code, and I am not database expert, this just got dropped on me as a developer.

It looks like id for each table is set up as a primary key, how that is all the indexes that I see.

Thanks for any advice.

EDIT

Table Structure

users

| id (index) | role_id | username | password | email |

pdf_folders

| id (index) | name | date_created | is_global(bool) |

pdf_folders_users

| id (index) | folder_id | user_id (now an index) |

pdfs

| id (index) | parent_id | pdf_folder_id | ...more file information

Upvotes: 0

Views: 513

Answers (2)

Devario Johnson
Devario Johnson

Reputation: 11

@patcouch22

to see your sql in the view 

<?php echo $this->element('sql_dump'); ?>

to see it in the controller

$this->render('sql');

Upvotes: 1

user1211655
user1211655

Reputation:

Start by adding an index on user_id field -

CREATE INDEX FK_user_id ON PdfFoldersUser (user_id)

It would help if you posted the full table structure for each of the relevant tables.

Upvotes: 3

Related Questions