Re: Help to improve clause in SQL Select

[Amarante]

Wow,
before -> 2.403 sec
now-> 0.016 Sec

Code: Select all

SELECT Propriedade.Inscricao, 
Propriedade.Titular_1, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_1 ) End,
Propriedade.Titular_2, Case When Propriedade.Titular_2 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_2 ) End,
Propriedade.Titular_3, 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

[Rathinagiri]

Friend, I am not a master.

I have used 'Explain query plan' command with my sql query in sqlite and it gave the following result.

This was my query.

Code: Select all

explain query plan SELECT number,
Case When n.title1 = 0 Then '' Else ( select t1.name from titles t1 where T1.titlenumber = n.title1 ) End,
Case When n.title2 = 0 Then '' Else ( select t1.name from titles t1 where T1.titlenumber = n.title2 ) End,
Case When n.title3 = 0 Then '' Else ( select t1.name from titles t1 where T1.titlenumber = n.title3 ) End
FROM name n
ORDER BY number
LIMIT 0, 500

Code: Select all

"0"	"0"	"0"	"SCAN TABLE name AS n"
"0"	"0"	"0"	"EXECUTE CORRELATED SCALAR SUBQUERY 1"
"1"	"0"	"0"	"SEARCH TABLE titles AS t1 USING INTEGER PRIMARY KEY (rowid=?)"
"0"	"0"	"0"	"EXECUTE CORRELATED SCALAR SUBQUERY 2"
"2"	"0"	"0"	"SEARCH TABLE titles AS t1 USING INTEGER PRIMARY KEY (rowid=?)"
"0"	"0"	"0"	"EXECUTE CORRELATED SCALAR SUBQUERY 3"
"3"	"0"	"0"	"SEARCH TABLE titles AS t1 USING INTEGER PRIMARY KEY (rowid=?)"

This is for your previous query.

Code: Select all

explain query plan SELECT n.number,
Case When n.Title1 = 0 Then '' Else T1.Name End,
Case When n.Title2 = 0 Then '' Else T2.Name End,
Case When n.Title3 = 0 Then '' Else T3.Name End
FROM name n, titles AS T1, Titles AS T2, Titles AS T3
WHERE n.Title1 = Case When n.title1 > 0 Then T1.titlenumber Else 0 End
and n.Title2 = Case When n.Title2 > 0 Then T2.Titlenumber Else 0 End
and n.Title3 = Case When n.Title3 > 0 Then T3.Titlenumber Else 0 End
GROUP BY n.number, n.number, n.number
ORDER BY n.number
LIMIT 0, 500

Code: Select all

"0"	"0"	"0"	"SCAN TABLE name AS n"
"0"	"1"	"1"	"SCAN TABLE titles AS T1"
"0"	"2"	"2"	"SCAN TABLE Titles AS T2"
"0"	"3"	"3"	"SCAN TABLE Titles AS T3"
"0"	"0"	"0"	"USE TEMP B-TREE FOR ORDER BY"

Even though query plan of yours looks simple, it makes a full scan of t1, t2, t3. Whereas in my query, it is only a primary key search which will be fast.

[Amarante]

Rathinagiri,
Thanks for the tip to "explain query plan" in SQLite. I did not know. It is very useful.

[Rathinagiri]

I am sure this page would be helpful to you.

http://www.sitepoint.com/using-explain- ... l-queries/

[koke]

Hola intenta esto.
Hi Try this.

select P.Inscricao, P.titular_1, P.titular_2, P.titular_3,
Case When P.titular_1 = 0 Then '' Else T1.nome END,
Case When P.titular_2 = 0 Then '' Else T2.nome END,
Case When P.titular_3 = 0 Then '' Else T3.nome END From Propriedade as P
JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
JOIN Titulares as T3 ON P.titular_2 = T3.titular_codigo

[Amarante]

koke,
Thanks for the tip.
The problem is that in its select the records with titular_2 field with zero are not displayed.

[koke]

koke,
Thanks for the tip.
The problem is that in its select the records with titular_2 field with zero are not displayed.

try this one

select P.Inscricao, P.titular_1, P.titular_2,P.titular_3,
Case When P.titular_1 = 0 Then '' Else T1.nome END,
Case When P.titular_2 = 0 Then '' Else T2.nome END,
Case When P.titular_3 = 0 Then '' Else T3.nome END
From Propriedade as P
LEFT JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
LEFT JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
LEFT JOIN Titulares as T3 ON P.titular_3 = T3.titular_codigo

[koke]

On my pc it works faster with this one

select P.Inscricao, P.titular_1, P.titular_2, P.titular_3,
IF (P.titular_1 = 0,' ', T1.nome),
IF (P.titular_2 = 0,' ', T2.nome),
if (P.titular_3 = 0,' ', T3.nome)
From Propriedade as P
LEFT JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
LEFT JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
LEFT JOIN Titulares as T3 ON P.titular_3 = T3.titular_codigo

[Amarante]

Thank you all for the help.
I look for a query that runs on MySQL and SQLite.
Did some testing in MySQL and SQLite, and found interesting things and share them with you:

The Database
Table Propriedade -> 369 records
Table Titulares -> 1074 records

Query from Rathinagiri

Code: Select all

SELECT Propriedade.Inscricao, 
Propriedade.Titular_1, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t1.nome from Titulares t1 where T1.Titular_Codigo = Propriedade.Titular_1 ) End,
Propriedade.Titular_2, Case When Propriedade.Titular_2 = 0 Then '' Else ( select t2.nome from Titulares t2 where T2.Titular_Codigo = Propriedade.Titular_2 ) End,
Propriedade.Titular_3, Case When Propriedade.Titular_1 = 0 Then '' Else ( select t3.nome from Titulares t3 where T3.Titular_Codigo = Propriedade.Titular_3 ) End
FROM Propriedade

MySQL -> 369 records
SQLite -> 367 records

Query from Koke

Code: Select all

select P.Inscricao, 
  P.titular_1, Case When P.titular_1 = 0 Then '' Else T1.nome END,
  P.titular_2, Case When P.titular_2 = 0 Then '' Else T2.nome END,
  P.titular_3, Case When P.titular_3 = 0 Then '' Else T3.nome END
  From Propriedade as P
  JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
  JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
  JOIN Titulares as T3 ON P.titular_2 = T3.titular_codigo

MySQL -> 367 records
SQLite -> 367 records

Query 2 from Koke

Code: Select all

select P.Inscricao, P.titular_1, P.titular_2, P.titular_3,
IF (P.titular_1 = 0,' ', T1.nome),
IF (P.titular_2 = 0,' ', T2.nome),
if (P.titular_3 = 0,' ', T3.nome)
From Propriedade as P
LEFT JOIN Titulares as T1 ON P.titular_1 = T1.titular_codigo
LEFT JOIN Titulares as T2 ON P.titular_2 = T2.titular_codigo
LEFT JOIN Titulares as T3 ON P.titular_3 = T3.titular_codigo

MySQL -> 369 records
SQLite -> Error: no such function: IF

[Rathinagiri]

The reason might be because two records having nonzero values but not found in the second table. When you do a LEFT JOIN, it is just an addition of fields to the left table with records wherever available. If matching record is not available in the joined tables, even then those records in the first table are shown. That's why there are 2 records more in LEFT JOIN.