Sorry, I just realized that my logic for the query is flawed anyway. It won't
return the proper data set I'm after. I'll have to go back to looking at
the lists table first.
I still guess knowing why the query below isn't as quick as expected
could be useful though.
At 01:32 PM 5/20/2004, Doug Y wrote:
>Hello,
> For the following query:
>
>SELECT *
> FROM permissions p
> INNER JOIN users u
> ON u.id = p.id
> LEFT JOIN user_list ul1
> ON ul1.id = u.id
> AND ul1.type = '1'
> LEFT JOIN user_list ul2
> ON ul2.id = u.id
> AND ul2.type = '2'
> INNER JOIN lists l
> ON ( l.list_id1 = ul1.list_id1 AND l.list_id2 = ul1.list_id2 )
> OR
> ( l.list_id1 = ul2.list_id1 AND l.list_id2 = ul2.list_id2 )
> WHERE
> p.code = '123456' AND p.type = 'User'
>
>(lists table has ~ 500k records, users ~ 100k, permissions ~ 60k,
>user_list ~ 530k)
>
>lists can be associated with 2 users via the user_list table, and are
>designated by the 1 or 2, can have a user with a 1, a user with a 2 or one
>of each.
>
>I'm getting really poor performance... about 60 seconds. Explain (see
>below) is showing its trying to use the pkey (list_id1,list_id2) on the
>list table, but not showing an index condition.
>
>If I get rid of the OR, and only at one of the conditions it returns very
>quickly and properly set the index condition. I can't use a union because
>I would end up with duplicate rows for those that have both ul type 1 and 2
>
>I actually started off trying the query by looking at lists first, but
>performance was awful since I can't narrow down the records like I can
>with permissions.
>
>I know the tables aren't really set up ideally, and I actually have to
>join a few more tables to the lists table after the fact, but want to get
>the base running as efficient as possible first.
>
>Is there any way to get this query to use the correct index condition so
>that it runs in a reasonable amount of time?
>
>Thanks!
- cut explains off -