MySQL very slow with 20 Million Records -

-

doing simple query 

    select state, count(state) cnt big_data status=0 group state

takes 20 seconds. here table def:

    create table `newtable` (`id`  bigint(22) not null auto_increment ,                              `city`  varchar(32) character set latin1 collate latin1_swedish_ci not null ,                              `state`  varchar(2) character set latin1 collate latin1_swedish_ci not null ,                              `miles_away`  int(5) not null ,                              `member_id`  int(11) not null ,                              `gender`  varchar(17) character set latin1 collate latin1_swedish_ci not null ,                              `profile`  varchar(128) character set latin1 collate latin1_swedish_ci not null ,                              `status`  varchar(1) character set latin1                                 collate latin1_swedish_ci not null ,     primary key             ( `id`,                                `city`,                               `state`,                                `miles_away`,                                `member_id`,                                `gender`,                                `profile`,                                `status`),     unique index `id` (`id`) using btree ,     unique index `profile` (`profile`) using btree ,     index `city` (`city`) using btree ,     index `state` (`state`) using btree ,     index `miles_away` (`miles_away`) using btree ,     index `member_id` (`member_id`) using btree ,     index `gender` (`gender`) using btree ,     index `status` (`status`) using btree)     engine=innodb     default character set=latin1 collate=latin1_swedish_ci     auto_increment=12889691     row_format=compact;

even simple id lookup based on primary key takes long. server has 72 cores (4th gen xeon es2690 64gb ram)

here screenshot of explain:

multi threads http://gaysugardaddyfinder.com/shot.png

help!

use explain show execution plan.

reference: https://dev.mysql.com/doc/refman/5.5/en/using-explain.html

for best performance, provide suitable index optimizer can avoid "using file sort" operation satisfy group by.

i'd suggest:

create index `newtableix1` on `newtable` (`status`,`state`)

(i suggest because query includes equality predicate on status column, , performing group operation on state column. new index, i'd expect explain output show it's using new index, , show query being satisfied entirely index -- "using index" shown in column of explain output.)

with new index defined, separate index on status column redundant, , can dropped.

followup

since id unique , not null, serve primary key table. there's no need primary key include every column in table. (the secondary indexes going large table itself, since primary key going stored in each index "pointer" cluster index.)

if table, i'd replace this:

primary key             ( `id`,                            `city`,                           `state`,                            `miles_away`,                            `member_id`,                            `gender`,                            `profile`,                            `status`), unique index `id` (`id`) using btree ,

with this:

primary key (`id`)

(i'd create new table, , copy data old table.)


Comments

Post a Comment

Popular posts from this blog

c++ - No viable overloaded operator for references a map -

-
i trying use map can make tag names reference number. when try , use it, in code error (6 in total every time reference map): src/main.cpp:25:45: error: no viable overloaded operator[] type 'std::map<std::string, std::string>' const char* idcs = node.child_value(tagmap[3]); this code: #include "pugi/pugixml.hpp" #include <iostream> #include <string> #include <map> int main() { pugi::xml_document doca, docb; std::map<std::string, pugi::xml_node> mapa, mapb; std::map<std::string, std::string> tagmap {{"1", "data"}, {"2", "entry"}, {"3", "id"}, {"4", "content"}}; if (!doca.load_file("a.xml") || !docb.load_file("b.xml")) { std::cout << "can't find input files"; return 1; } (auto& node: doca.child(tagmap[1]).children(tagmap[2])) { co
Read more

java - Custom OutputStreamAppender not run: LOGBACK: No context given for <MYAPPENDER> -

-
i wish write custom appender on basis of outputstreamappender . wrote following class package tests; import java.io.ioexception; import java.io.outputstream; import ch.qos.logback.core.outputstreamappender; public class myappender<e> extends outputstreamappender<e> { public myappender() { system.out.println("myappender created"); setoutputstream(new outputstream() { @override public void write(int b) throws ioexception { throw new unsupportedoperationexception(); } }); } } then wrote following runner: package tests; import org.slf4j.loggerfactory; import ch.qos.logback.classic.logger; public class runner { private static final logger log = (logger) loggerfactory.getlogger(runner.class); public static void main(string[] args) { //log.addappender(new myappender<iloggingevent>()); log.info("hello world"); } } finall
Read more

java - Cannot secure connection using TLS -

-
i'm trying establish secure socket connection between java client applet (built jdk 1.7.0_75-b13) , vc++ server application. as test vehicle, used vc++ client/server sample found in msdn forums, modified use schannel , able establish socket using cipher suite tls_rsa_with_aes_128_cbc_sha. works of tls 1.0/1.1/1.2. when try opening socket java applet same server application, connection rejected server reporting following: tls 1.0 acceptsecuritycontext failed: 0x80090327 tls 1.1 acceptsecuritycontext failed: 0x80090331 tls 1.2 acceptsecuritycontext failed: 0x80090331 this java code used create socket: debugprint("setting secure connection"); sslsocketfactory sslsocketfactory = (sslsocketfactory) sslsocketfactory.getdefault(); sslsocket sslsocket = (sslsocket) sslsocketfactory.createsocket("127.0.0.1", socketnumber); debugprint("starting handshake"); sslsocket.settcpnodelay(true); sslsocket.setsolinger(false, 0); sslsocket.s
Read more