I didn’t think this was.

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