sql order by - Need help optimizing mysql query to get it to sort quickly by index -


someone helped me come query still slow; order slowing down , dont think using index i'm hoping can fix me :d yes read manual page can't understand it.

query:

 explain select u.id, u.url, u.title, u.numsaves  urls u  join tags t on t.url_id = u.id  , t.tag = 'osx'  order u.numsaves desc  limit 20    showing rows 20 - 19 ( 20 total, query took 1.5395 sec) [numsaves: 6130 - 2107]    id     select_type     table   type    possible_keys   key     key_len     ref     rows     1  simple  t   ref     tag_id  tag_id  767     const   49432   using where; using index; using temporary; using filesort  1  simple  u   eq_ref  primary,id_numsaves_ix  primary     4   jcooper_whatrethebest_urls.t.url_id     1

database:

 create table `urls` (  `id` int(11) not null auto_increment,  `url` text not null,  `domain` text,  `title` text not null,  `description` text,  `numsaves` int(11) not null,  `firstsaved` varchar(256) default null,  `md5` varchar(255) not null default '',  primary key (`id`),  unique key `md5` (`md5`),  key `id_numsaves_ix` (`id`,`numsaves`)  ) engine=innodb auto_increment=2958560 default charset=utf8   create table `tags` (  `url_id` int(11) default null,  `hash` varchar(255) not null,  `tag` varchar(255) not null,  unique key `tag_id` (`tag`,`url_id`)  ) engine=innodb default charset=utf8

i think main problem query choice of indexes.

1) tags has compound unique key on tag , url_id no primary key.

if nothing else, should make primary - may bit performance. also, might want take close if varchar(255) necessary tags. makes index quite big.

2) add separate index on numsaves since you're ordering that. compound index on id , numsaves not going here.

3) explain says have 49432 rows in tags match "osx". quite redundant. may want split tags table two, 1 containing text while other contains n:m link urls.


Comments

Post a Comment

Popular posts from this blog

c# - Send Image in Json : 400 Bad request -

from android trying send image class of data, iis webservice. (c#) the problem 400 bad request . the image being encoded base64 . , placed json rest of class elements. my guess base64 not valid inside json. server not understand it. if set string "" , post accepted fine. so question is, how make base64 valid within json array? ( tried url.encode no success). or how should send image android webservice? gson gson = new gson(); string json = gson.tojson(record); // record has param { string base64photo } how big image? i'm pretty sure surpassing iis json size limit (default 4 mb). check http://geekswithblogs.net/frankw/archive/2008/08/05/how-to-configure-maxjsonlength-in-asp.net-ajax-applications.aspx or http://www.webtrenches.com/post.cfm/iis7-file-upload-size-limits good luck!
Read more

javascript - addthis share facebook and google+ url -

i attempting use addthis create simple url shares whichever current page user on when click share button. of right now, have twitter working. when user clicks, able see url populated in tweet box. when user clicks facebook or google+, url doesn't seem show in share box. <ul class="addthis_toolbox addthis_default_style "> <li><a class="addthis_button_facebook"><img src="/img/facebook-social.png" width="20" height="20" border="0" alt="share" /></a></li> <li><a class="addthis_button_twitter"><img src="/img/twitter-social.png" width="20" height="20" border="0" alt="share" /></a></li> <li><a class="addthis_button_google_plusone_share"><img src="/img/google-social.png" width="20" height="20" border="0" alt="s...
Read more

ios - Show keyboard with UITextField in the input accessory view -

i have buttona , when buttona clicked, want keyboard show uitextfield in inputaccessoryview. there way have keyboard show manually , set inputaccessoryview without having uitextfield initially? thanks. you cannot summon keyboard without object can become first responder. there 2 ways work around: subclass uiview , implement uikeyinput protocol in it. example: in .h file: @interface inputobject : uiview<uikeyinput> @property (nonatomic, copy) nsstring *text; @property (nonatomic, strong) uiview *inputaccessoryview; // must override inputaccessoryview , since it's readonly default @end in .m file, implement protocol: - (bool)canbecomefirstresponder { return yes; } - (bool)hastext { return [self.text length]; } - (void)inserttext:(nsstring *)text { nsstring *selftext = self.text ? self.text : @""; self.text = [selftext stringbyappendingstring:text]; [[nsnotificationcenter defaultcenter] postnotificationname:kinputobjec...
Read more