Optimize Mysql Database?

Question

Optimize Mysql Database?

Navigation

#1 by (1 votes)

0

I have a TABLE called "Data" which I filled with information brought from a CSV file (Generated from a third-party system).

the database has multiple columns with Codes that are not numeric. This is just a small example

Table Data Easily exceeded 250,000 records per month

as " Solution " I had thought about dividing the data table into several tables. that is customers, routes, products

and fill these tables from mysql queries such as

insert into productos(producto_codigo,producto)
select producto_codigo,producto from datos

this also adding a numeric id to each table to achieve a result like this

after this insert the auto numeric key in the "Data" table with the following query

UPDATE datos as d,productos as p
SET d.productoid = p.id
where d.codigo_producto = p.codigo_producto

which worked for a while. over time the querys took longer and longer. obviously I'm doing it wrong

for you what would be the right way to do it? How do I make this optimal?

taking into account that the CSV file is the only possible way to migrate data due to business problems with the third party company

mysql mysql-workbench phpmyadmin

asked by Wilfredo Aleman 24.08.2017 в 06:33

source

1 answer

Save data in an array using foreach loop SQL Server procedure always returns int

score 1 · Accepted Answer

Changes you should make:

First, create indexes for productos.codigo_producto and datos.codigo_producto .

Example:

ALTER TABLE 'productos' ADD INDEX ('codigo_producto'(24));
ALTER TABLE 'datos' ADD INDEX ('codigo_producto'(24));

# Donde "(24)" es la cantidad de caracteres que serán tomados para crear los indices
# Modifica dicho valor a gusto

Once you have created these indexes you can optimize the query of UPDATE like this:

UPDATE datos as d
  INNER JOIN productos as p
    ON p.codigo_producto = d.codigo_producto
SET d.productoid = p.id