Keiter: Rathrock: right. And I’m giving you hints on where you should start.
Cegla: Rathrock: well you aren’t listening to one of the most knowledgeable people in this channel :
Mithell: Keiter: no crown for you.
Laurito: Rathrock: See http://dev.mysql.com/doc/refman/5.6/en/using-explain.html
Letman: Rathrock: See http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
Keiter: Rathrock: and use SHOW CREATE TABLE to see if you’re using InnoDB
Keiter: Rathrock: then set the innodb_buffer_pool_size and innodb_log_file_size properly.
Raiford: OMG, just noticed its MyISAM
Keiter: Rathrock: your large static table can remain MyISAM, really.
Keiter: Rathrock: it’ll just cause other issues if you haven to take a snapshot for a backup, for instance.
Vanos: Well. I kind of did it. I have a new field, auto increment and it populated. I do have a key but it is MUL . will that work?
Logie: Diex7: don’t explain table
Moerke: Use SHOW CREATE TABLE tablename;
Dyner: If we backup we usually exclude logs and a second backup for logs only at like 3am
Shonts: Rathrock: InnoDB offers ACID transactions, foreign key constraints, and robust crash recovery. Generally a good default unless something specific requires MyISAM.
Filipponi: Naktibalda: how is that different
Remian: Tidx int11 NOT NULL AUTO_INCREMENT,n KEY idx_table1_tidx tidxn ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CH****T=utf8′
Curbo: This does not say its a Primary key
Keiter: Diex7: because you didn’t add it as a primary key
Lundgren: Keiter: it gives complete information about table
Saden: If Key is MUL, multiple occurrences of a given value are permitted within the column. The column is the first column of a nonunique index or a unique-valued index that can contain NULL values.
Bredice: Diex7: it gives complete information about table
Antone: Keiter: ADD PRIMARY KEY tidx
Shumake: Naktibalda: oh, thats bad, it should be unique
Praml: Keiter: dont kill me but : http://www.itswar.net/maps/uploads/1442913061.png
Milich: Got it! tidx int11 NOT NULL AUTO_INCREMENT,n PRIMARY KEY tidx,n KEY idx_table1_tidx
Keiter: Rathrock: this is full of terrible. You need to rewrite that query
Keiter: Rathrock: first step is to change the dependent subqueries to joins
Leffers: Still not sure why its instant on local
Churn: Rathrock: See http://dev.mysql.com/doc/refman/5.6/en/rewriting-subqueries.html
Keiter: Rathrock: are you using 5.6 locally?
Keiter: Rathrock: also, your derived table with 86k rows is bad news.
Tawwab: Rathrock: have you got the same amount of data in your local database?
Fancher: 5.5.43-MariaDB locally mysql Ver 14.14 Distrib 5.1.73 in PROD
Woytowicz: Naktibalda: locally its a day older
Demarcus: As it seems our vps server is using latin1_swedish_ci for collation as default, to prevent this kind of mismatch http://picpaste.com/latin1-to-utf8mb4-rldPOO1K.png I want to set utf8mb4_unicode_ci as default. Is editing /etc/my.cnf the correct file to make changes?
Rohland: Rathrock: compare output of EXPLAIN in your local machine
Stickman: Naktibalda: you migth be on to something, let me recopy to be sure, just slow net as backups are running too
Goswick: Naktibalda: this is locally the copy of my local one is 10 sec old http://www.itswar.net/maps/uploads/1442903154.png
Biel: Where is that derived table?
Hemric: Urgh, ok now im really going to die :p
Cove: Https://gist.github.com/Rathrock/4109675768ee7b60db1c
Brittman: Surely I am doing something wrong here, or misreading the docs http://pastie.org/10436006
Brittman: I didn’t think this was supposed to be possible