Query Preformance

[tobeyt23]

Anyway I can speed this up possible with a UNION, i tried but seem to be missing something?

 

SELECT 
xfiles.id, xfiles.user_id, xfiles.action, xfiles.other_action, xfiles.document, xfiles.start_date, xfiles.end_date, user_profiles.fname, user_profiles.mname, user_profiles.lname, licenses.number, licenses.state, asc_records.fname, asc_records.mname, asc_records.lname, asc_records.st_abbr, asc_records.lic_number, asc_records.status 
FROM xfiles 
LEFT JOIN user_profiles ON xfiles.user_id = user_profiles.user_id LEFT JOIN licenses ON xfiles.user_id = licenses.user_id AND xfiles.license_id = licenses.id
LEFT JOIN asc_records ON xfiles.asc_id = asc_records.id 
WHERE xfiles.deleted=0 AND licenses.state = 'FL' OR asc_records.st_abbr = 'FL'

[mikosiko]

Huh?... don't follow....

performance issue?... what the EXPLAIN plan say?... indexes involved?

[tobeyt23]

Basically this take 15 secs to run no indexes

[mikosiko]

and?... what the EXPLAIN plan say?

[tobeyt23]

what i am asking is can this be turned into a union statement that will make it process faster?

[mikosiko]

that was covered in the first part of my first answer

 

UNION and JOIN are 2 different animals... use one or other is going to depend on your objectives (and we don't know them yet)

[tobeyt23]

i want to pull the same data just quicker that was my question to begin with only asked if it could be done with a union

[mikosiko]

ok... you want the short answer... NO

[tobeyt23]

thanks

[tobeyt23]

So what could i do to speed this query up?

[fenway]

Again, post the EXPLAIN output.

[tobeyt23]

Here you go:

 

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,xfiles,ALL,,,,,3653,

1,SIMPLE,user_profiles,ALL,,,,,1226,

1,SIMPLE,licenses,eq_ref,PRIMARY,PRIMARY,4,cb_overall.xfiles.license_id,1,

1,SIMPLE,asc_records,eq_ref,PRIMARY,PRIMARY,4,cb_overall.xfiles.asc_id,1,Using where

 

[mikosiko]

seems that you don't have any index in your table users_profile... you should have at least one on the column user_id

 

 

[tobeyt23]

Wow that made a huge difference

[tobeyt23]

thanks went from 15secs to just over 1sec

[fenway]

You're mixing AND and OR -- probably not how you expect -- you're missing parentheses.  IN() solves this nasty oversight.

 

Also, assuming your 'deleted' column is rare, an index on that column might help too, since now EXPLAIN shows 'all' for the `xfiles` table.