Re: Help to improve clause in SQL Select

Moderator: Rathinagiri

User avatar
Amarante
Posts: 182
Joined: Fri Apr 27, 2012 9:44 pm
Location: Araruama-RJ, Brazil

Re: Help to improve clause in SQL Select

Post by Amarante » Thu Apr 09, 2015 6:49 pm

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

User avatar
Rathinagiri
Posts: 5264
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 169 times
Been thanked: 172 times
Contact:

Post by Rathinagiri » Thu Apr 09, 2015 6:52 pm

:) 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.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
Amarante
Posts: 182
Joined: Fri Apr 27, 2012 9:44 pm
Location: Araruama-RJ, Brazil

Post by Amarante » Thu Apr 09, 2015 7:05 pm

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

User avatar
Rathinagiri
Posts: 5264
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 169 times
Been thanked: 172 times
Contact:

Post by Rathinagiri » Thu Apr 09, 2015 7:06 pm

I am sure this page would be helpful to you.

http://www.sitepoint.com/using-explain- ... l-queries/
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

User avatar
koke
Posts: 106
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 41 times
Been thanked: 25 times

Post by koke » Fri Apr 10, 2015 6:15 pm

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
,___,
[O.o]
/)__)
-”–”-
KoKe

User avatar
Amarante
Posts: 182
Joined: Fri Apr 27, 2012 9:44 pm
Location: Araruama-RJ, Brazil

Post by Amarante » Fri Apr 10, 2015 6:40 pm

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

User avatar
koke
Posts: 106
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 41 times
Been thanked: 25 times

Post by koke » Fri Apr 10, 2015 7:56 pm

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
Last edited by koke on Fri Apr 10, 2015 8:02 pm, edited 1 time in total.
,___,
[O.o]
/)__)
-”–”-
KoKe

User avatar
koke
Posts: 106
Joined: Wed Aug 21, 2013 3:54 pm
DBs Used: DBF, mySql, mariaDB
Has thanked: 41 times
Been thanked: 25 times

Post by koke » Fri Apr 10, 2015 8:01 pm

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
,___,
[O.o]
/)__)
-”–”-
KoKe

User avatar
Amarante
Posts: 182
Joined: Fri Apr 27, 2012 9:44 pm
Location: Araruama-RJ, Brazil

Post by Amarante » Sat Apr 11, 2015 2:03 pm

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 :D
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 :D
SQLite -> Error: no such function: IF :?

User avatar
Rathinagiri
Posts: 5264
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Has thanked: 169 times
Been thanked: 172 times
Contact:

Post by Rathinagiri » Sat Apr 11, 2015 3:15 pm

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.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.

Post Reply