Greetings to all of you.
I have recently seen the need to delve more into the world of the DBA, because in my current company are having changes and some of these changes is to migrate information hosting provider to our own systems, in this context I pose the following :
The vendor provided us with history of transactions totaling approximately 7.5 million records, but these records are not standardized, so lucky I have a table with 7 columns of concepts.
Breakdown 3 columns in their tables, so that I have every concept index for each column, the problem comes when trying to dump the table "dump" to a table properly normalized as response times are very high (take two days with a query running) through consultations type:
Theta
insert into sales (col1, col2, col3, ncol1) SELECT col1, col2, col3, (select tn1.id from TN1 Where tn1.concepto = dump.concepto) from dump [/ CODE]
ANSI
insert into sales (col1, col2, col3, ncol1) select a.col1, a.col2, a.col3, b.col1 innert from dump to join b on TN1 (b.concepto = a.concepto) [/ CODE]
It does not really know if it's the right way to normalize a table with the number of records, or if there may be another way to do it.
I changed the type of database engine between "InnoDB" and "MyISAM" I really do not see much change in the performance tests with 100,000 records.
Also try instead to dump the table "dump" add index columns in it and run an update:
obj left join sales_dump update product on obj.product src = src.name September obj.product_id src.id WHERE 1 = [/ CODE]
Both ANSI format and Theta.
But it is also flying the query, MySQL Workbench advance with monitoring the work of the manager and if you are running queries, however testify to the fastest consultations, also it depends on the hardware where is the DBMS.
This is my configuration file.
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208
# Configuration name generated sysdev01 10/07/2015 19:30:43
[Mysql]
CLIENT # #
port = 3306
socket = "C: /xampp/mysql/mysql.sock"
[Mysqld]
GENERAL # #
#user = mysql
default-storage-engine = InnoDB
socket = "C: /xampp/mysql/mysql.sock"
basedir = "C: / xampp / mysql"
pid-file = "mysql.pid"
server-id = -19910709
PLUGIN_DIR = "C: / xampp / mysql / lib / plugin /"
skip-federated
# MyISAM #
key-buffer-size = 64M
myisam-recover-options = FORCE, BACKUP
SAFETY # #
max-allowed-packet = 16M
max-connect-errors = 1000000
# DATA STORAGE #
datadir = "C: / xampp / mysql / data"
BINARY LOGGING # #
log-bin = "mysql-bin"
expire-logs-days = 14
sync-binlog = 1
# CACHE AND LIMITS #
tmp-table-size = 64M
max-heap-table-size = 64M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 524288
INNODB # #
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-TRX-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 1G
LOGGING # #
log-error = "mysql-error.log"
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = "mysql-slow.log"
[/ CODE]
And this is my hardware
HDD: 540Gb 5400 rpm.
RAM: 8GB 1333Mhz.
Processor: 2.4 GHz i7 4700MQ
S.O. Win 10 Pro
[/ CODE]
This is development environment.
I appreciate their valuable contributions