How can I make this SQL query more efficient? PHP.

Question

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!

Answer 1

Make sure that the categories.id, users_categories.category_id, users_categories.user_id, regions_categories.category_id and regions_categories.region_id columns are all indexed.

Answer 2

Assuming jmans's suggestions were already implemented;

IIRC MySQL queries can only use one index at a time, and so OR can be expensive and it can be faster to split it into two queries and UNION them, allowing each subquery to use a different index

Answer 3

Try this one:

select * from
(
 select category_id from users_categories where user_id = ?
 union
 select category_id from region_categories where region_id = ?
) as q
inner join categories c on c.id = q.category_id

I guess it does the same job, but does not uses OR (which is a thing to avoid with most RDBMSs) - hence letting the server use indexes in more effective manner. Of cause, as was already noted by jmans, you should consider indexing your data.