How can I make this SQL query more efficient? PHP.
- by Alan Grant
Hi all,
I have a system whereby a user can view categories that they've subscribed to individually, and also those that are available in the region they belong in by default.
So, the tables are as follows:
Categories
UsersCategories
RegionsCategories
I'm querying the db for all the categories within their region, and also all the individual categories that they've subscribed to.
My query is as follows:
Select * FROM (categories c)
LEFT JOIN users_categories uc on uc.category_id = c.id
LEFT JOIN regions_categories rc on rc.category_id = c.id
WHERE (rc.region_id = ? OR uc.user_id = ?)
At least I believe that's the query, I'm creating it using Cake's ORM layer, so the exact one is:
$conditions = array(
array( "OR" => array (
'RegionsCategories.region_id' => $region_id,
'UsersCategories.user_id' => $user_id
)
));
$this->find('all', $conditions);
This turns out to be incredibly slow (sometimes around 20 seconds or so. Each table has around 5,000 rows). Is my design at fault here?
How can I retrieve both the users' individual categories and those within their region all in one query without it taking ages?
Thanks!
