Searching for a user across two tables taking 23 seconds

[Tom Beharrell]

I am using a database with two tables; one for users and one for their profile. There is usually one record in each table for every user. There is a primary key, userId, in both tables.

users:
userId primary key
username
status

userProfileData:
userId primary key
firstName
lastName
email

There is a user search which executes the following query:

SELECT SQL_CALC_FOUND_ROWS users.userId, users.username, users.status, userProfileData.email from users
left join userProfileData on users.userId=userProfileData.userId where status like '%active%' and (users.username like ? or email like ? or firstName like ? or lastName like ?)

I am new to this so have been experimenting with EXPLAIN, and the above query doesn't seem to use any keys, all returning NULL for key. It always takes about 23 seconds to execute, with 3000 records in both tables.

Changing the left join to join, or removing the users.username from the where clause, seems to 'fix' the problem, and the keys seem to be used (?) and the query executes in fractions of a second.

Is there a way of optimizing the query but preserving the ability to search across both tables, and returning rows even if the user is in 'users' but not 'userProfileData'?



Edited 1 time(s). Last edit at 11/05/2007 04:27PM by Tom Beharrell.