Results 1 to 4 of 4

Thread: Left Join

  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Left Join

    I have 2 SQLite3 tables like this

    table persons (~15,000 records)
    ID, Name, Address
    ID is PK

    table OtherInfo (~10,000 records)
    ID, personID, InfoNumber
    ID is PK, personID is FK and UNIQUE

    Code:
    SELECT COUNT(p.ID)
    FROM persons p LEFT JOIN OtherInfo oi ON oi.personID = p.ID
    WHERE <cond>
    
    SELECT p.ID, p.Name, oi.InfoNumber
    FROM persons p LEFT JOIN OtherInfo oi ON oi.personID = p.ID
    WHERE <cond>
    Even though there is just one InfoNumber for each person, it wasnt created in the person table inorder to save space (in the actual table, there are many more VARCHAR fields).

    But now, the LEFT JOIN query is taking a whole lot of time, so I may have to restructure the tables to have all fields in a single table.

    But is there a way out for the LEFT JOIN query ?

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "a way out" mean?

    did you declare an index on personID?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    I initially create index on 2 columns :

    CREATE UNIQUE INDEX IF NOT EXISTS "UID" ON "OtherInfo" (personID, InfoNumber);

    But have changed that to personID UNIQUE only. Now not so slow.

    But as more data is added, LEFT JOIN query is going to be slower. I was wondering if there was to optmize it instead of restructuring the table altogether.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You have an ID column on OtherInfo. Is that necessary? Why not make PersonID the key since you know it will be unique?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •