OT Optimizar query MySql

User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

OT Optimizar query MySql

Post by FranciscoA »

Hola amigos.
Tengo las siguientes consultas MySql que estoy tratando de optimizar, pero no lo he logrado.
Alguno de ustedes puede sugerirme alguna manera de lograrlo?

English:
Hi.
I have the following MySql queries that I am trying to optimize, but I have not succeeded.
Can any of you suggest some way to achieve it?

Code: Select all

//Tarda 3 min para 60 Mil registros (demasiado tiempo) 
//This query takes 3 minutes on a table with 60,000 records. Too much time.

SELECT a.codicont, a.nombre
FROM facturas a
WHERE NOT EXISTS ( SELECT b.codicont FROM catalogo b
                   WHERE b.codicont = a.codicont ) ;
                    

//Esta tarda 3 min para 60 Mil registros (demasiado tiempo) 
//This query takes 3 minutes on a table with 60,000 records. Too much time.

SELECT codicont, nombre 
FROM facturas 
WHERE codicont NOT IN (SELECT codicont FROM catalogo) ;  


//Esta tarda 5 min para 60 Mil registros (demasiado tiempo) 
//This query takes 5 minutes on a table with 60,000 records. Too much time.

SELECT t1.codicont, t1.nombre
  FROM facturas t1
  LEFT JOIN catalogo t2
    ON t2.codicont = t1.codicont
 WHERE t2.codicont IS NULL ;         
 

//Esta se toma3 segundos, pero trae todos los datos, y solo queremos los nulos. (los que no existen en tabla catalogo)
//This query takes 3 seconds on a table with 60,000 records, but we wants null records only.

SELECT A.codicont AS codigo, A.nombre AS nombre, B.codicont AS catalogo
FROM facturas A LEFT JOIN catalogo B
ON A.codicont = B.codicont ;
 
Pueden ayudar?
Can help?

Saludos.
Best regards.
Last edited by FranciscoA on Tue Mar 03, 2020 7:43 pm, edited 1 time in total.
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

Como podemos obtener solo los nulos, en esta consulta?

Code: Select all

SELECT A.codicont AS codigo, A.nombre AS nombre, B.codicont AS catalogo
FROM facturas A LEFT JOIN catalogo B
ON A.codicont = B.codicont ;
 
Gracias

Editado: Si le agregamos el WHERE, se toma 5 minutos.
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
oliveiros junior
Posts: 84
Joined: Tue Mar 20, 2007 3:13 pm

Re: OT Optimizar query MySql

Post by oliveiros junior »

Olá Francisco,

Para agilizar a consulta crie índices em ambas as tabelas com o campo codicont. isso irá agilizar a consulta.

Att.,

Oliveiros Junior
xmanuel
Posts: 613
Joined: Sun Jun 15, 2008 7:47 pm
Location: Sevilla
Contact:

Re: OT Optimizar query MySql

Post by xmanuel »

Prueba a usar HDO para MySQL.

:D
______________________________________________________________________________
Sevilla - Andalucía
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

Oliveiros Junior.
Gracias por contestar.
Ambas tablas tienen un indice sobre el campo CodiCont.
Alguna otra sugerencia?
Saludos.
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

xManuel.
Disculpa la ignorancia. Qué es HDO?
Gracias.
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

Manu.
Disculpas. Ya encontré alguna informacion, pero dice ser para HARBOUR. Yo uso XHarbour.
Continaré buscando, a ver si ya tienes la version para xharbour.
Gracias.
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: OT Optimizar query MySql

Post by nageswaragunupudi »

It does not matter at all whether you use HDO, ADO, Dolphin, TMySql or FWH library. None of them help us to optimize our sql queries. Optimization of queries is something we need to do ourselves. For this purpose, no point discussing which library to use.

I do not know how big is `catalogo` table. In case this table is not very large, we can use the approach given below.

FWH provides a demo server in the cloud free to our users for the purpose of testing and demonstrations. But this is a very slow server and even the communication to and from the server is quite slow.

