MySQL Forums
Forum List  »  Optimizer & Parser

Optimize query join with 25,000 records
Posted by: Andrea Presazzi
Date: August 26, 2013 02:27AM

Hello,
I wanted to submit a question optimization, if possible, about a query to JOIN multiple tables.
Optimization because the affected tables have between 20,000 and 30,000 records.

Currently it's work and everything is run, only that the average execution time of the query is 20 minutes.
Immediately place the query so I go to the point:
SELECT
prodottidatigen.codprod,prodottidatigen.descrizion e,prodottidatigen.prz,prodottidatigen.iva,,prodott idatigen.um,
jgs.Trattanti,jgs.qtatrattate,jgs.euro,
prodottidatiean.ean,
prodottidatigiacenze.giac_fisica,
prodottifornitore.codprodfornit,prodottifornitore. codfornit,prodottifornitore.nomefornit,
prodottipackage.pzct,
prodottidatiprezzi01.datadecor01,prodottidatiprezz i01.przAcq01,
prodottidatiprezzi10.datadecor10,prodottidatiprezz i10.przAcq10
prodottidatiprezzizero.datadecorzero,prodottidatip rezzizero.przAcqzero,
prodottiOper.NomeOperatore
FROM prodottidatigen
LEFT JOIN jgs USING (codprod)
LEFT JOIN prodottidatiean USING (codprod)
LEFT JOIN prodottidatigiacenze USING (codprod)
LEFT JOIN prodottipackage USING (codprod)
LEFT JOIN prodottifornitore USING (codprod)
LEFT JOIN prodottidatiprezzi01 USING (codprod)
LEFT JOIN prodottidatiprezzi10 USING (codprod)
LEFT JOIN prodottidatiprezzizero USING (codprod)
LEFT OUTER JOIN prodottiOper
ON prodottifornitore.operat = prodottiOper.codOper

So, there are 10 tables each containing various data/different fields of sale items, which are about 25,000
All tables have in common the ProductCode, which is used as a merge field.
Only the last table does not use the ProductCode as a merge field but uses a different one.
I perform this query and can retrieve all the data I need, relate properly.
Everything working, but the running time is just a bit 'long,
I guess due to the fact that the records involved are so great.
The data obtained from this query will be use to insert a table "final" consultation through:
INSERT INTO tabFinal (codProd, ..., ...,)
SELECT ==> join query shown above

If there is the possibility to optimize the execution time of the query,
maybe using another method instead of join are well available to test it.

Thanks to anyone who can give me a hint

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimize query join with 25,000 records
2363
August 26, 2013 02:27AM


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.