Re: Help to improve clause in SQL Select

[Amarante]

Friends of the forum,
I have a select that is very slow and precise improve it so I ask your help.

Consider two tables:

CREATE TABLE "Propriedade" (
"Inscricao" INT(11) NOT NULL AUTO_INCREMENT,
"Titular_1" INT(11) NULL DEFAULT NULL,
"Titular_2" INT(11) NULL DEFAULT NULL,
"Titular_3" INT(11) NULL DEFAULT NULL,
PRIMARY KEY ("Inscricao")
)

CREATE TABLE "Titulares" (
"Titular_Codigo" INT(11) NOT NULL AUTO_INCREMENT,
"Nome" CHAR(60) NOT NULL,
PRIMARY KEY ("Titular_Codigo")
)

---------------------------------------------------------------------------
Relate the Titular_ fields 1, 2 and 3 of Propriedade table with Titular_Codigo field of Titulares table
The fields Propriedade.Titular_1, Propriedade.Titular_2 and Propriedade.Titular_3 that are as value 0 should return with Titulares.Nome = ""
Then used the select:

SELECT Propriedade.Inscricao,
Case When Propriedade.Titular_1 = 0 Then '' Else T1.Nome End,
Case When Propriedade.Titular_2 = 0 Then '' Else T2.Nome End,
Case When Propriedade.Titular_3 = 0 Then '' Else T3.Nome End
FROM Propriedade, Titulares AS T1, Titulares AS T2, Titulares AS T3
WHERE Propriedade.Titular_1 = Case When Propriedade.Titular_1 > 0 Then T1.Titular_Codigo Else 0 End
and Propriedade.Titular_2 = Case When Propriedade.Titular_2 > 0 Then T2.Titular_Codigo Else 0 End
and Propriedade.Titular_3 = Case When Propriedade.Titular_3 > 0 Then T3.Titular_Codigo Else 0 End
GROUP BY Propriedade.Inscricao, Propriedade.Inscricao, Propriedade.Inscricao
ORDER BY Inscricao
LIMIT 0, 500

---------------------------------------------------------------------------
Which returns me correctly:

"Inscricao", "T1.Nome", "T2.Nome", "T3.Nome"
"32", "ANTONIO", "MARIA", ""
"33", "ELIETE", "ELIZABETE", "MARLI"
"34", "DERCI", "MARGARIDA", "CARLITO"
"31", "ROBERTO", "LUIZ", ""
"37", "MARCIA", "", ""

---------------------------------------------------------------------------
The problem is that the amount of CASEs in a file with thousands of records is very slow
Can anyone help me improve the select?
In time, I can not change the files, only read them.

Thanks in advance.

[dragancesu]

http://stackoverflow.com/questions/4706 ... e-database

[Amarante]

dragancesu,
Thanks for your response,
I had already been testing if ()
see the results in 500 records:
with If () -> 2,450 sec
with case -> 2.403 sec.
Beyond what can not be stuck with a particular function of a particular Database as MySql.
But thanks for the tip.

[serge_girard]

Amarante,

Can you please provide sample data?

Serge

[serge_girard]

Amarante,

I think you must try it as following:

Code: Select all

SELECT Propriedade.Inscricao, T1.Nome                                          
FROM Propriedade ,                                                           
     Titulares AS T1                                                          
WHERE Propriedade.Titular_1   = T1.Titular_Codigo               
AND   Propriedade.Titular_1   > '0'                                           
UNION ALL                                                                    
SELECT Propriedade.Inscricao, T2.Nome                                        
FROM Propriedade ,                                                            
     Titulares AS T2                                                         
WHERE Propriedade.Titular_2   = T2.Titular_Codigo          
AND   Propriedade.Titular_2   > '0'                                           
UNION ALL                                                       
SELECT Propriedade.Inscricao, T3.Nome                                        
FROM Propriedade ,                                                         
     Titulares AS T3                                                         
WHERE Propriedade.Titular_3   = T3.Titular_Codigo               
AND   Propriedade.Titular_3   > '0'                                           
GROUP BY Propriedade.Inscricao, Propriedade.Inscricao, Propriedade.Inscricao 
ORDER BY Inscricao                                                          
LIMIT 0, 500                                                            

But without data it is like a blind man in a labyrinth...

Serge

[Amarante]

Serge,
As soon as possible will arrange the data.
Thank you for your attention.

[Rathinagiri]

What about this?

Code: Select all

SELECT Propriedade.Inscricao,
Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_1 ) End,
Case When Propriedade.Titular_2 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_2 ) End,
Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_3 ) End
FROM Propriedade
ORDER BY Inscricao
LIMIT 0, 500

[Amarante]

Serge,
Your test unfortunately not served.
I'll try to explain what I need, despite already be working, I need to optimize it.
The property table contains 3 fields (propriedade.titular_1, propriedade.titular_2 and propriedade.titular_3) that may or may not contain a number that identifies a record in the file holders.
The file contains the holders titular_codigo fields (same as titular_X, wherein X is 1, 2 or 3) and a field name.
I need to read the Table property and for each need Titular_X field displays the name contained in the table Holders and if the field is titular_X with 0 I must Exir the name blank.
That is, the result I need 4 columns of information
inscription, t1.nome, t2.nome, t3.nome

[Rathinagiri]

I really like the approach of saving 3 fields in the same field with an identifier. It is easy to search through all the names but we can select whenever we require them. I think these are last name, middle name and first name of persons. If they don't have a middle name, the space is saved! It may not be a bigger effect on small databases. But for a very big database, every byte is counted.

[Amarante]

Rathinagiri,
Your query solved my problem. Sensational.

I really like the approach of saving 3 fields in the same field with an identifier. It is easy to search through all the names but we can select whenever we require them. I think these are last name, middle name and first name of persons. If they don't have a middle name, the space is saved! It may not be a bigger effect on small databases. But for a very big database, every byte is counted.

We learn in the same school. For those who have had to store 8500 codes of 5 alphanumeric characters on a floppy 5 1/4 single-sided (about 360 kb) and a 48 kb memory, every byte was valuable. At that time I stored two characters in one byte doing bit shift (almost a zip precursor).
Even today with the wide availability of bytes continue with the idea of making space.
Follows a mass of data for testing.
Thank you master.