Optimizing mysql group by with large linking table -

-

i've been reading lot on , still 30+ seconds per query, while i'm should work lot faster.

the problem this:

having large linking table (40 millions rows, data consisting of 650mb , index representing 1.8 gb) defined follows:

create table if not exists `glossary_entry_wordlist_1` (   `idterm` mediumint(8) unsigned not null,   `idkeyword` mediumint(8) unsigned not null,   `termlength` smallint(6) not null,   `termnumberwords` tinyint(4) not null,   `termtransliteralrfc` mediumint(9) not null,   `keywordlength` tinyint(3) unsigned not null,   `termlanguage` tinyint(4) not null,   primary key (`idkeyword`,`idterm`),   key `termtransliteralrfc` (`termtransliteralrfc`),   key `termlength` (`termlength`),   key `secondprimary` (`idterm`,`idkeyword`) ) engine=myisam default charset=latin1 collate=latin1_general_ci

and small temporary table defined follows:

create temporary table if not exists `foundids` (   `searchid` int(11) not null,   `searchedkeywordid` int(11) not null,   `similarkeywordid` mediumint(8) unsigned not null,   `partsmatched` tinyint(4) not null,   `sumsimliarparts` int(11) not null,   `keywordlength` int(11) not null,   `fuzzymark` float not null,   `keyworddjb2` bigint(20) not null,   `smallkeyword` tinyint(4) not null,   primary key (`similarkeywordid`),   key `searchid` (`searchid`),   key `searchedkeywordid` (`searchedkeywordid`),   key `partsmatched` (`partsmatched`),   key `keywordlength` (`keywordlength`),   key `smallkeyword` (`smallkeyword`),   key `keyworddjb2` (`keyworddjb2`) ) engine=memory default charset=utf8 collate=utf8_unicode_ci;

i need retrieve glossary_entry_wordlist_1 idterm associated @ least 50% (or other percent) of idkeyword in table foundids.

practically, need find sentences contain x words.

for use query (please mind conditional data here example):

select glossary_entry_wordlist_1.idterm,  count( glossary_entry_wordlist_1.idkeyword) termsmatched ,  sum(foundids.keywordlength) sumtermlength,  sum(foundids.fuzzymark)/100 sumfuzzy,  sum(foundids.partsmatched*foundids.keywordlength)/100 sumlengthsimilar,  foundids.searchid foundids inner join glossary_entry_wordlist_1 on glossary_entry_wordlist_1.idkeyword = foundids.similarkeywordid foundids.searchid = '17559' , glossary_entry_wordlist_1.termtransliteralrfc >= '824.4' , glossary_entry_wordlist_1.termlength>= '8.55' , glossary_entry_wordlist_1.termlength<= '18' , foundids.smallkeyword = 0 group glossary_entry_wordlist_1.idterm having count( glossary_entry_wordlist_1.idkeyword)>'2' order null

and explain:

id  select_type  table  type  possible_keys  key  key_len  ref  rows    1 simple foundids ref primary,searchid,smallkeyword searchid 4 const 8 using where; using temporary  1 simple glossary_entry_wordlist_1 ref primary,termtransliteralrfc,termlength primary 3 foundids.similarkeywordid 146 using

the engine behavior this: - smaller word length (1-2 letters) longer query response (obviously have lot more associations) - more words in searched table (foundids), longer query

any ideas on how improve query response?

thank you,


Comments

Post a Comment

Popular posts from this blog

java - How to Configure JAXRS and Spring With Annotations -

-
i working on little spring web service project. here example route: package com.example.api; import javax.ws.rs.get; import javax.ws.rs.path; import javax.ws.rs.produces; import javax.ws.rs.pathparam; import javax.ws.rs.ext.provider; import javax.ws.rs.core.mediatype; @provider public class webserviceroutes { @get @path("/hello") public string health() { return "hello"; } } then in spring configuration class: package com.example.api; import org.springframework.context.annotation.bean; import org.springframework.context.annotation.configuration; @configuration public class springcontextconfiguration { @bean( destroymethod = "shutdown" ) public springbus cxf() { return new springbus(); } @bean public webserviceroutes webserviceroutes() { return new webserviceroutes(); } } and in web.xml: <?xml version="1.0" encoding="utf-8"?> <web-app version=...
Read more

visual studio - TFS will not accept changes I've made to a Java project -

-
i using tfs's team explorer manage visual studio projects. recently, i've created new java project (not in visual studio) manually added tfs using source control control explorer in visual studio. after added java project tfs, made changes , bug fixes. then, went visual studio , opened source control explorer check in changes, tfs thinks no changes made. it seems needed check out project before making changes. guess erroneously expected tfs track automatically, okay. so, using source control explorer in tfs, checked out project, , tried check in pending changes. when tried check in, got following message: all of changes either unmodified files or locks. changes have been undone server. is there way convince server indeed project has changed? how can check in changes have made? thank help.
Read more

php - Create image in codeigniter on the fly -

-
i want create image in codeigniter in simple php using gd functions. have tried following method in controller function image(){ $my_img = imagecreate( 200, 80 ); $background = imagecolorallocate( $my_img, 0, 0, 255 ); $text_colour = imagecolorallocate( $my_img, 255, 255, 0 ); $line_colour = imagecolorallocate( $my_img, 128, 255, 0 ); imagestring( $my_img, 4, 30, 25, "thesitewizard.com", $text_colour ); imagesetthickness ( $my_img, 5 ); imageline( $my_img, 30, 45, 165, 45, $line_colour ); //$this->output->set_content_type('image/png'); header( "content-type: image/png" ); imagepng( $my_img ); imagecolordeallocate( $line_color ); imagecolordeallocate( $text_color ); imagecolordeallocate( $background ); imagedestroy( $my_img ); } if write code in simple php can see in url http://www.domain.com/image.php if run in codeignite...
Read more