mysql: slow query on indexed field -

-

the orders table has 2m records. there ~900k unique ship-to-ids.

there index on ship_to_id ( field isint(8)).

the query below takes 10mn complete. i've run processlist has command = query , state = sending data.

when run explain, existing index used, , possible_keys null.

is there should speed query up? thanks.

select    ship_to_id customer_id  orders  group ship_to_id  having sum( price_after_discount ) > 0

does not have useful index. try adding index on price_after_discount, , add condition this:

where price_after_discount > 0

to minimize number of rows need sum can discard 0.

also try running "top" command , @ io "wait" column while query running. if high, means query causes lot of disk i/o. can increase various memory buffers if have ram speed (if you're using innodb) or myisam done through filesystem cacheing. restarting server flush these caches.

if not have enough ram (which shouldn't need 2m records) consider partitioning scheme against maybe ship-to-ids column (if version of mysql supports it).


Comments

Post a Comment

Popular posts from this blog

java - Jmockit String final length method mocking Issue -

-
using jmockit 1.2 jar, trying mock string's length method getting unexpected invocation exception: failed: test java.lang.illegalstateexception: missing invocation mocked type @ point; please make sure such invocations appear after declaration of suitable mock field or parameter @ stringdemo.testa$1.<init>(testa.java:17) @ stringdemo.testa.test(testa.java:13) i using testng: @test public void test() throws exception { new expectations() { @mocked("length") string astring; { astring.length(); result = 2; } }; system.out.println(a.showa("test")); } } actual class a: public class { public static int showa(string str){ int a= str.length(); return a; } } this wrong way of recording expected results. shouldn't mock , record string's length() method, record showa() instead. here solution @suppresswarnings("unused&qu
Read more

asp.net - Razor Page Hosted on IIS 6 Fails Every Morning -

-
i have simple page in intranet uses razor/asp fetch single record table , display it, plus few graphic, dashboard display. meaning, no user intervention other first time open ie internal url. the problem having every morning ie displays "page not found or network error" message. refresh page, , it's stuck , doesn't display anything. try different pc, open ie internal url , it's stuck... ...until following: login server hosting page run inetmgr go web sites, etc right click on page that's giving me problem , select browse at moment, error message: server error in '/' application. -------------------------------------------------------------------------------- type of page not served. description: type of page have requested not served because has been explicitly forbidden. extension '.cshtml' may incorrect. please review url below , make sure spelled correctly. requested url: /application/dashboard.cshtml ---------------
Read more

c++ - wxwidget compiling on windows command prompt -

-
can please guide me on method,as how compile wxwidget using windows command prompt or msys. [i can't seem find on wxwiki/wxwidget official book/anywhere else] i have compiled wxwidget instructions have provided (using msys). from searching on internet seems 1 can (maybe?) through use of makefile (is correct?) if yes: directory & how should link wx libraries in makefile if no: way besides makefile? i know can use ide's code::block , make life simpler prefer compile using command prompt/msys. thanks in advance.
Read more