We have a table `states` on this server which is a copy of the states.dbf in our samples folder.
We also have another table `custbig` with the same structure as the customer.dbf in the samples folder, but contains one million (1,000,000) records. Field `state` is indexed.

As we know the `state` field of `custbig` corresponds to the field `code` in the table `states`. Our job is to select rows from custbig table where `state` is not found in the `states` table.

Here is the code we tested:

Code: Select all

#include "fivewin.ch"

function Main()

   local oCn   := FW_DemoDB()
   local oRs, cSql, cCodes, nSecs

   ? cCodes := oCn:QueryResult( "SELECT GROUP_CONCAT( code ) AS codes FROM states" )
   ? cCodes := "( '" + StrTran( cCodes, ",", "','" ) + "' )"

   nSecs := SECONDS()
   oRs   := oCn:RowSet( "SELECT ID,FIRST,STATE FROM custbig WHERE STATE NOT IN " + cCodes )
   nSecs := SECONDS() - nSecs
   XBROWSER oRs TITLE "Read in " + cValToChar( nSecs ) + " seconds"

   oCn:Close()

return nil
 
Image

The query took nearly one second or less.
If this is the speed we obtained on a very slow server with a table containing 1,000,000 records, you should be able to get instantaneous response from 60,000 records on a better server.

Though we used FWH built-in library for this sample, we get the same speed whatever the libray we use ADO, Dolphin or any other. Speed does not depend on the library but on the sql query and indexes.

Note: If you are using MySql Server Version 8.0 or above, we have much better options.
Regards

G. N. Rao.
Hyderabad, India
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

Mr. Rao.

Gracias por su generosa respuesta.
Thanka you for your generous response.

Uso MySql Server 5.1.
I am using MySql Server 5.1.

Había logrado bajar el tiempo de respuesta a 2.3 segundos, con las siquientes consultas:
I had already managed to minimize the response time to 2.3 seconds using the following queries:
(I was trying to do it in a single query...uff)

Code: Select all

DROP TABLE IF EXISTS pruebas ;

CREATE TABLE IF NOT EXISTS pruebas LIKE facturas ;

INSERT INTO pruebas SELECT * FROM facturas GROUP BY codicont ;

SELECT fechemis,td,numdoc,codicont,nombre FROM pruebas 
WHERE codicont NOT IN (SELECT codicont FROM catalogo) ;
 
Probaré su código y le digo.
I'lll test your code and I'll tell you.


Gracias nuevamente.
Thanks again.
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: OT Optimizar query MySql

Post by nageswaragunupudi »

Code: Select all

WHERE codicont NOT IN (SELECT codicont FROM catalogo)
This is not well optimized by MySQL

By the way how many records are there in catalogo?
Regards

G. N. Rao.
Hyderabad, India
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

1.000 records.
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

Mr. Rao.

I tested your code and we got the answer in 1.55 / 1.11 seconds from the demo server.

My computer is quite old and I use win8 32 bits
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
FranciscoA
Posts: 1964
Joined: Fri Jul 18, 2008 1:24 am
Location: Chinandega, Nicaragua, C.A.

Re: OT Optimizar query MySql

Post by FranciscoA »

What do you think about the 1000 records specified?
Francisco J. Alegría P.
Chinandega, Nicaragua.

Fwxh1204-MySql-TMySql
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: OT Optimizar query MySql

Post by nageswaragunupudi »

FranciscoA wrote:Mr. Rao.

I tested your code and we got the answer in 1.55 / 1.11 seconds from the demo server.

My computer is quite old and I use win8 32 bits
Did the sample code work for you? I thought you had an older version of FWH that does not support FW_DemoDB() function.
This server is situated in Europe. Speed may vary based on the distance of our location as well the traffic on the server at the same time. Lot of people like us keep using this cloud server.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: OT Optimizar query MySql

Post by nageswaragunupudi »

FranciscoA wrote:What do you think about the 1000 records specified?
1000 seems large. But let us give it a try.
Please also let me know if the `codicont` field is numeric or character and what is the size of the field.
Regards

G. N. Rao.
Hyderabad, India
Post Reply