MySQL Forums
Forum List  »  Optimizer & Parser

Normalize dump table
Posted by: Jose Valle
Date: October 11, 2015 08:37PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Normalize dump table
2528
October 11, 2015 08:37PM
895
October 11, 2015 08:42PM
1116
October 13, 2015 01:41PM
997
October 17, 2015 07:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.