tobeyt23 Posted June 2, 2011 Share Posted June 2, 2011 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' Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/ Share on other sites More sharing options...
mikosiko Posted June 2, 2011 Share Posted June 2, 2011 Huh?... don't follow.... performance issue?... what the EXPLAIN plan say?... indexes involved? Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224203 Share on other sites More sharing options...
tobeyt23 Posted June 2, 2011 Author Share Posted June 2, 2011 Basically this take 15 secs to run no indexes Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224206 Share on other sites More sharing options...
mikosiko Posted June 2, 2011 Share Posted June 2, 2011 and?... what the EXPLAIN plan say? Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224221 Share on other sites More sharing options...
tobeyt23 Posted June 2, 2011 Author Share Posted June 2, 2011 what i am asking is can this be turned into a union statement that will make it process faster? Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224225 Share on other sites More sharing options...
mikosiko Posted June 2, 2011 Share Posted June 2, 2011 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) Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224236 Share on other sites More sharing options...
tobeyt23 Posted June 2, 2011 Author Share Posted June 2, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224238 Share on other sites More sharing options...
mikosiko Posted June 2, 2011 Share Posted June 2, 2011 ok... you want the short answer... NO Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224243 Share on other sites More sharing options...
tobeyt23 Posted June 2, 2011 Author Share Posted June 2, 2011 thanks Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224246 Share on other sites More sharing options...
tobeyt23 Posted June 2, 2011 Author Share Posted June 2, 2011 So what could i do to speed this query up? Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224298 Share on other sites More sharing options...
fenway Posted June 2, 2011 Share Posted June 2, 2011 Again, post the EXPLAIN output. Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224408 Share on other sites More sharing options...
tobeyt23 Posted June 2, 2011 Author Share Posted June 2, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224424 Share on other sites More sharing options...
mikosiko Posted June 3, 2011 Share Posted June 3, 2011 seems that you don't have any index in your table users_profile... you should have at least one on the column user_id Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224679 Share on other sites More sharing options...
tobeyt23 Posted June 3, 2011 Author Share Posted June 3, 2011 Wow that made a huge difference Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224695 Share on other sites More sharing options...
tobeyt23 Posted June 3, 2011 Author Share Posted June 3, 2011 thanks went from 15secs to just over 1sec Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1224732 Share on other sites More sharing options...
fenway Posted June 27, 2011 Share Posted June 27, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/238208-query-preformance/#findComment-1235283 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.