Query ignoring index when using 'OR' ?

[bachx]

I have this query:

 

SELECT f.uid, f.status FROM friends f
LEFT JOIN f.friends_invitations fi ON f.fid = fi.fid
WHERE (f.uid = 2 OR fi.uid = 2)

 

The above query is looping on all 'friends' records, and ignoring the f.uid index, despite fid and uid being indexes in BOTH tables.

 

Any ideas or suggestions?

[WebStyles]

try this:

SELECT friends.uid, friends.status FROM friends 
LEFT JOIN friends_invitations ON friends.uid = friends_invitations.fid
WHERE (friends.uid = 2 OR friends_invitations.fid = 2)

 

can you post your database structures with field names please?

[bachx]

Sure thing:

 

Friends Table:

- fid (Auto-increment/Primary key)

- uid (Unique index)

 

Friends Invitations Table:

- id  (Auto-increment/Primary key)

- fid (Index)

- uid (Index)

 

This is a simplified structure so it might not make much sense, but basically I want to retrieve a user that exists either in Friends or Friends Invitations table by the 'uid' field, both tables are connected/joined by the 'fid' field.

[bachx]

Any ideas guys? It's kinda urgent

[phpSensei]

do a mysql_error() and a mysql_num_rows, and try putting the values 2 in quotation marks.

[bachx]

No errors, mysql_num_rows returns 1, adding quotes changes nothing.

 

The query in my topic works fine, however it scans all rows of the friends table in order to retrieve a single row, despite 'uid' being an index.

It's definitely caused by the 'OR', as when I remove it the query executes faster, so I'm looking for a more efficient way to implement it.

[bachx]

Explain for the query:

 

select_typetabletypepossible_keyskeyrefrowsExtra

SIMPLEfALLuid(NULL)(NULL)500-

SIMPLEfireffidfidf.fid12Using where

 

[WebStyles]

How can we help you if you don't post accurate information?

where does the filed `status` come from?

 

please post the complete database structure, with all the accurate names, and tell us EXACTLY what you are trying to do... you said:

 

(...) but basically I want to retrieve a user that exists either in Friends or Friends Invitations table by the 'uid' field, both tables are connected/joined by the 'fid' field.

 

What does this mean?

1. you want to select all `uid` values from both tables

2. only the ones that exist in both tables

3. only the ones from table Friends that also exist in friends_invitations

 

??? You are not specific enough.

 

[bachx]

Don't mind 'status', consider it not there, other fields are really irrelevant.

 

What I'm trying to do?

I have a given User ID (uid), for example '2'. I want to get all records that exists in both tables for that user. However User '2' might have a record in Friends but not in Friends Invitations, and vice versa, hence I used 'OR' in my original query.

 

My original query is 100% correct, however performance-wise it's bad, because it's scan all records of Friends table in order to get to User 2, even though 'uid' is an index.

 

Hope I was clear enough.