List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:October 3 2006 10:00am
Subject:Re: join vs subqueries
View as plain text  
I cannot help you on specific performance timings --

but if the result is the same, the database engine can use whatever
trick to retrieve them. That being said, a "subquery/derived table"
could then be rewritten (internally) to a JOIN, for example.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> I'm not (yet) using sub-queries since the old version of MySQL were
> unable to handle them, then I was using 'join'.
>
> I wish to know if it's possibile to do all what I did with 'join'
> with subqueries.
>
> and which one is faster/better to use?
>
> for example it'd be possibile to 'translate' that using subselect?
>
> SELECT Esercente.Insegna, Terminale.TermId, Abilitazione.CodCarta,
>         Circuito.Nome, Abilitazione.Data, Circuito.Tel
> FROM Esercente
> INNER JOIN Terminale ON Esercente.CodSia = Terminale.CodSia
> LEFT  JOIN Abilitazione ON Terminale.TermId=Abilitazione.TermId
> LEFT  JOIN Circuito ON Abilitazione.CodCarta=Circuito.CodCarta
>
> my db is that (and on attachment there is the pdf with the logical
> schema)
>
> CREATE DATABASE IF NOT EXISTS CentroServizi;
> USE CentroServizi;
> CREATE TABLE IF NOT EXISTS Esercente
> (
> CodSia  MEDIUMINT UNSIGNED NOT NULL,  #Il CodSia e' un numero
> positivo a 7
>                                         # cifre;
>                                         # MEDIUMINT UNSIGNED va da 0
> a 16777215
> Citta   VARCHAR(20) NOT NULL,
> Insegna VARCHAR(50),
> PRIMARY KEY (CodSia)
> )
> TYPE = InnoDB
>
>
> CREATE TABLE IF NOT EXISTS Terminale
> (
> TermId   MEDIUMINT UNSIGNED NOT NULL, # il TermId e' numero positivo
> a 6 cifre
>                                         # MEDIUMINT UNSIGNED va da 0
> a 16777215
> CodSia   MEDIUMINT UNSIGNED NOT NULL,
> Modello  VARCHAR(20),
> PRIMARY KEY (TermId),
> INDEX CodSia (CodSia),
> FOREIGN KEY (CodSia) REFERENCES Esercente(CodSia)
>                        ON UPDATE CASCADE
>                        ON DELETE CASCADE
> )
> TYPE=INNODB;
>
> CREATE TABLE IF NOT EXISTS Circuito
> (
> CodCarta TINYINT UNSIGNED NOT NULL, # il CodCarta e' un numero
> positivo a 2
>                                       # cifre
>                                       # TINYINT UNSIGNED va da 0 a 255
> Nome     VARCHAR(20) NOT NULL,
> Tel      VARCHAR(50),
> PRIMARY KEY (CodCarta)
> )
> TYPE=INNODB;
>
> CREATE TABLE IF NOT EXISTS Abilitazione
> (
> TermId    MEDIUMINT UNSIGNED NOT NULL,
> CodCarta  TINYINT   UNSIGNED NOT NULL,
> Data      DATE NOT NULL,
> PRIMARY KEY (TermId, CodCarta),
> INDEX TermId (TermId),
> INDEX CodCarta  (CodCarta),
> FOREIGN KEY (TermId) REFERENCES Terminale(TermId)
>                          ON UPDATE CASCADE
>                          ON DELETE CASCADE,
> FOREIGN KEY (CodCarta) REFERENCES Circuito(CodCarta)
>               ON UPDATE CASCADE
>               ON DELETE CASCADE
> )
> TYPE=INNODB;
>
>
> CREATE TABLE IF NOT EXISTS ChiaInt
> (
> CallId      INT UNSIGNED NOT NULL, # Si potranno gestire fino a
> 4294967295
>                                      # chiamate
> Operatore   VARCHAR(20) NOT NULL,
> Chiamante   MEDIUMINT UNSIGNED NOT NULL , # e' il CodSia
> Inizio      DATETIME NOT NULL,
> Fine        DATETIME NULL,
> Manutentore VARCHAR(20),
> Motivo      TEXT, # Stringa lunga al max 65KB
> PRIMARY KEY (CallId),
> INDEX Chiamante (Chiamante),
> FOREIGN KEY (Chiamante) REFERENCES Esercente(CodSia)
>                        ON UPDATE CASCADE
>                        ON DELETE CASCADE
> )
> TYPE=INNODB;
>
>
>
>
> thank you in advance
>
> MAS!
>


----------------------------------------------------------------------------
----


>
>
>


----------------------------------------------------------------------------
----


>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
join vs subqueriesMAS!3 Oct
  • Re: join vs subqueriesMartijn Tonies3 Oct