2

In short, I have this SQLite3 query that uses up 30% of CPU when being run.

SELECT s.*
    ,r.RaagEnglish
    ,(
        SELECT w.WriterEnglish
        FROM Writer w
        WHERE w.WriterID = (
                SELECT MAX(s1.WriterID)
                FROM Shabad s1
                WHERE s1.ShabadID = s.ShabadID
                )
        ) AS WriterEnglish
FROM Shabad s
    ,raag r
WHERE r.RaagID = s.RaagID
    AND s.ShabadID = $SHABADNO
ORDER BY ShabadID ASC

I have 3 tables in my Database: Writer, Raag, and Shabad.

A row in Shabad has a WriterID and a RaagID column.

This SQL Query has to get all the Rows in Shabad that match with the Column ShabadID, plus they have to match the WriterID column with WriterEnglish column from the Writer Table. And match RaagID column to RaagEnglish column from the Raag table.

This is a big query, and I am a SQL n00b, so I would appreciate if someone could help me optimize this query.

In the query above, Shabad is the Article, Raag is the Article Format. Max chooses the maximum value from all of the rows of a specific column.

Here is my DB (no private info, don't worry):

Raag

enter image description here

Writer

enter image description here

Shabad:

enter image description here

3

What do you want to achieve? How are the two tables linked?

Perhaps a LEFT JOIN + USING(RaagID) or something like that is better?

Also, it might help to know what Shabad and Raag mean in English.

Update from comments: shabad is the main table and has references to the other two, so I believe the answer is

SELECT *
FROM shabad s
LEFT JOIN raag r using (raagid)
LEFT JOIN writer w using (writerid)

then filter that with where-clause and your criteria

2

@flyby's answer was perfect, but I had a few problems that are specific to me that need to be addressed.

SELECT *
FROM   Shabad
       LEFT JOIN Writer USING(WriterID)
       LEFT JOIN Raag USING(RaagID)
WHERE  ShabadID = $shabadno
ORDER  BY ID ASC  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.