i've got big query contain same types need feed list in app. problem query isn't fast. i'm thinking if limit each individual union might speed bit i'm not sure.
so question how optimize query execute faster?
select distinct alert_id, uniquekey, type, user_id, date_added ( ( select r.alert_id, r.alert_id uniquekey, 'reply' `type`, r.user_id, r.date_added `reply` r left join `alerts` on r.alert_id = a.alert_content_id r.user_id = :id , a.hide = '0' order date_added desc ) union ( select r.alert_id, r.alert_id uniquekey, 'replyfromfollowing' `type`, r.user_id, r.date_added `reply` r left join `alerts` on r.alert_id = a.alert_content_id r.user_id in( '$followingstring' ) , a.hide = '0' order date_added desc ) union ( select i.alert_id, i.alert_id uniquekey, 'liked' `type`, i.user_id, i.date_added `interactions` left join `alerts` on i.alert_id = a.alert_content_id i.user_id = :id , a.hide = '0' group alert_id order date_added desc ) union ( select i.alert_id, i.alert_id uniquekey, 'likedfromfollowing' `type`, i.user_id, i.date_added `interactions` left join `alerts` on i.alert_id = a.alert_content_id i.user_id in ( '$followingstring' ) , a.hide = '0' group alert_id order date_added desc ) union ( select alerts alert_id, alert_content_id uniquekey, 'following' `type`, user_id, date_added alerts left join `alerts_content` ac on ac.id = a.alert_content_id a.user_id in ( '$followingstring' ) , ac.anoniem = '0' , a.hide = '0' group alert_id order date_added desc ) ) joined group uniquekey order date_added desc limit ".(int)$start.",20" table structures
reply table structure: id user_id alert_id description reply_on_alert reply_on_reply date_added interaction table structure: id alert_id action_id reply_id user_id date_added alerts table structure(yes know big mistake naming `id` : `alerts`): alerts title alert_content_id user_id cat lat lon state hide date_added alerts_content table structure: id alert_id description img results of query:
array ( [0] => array ( [alert_id] => 173404 [uniquekey] => 173404 [type] => reply [user_id] => 2 [date_added] => 2015-06-01 16:34:16 ) [1] => array ( [alert_id] => 172174 [uniquekey] => 172174 [type] => replyfromfollowing [user_id] => 1380 [date_added] => 2015-06-01 16:01:04 ) [2] => array ( [alert_id] => 171772 [uniquekey] => 171772 [type] => liked [user_id] => 2 [date_added] => 2015-06-01 15:58:44 ) [3] => array ( [alert_id] => 149423 [uniquekey] => 149423 [type] => reply [user_id] => 2 [date_added] => 2015-06-01 15:25:56 ) [4] => array ( [alert_id] => 164742 [uniquekey] => 164742 [type] => reply [user_id] => 2 [date_added] => 2015-05-12 09:46:39 ) [5] => array ( [alert_id] => 163344 [uniquekey] => 163344 [type] => replyfromfollowing [user_id] => 3 [date_added] => 2015-05-12 09:44:46 ) [6] => array ( [alert_id] => 164205 [uniquekey] => 164205 [type] => liked [user_id] => 2 [date_added] => 2015-05-11 11:06:39 ) [7] => array ( [alert_id] => 160890 [uniquekey] => 160890 [type] => replyfromfollowing [user_id] => 1380 [date_added] => 2015-05-08 14:29:34 ) [8] => array ( [alert_id] => 163002 [uniquekey] => 163002 [type] => replyfromfollowing [user_id] => 1380 [date_added] => 2015-05-08 13:31:12 ) [9] => array ( [alert_id] => 159123 [uniquekey] => 159123 [type] => replyfromfollowing [user_id] => 48 [date_added] => 2015-04-30 15:10:32 ) [10] => array ( [alert_id] => 150546 [uniquekey] => 150546 [type] => replyfromfollowing [user_id] => 16 [date_added] => 2015-04-21 21:52:49 ) [11] => array ( [alert_id] => 149497 [uniquekey] => 149497 [type] => reply [user_id] => 2 [date_added] => 2015-04-10 15:19:06 ) [12] => array ( [alert_id] => 141078 [uniquekey] => 141078 [type] => liked [user_id] => 2 [date_added] => 2015-04-10 15:15:32 ) [13] => array ( [alert_id] => 125466 [uniquekey] => 125466 [type] => replyfromfollowing [user_id] => 3 [date_added] => 2015-04-09 00:15:22 ) [14] => array ( [alert_id] => 134592 [uniquekey] => 134592 [type] => replyfromfollowing [user_id] => 3 [date_added] => 2015-04-09 00:11:04 ) [15] => array ( [alert_id] => 124194 [uniquekey] => 124194 [type] => likedfromfollowing [user_id] => 3 [date_added] => 2015-04-09 00:08:35 ) [16] => array ( [alert_id] => 128645 [uniquekey] => 128645 [type] => likedfromfollowing [user_id] => 3 [date_added] => 2015-04-09 00:07:29 ) [17] => array ( [alert_id] => 144867 [uniquekey] => 144867 [type] => replyfromfollowing [user_id] => 3 [date_added] => 2015-04-06 13:59:19 ) [18] => array ( [alert_id] => 133355 [uniquekey] => 133355 [type] => liked [user_id] => 2 [date_added] => 2015-03-31 16:16:15 ) [19] => array ( [alert_id] => 141075 [uniquekey] => 141075 [type] => liked [user_id] => 2 [date_added] => 2015-03-30 15:17:01 ) )
some possibilities, in no particular order:
optimization #1:
use limit in subqueries, too. however, since using offset, may not obvious how it.
before query, calculate $start+20 , put into, say, $limit. use limit $limit inner queries. no, don't use offset on them. guarantees enough rows each query satisfy outer offset $start limit 20.
optimization #2:
restructure tables don't need join table (alerts) find out whether show record. is, having hide prevents number of potential optimizations. before advising further, need understand need left. there rows in reply, etc, not in alerts? if not, rid of left , searching alerts theoffsetandlimit`, joining 4 other tables.
optimization #3:
restructure data there one core table, alerts , 4 other tables hanging off it. sure have (all?) of fields needed this query in new table.
optimization #4:
the current structure requires full scan of each of 4 tables before thinking offset , limit. smells "pagination"; it? optimizing "pagination", goals avoid table scan , offset; instead remember "left off" query can
where ... , x < $left_off order x desc limit 20 this should make possible read 20 rows, not entire table(s). make query much faster, later pages. (a bigger offset costs more time`)
i discuss pagination optimization in my blog.
Comments
Post a Comment