OT Optimizar query MySql

[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.

[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.

[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]

Prueba a usar HDO para MySQL.

[FranciscoA]

Oliveiros Junior.
Gracias por contestar.
Ambas tablas tienen un indice sobre el campo CodiCont.
Alguna otra sugerencia?
Saludos.

[FranciscoA]

xManuel.
Disculpa la ignorancia. Qué es HDO?
Gracias.

[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.

[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
 

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.

[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.

[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?

[FranciscoA]

1.000 records.

[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

[FranciscoA]

What do you think about the 1000 records specified?

[nageswaragunupudi]

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.

[nageswaragunupudi]

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.