HMGforum.com

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

Amarante,

Can you please provide sample data?

Serge

Amarante,

I think you must try it as following:
But without data it is like a blind man in a labyrinth...

Serge

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

What about this?

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

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.

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.