php - How to optimize query with table scans? -


this far slowest query in web application.

select prof.user_id userid, prof.first_name first, prof.last_name last, prof.birthdate, prof.class_string classes, prof.city, prof.country, prof.state, prof.images, prof.videos, u.username, u.avatar, (select count(*) company_member_sponsorship member_id = prof.user_id , status = 'sponsored') sponsor_count, (select count(*) member_schedules user_id = prof.user_id) sched_count member_profiles prof left join users u on u.id = prof.user_id order ( prof.images + prof.videos * 5 + ( case when prof.expire_date > :time 50 else 0 end ) + sponsor_count * 20 + sched_count * 4 ) desc, prof.last_name asc limit :start, :records

everything else on site takes less second load lots of queries happening on levels. 1 takes 3-4 seconds.

it's table scans causing slowdown. can understand why; first table has 50,000+ rows, second 160,000+ rows.

is there way can optimize query make go faster?

if worse comes worst can go through code , maintain tally sponsorships , events in profile table images , videos though i'd avoid it.

edit: added results of explain on query.

id select_type table type possible_keys key key_len ref rows 1 primary prof null null null null 44377 using temporary; using filesort 1 primary u eq_ref primary primary 3 mxsponsor.prof.user_id 1 3 dependent subquery member_schedules ref user_id user_id 3 mxsponsor.prof.user_id 6 using index 2 dependent subquery company_member_sponsorship ref member_id member_id 3 mxsponsor.prof.user_id 2 using where; using index

edit2:

i ended dealing problem maintaining count in member profile. wherever sponsorships/events added/deleted invoke function scans sponsorship/events table , updates count member. there might still way optimize query this, we're publishing site rather i'm going quick , dirty solution now.

not guaranteed work, try using join , group by rather inner selects:

select prof.user_id userid, prof.first_name first, prof.last_name last, prof.birthdate, prof.class_string classes, prof.city, prof.country, prof.state, prof.images, prof.videos, u.username, u.avatar, count(cms.id) sponsor_count, count(ms.id) sched_count member_profiles prof left join users u on u.id = prof.user_id left join company_member_sponsorship cms on cms.member_id = prof.user_id , cms.status = 'sponsored' left join member_schedules ms on ms.user_id = prof.user_id group u.id order ( prof.images + prof.videos * 5 + ( case when prof.expire_date > :time 50 else 0 end ) + sponsor_count * 20 + sched_count * 4 ) desc, prof.last_name asc limit :start, :records

if that's not better, explain of query help.


Comments

Post a Comment

Popular posts from this blog

javascript - backbone.js Collection.add() doesn't `construct` (`initialize`) an object -

<!doctype html public "-//w3c//dtd xhtml 1.0 strict//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-strict.dtd"> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script> <script src="http://documentcloud.github.com/underscore/underscore-min.js"></script> <script src="http://documentcloud.github.com/backbone/backbone-min.js"></script> </head> <body> <button id="cmd_create_event" name="cmd_create_event" type="button">create new `event`</button> <script type="text/javascript"> var eventmodel = backbone.model.extend({ initialize : function() { console.log("`event` initialized. id: " + this.cid); this.bind("change:status", function() { console.log(this.get("status") + " value `status`"); }); this.bind("error", function(model, e...
Read more

php - Get uncommon values from two or more arrays -

is there function in php give array of uncommon values 2 or more arrays? for example: $array1 = array( "green", "red", "blue"); $array2 = array( "green", "yellow", "red"); .... $result = function_needed($array1, $array2,...); print_r($result); should give output: array("blue", "yellow", ...); use array_diff , array_merge : $result = array_merge(array_diff($array1, $array2), array_diff($array2, $array1)); here's demo. for multiple arrays, combine callback , array_reduce : function unique(&$a, $b) { return $a ? array_merge(array_diff($a, $b), array_diff($b, $a)) : $b; } $arrays = array( array('green', 'red', 'blue'), array('green', 'yellow', 'red') ); $result = array_reduce($arrays, 'unique'); and here's demo of that.
Read more

Adding duplicate array rows in Php -

i create function in php adds rows of array shared column value. so input. $test = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); and output. $testduplicatefree = array( array("c", 7, 9), array("test", 5, 6) ) ); i'm thinking function combine_duplicates($array,$col){ ... ... return $duplicatefreearray; } where $col duplicate free array. so, in case, combine_duplicates($test,0); would me desired output. this. <?php function combine_duplicates($array,$col) { $index = array(); foreach ($array $row) { $key = $row[$col]; if (!isset($index[$key])) { $index[$key] = $row; } else { ($i = 0; $i < count($row); ++$i) { if ($i != $col) { $index[$key][$i] += $row[$i]; } } } } return array_values($index); } $array = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); print_r(combine_duplicates($array, 0)); try here: http://codepad.org/mdhesqhi
Read more