Optimize SQL query - how? (was: SQlite and primary key)

Just starting out? Need help? Post your questions and find answers here.
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

Good morning
In my program, I use an SQlite database.
I have a table with the primary key on a Text field.

When I add new records, these are saved at the end of the table. I deduce that the primary key is not sorted.

Should we reorganize this table so that the column that corresponds to my primary key is sorted on this column? And if so, how do we do it?

Thank you for your advice.
Last edited by jak64 on Sun Mar 19, 2023 8:47 pm, edited 1 time in total.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by infratec »

Hm ... it looks like you have not really an idea of SQL.

A key is not used for sorting.
It is used to speedup your SQL statements or for identify one record for an update.

Sorting is done bei your SQL cmd, like:

Code: Select all

SELECT * FROM table ORDER BY name ASC
If you have a key/index on the field name, the SQL cmd is faster.
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

Hello infratec,
I may have misspoken:

When my program makes a select on this table on the field corresponding to the primary key, I imagine that the search is done by dichotomy on this column, hence my question of sorting or not on this column so that the search is more fast.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by infratec »

I understand you.

But you don't understand my answer:

You need an ORDER BY clause for sorting.

A key/index alone has no influence on sorting.
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

Hello infratec,

I reformulate my question which does not concern the sorted result or not of my query.

Let me explain :
When SQlite does a search on the primary key with my select, does it read the table sequentially or does it proceed by binary search?

If, for example, my table contains 65,536 records, by binary search SQlite will only read 16 table readings instead of, on average, 65536 / 2 = 32768 table readings if it reads the table sequentially.

Or I ask myself too many questions and SQlite manages...
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by infratec »

So you mean not sorting, you mean the speed of the WHERE condition.

If you have a key/index which fits or help your WHERE condition, then the reading is much faster.
But all this is handled by the SQL engine. It optimizes as good as possible.
You can only assist the engine by providing good SQL statements and optimized keys.

PRIMARY or not has no influence.
A PRIMARY key means only that it is a key and unique for each record.
So it is not a good idea to add a pimary key on a text field, because you never know if somebody enters the same text
and then the insert fails.
You normaly use an id field which autoincrements for a primary key.
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

Ok, I understood, I think that internally the SQL engine does what it takes to optimize table access.
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

In fact, this field is calculated by my program, no user adds records.

I explain:

In my program which reads my music (and other functions which I added, following the various exchanges which I had on the forum), when the coding of the mp3 was done in variable, the indicated duration is not not right. In this case, when the music in question is first played by my program, at the end it stores the correct duration in the table. The following times, it will look for the correct duration in the table to display it.
The key (which my program calculates) is, in fact, the full path of the mp3 file (directory, sub-directory,... name of the mp3 file) which I purify of special characters.
As there cannot be 2 mp3 files with exactly the same name in the same directory, there is no risk of duplicates.
User avatar
skywalk
Addict
Addict
Posts: 3972
Joined: Wed Dec 23, 2009 10:14 pm
Location: Boston, MA

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by skywalk »

You should rename this topic.
Optimize SQL query - how?
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how?

Post by jak64 »

OK, I did.
User avatar
NicTheQuick
Addict
Addict
Posts: 1223
Joined: Sun Jun 22, 2003 7:43 pm
Location: Germany, Saarbrücken
Contact:

Re: Optimize SQL query - how?

Post by NicTheQuick »

Every column that is either primary or has an index, can be accessed very fast. The database engine then automatically creates fast search indexes. How exactly these indexes look like depends usually on the datatype of that column and the way you define that index.

You can also create multicolumn indexes which only make sense if you have WHERE clauses with all these column involved together. If you for example have a table with two columns for the first and last name of a person and you always write SQL statements where you match for both of these columns, a multicolumn index is faster that two separate indexes for each of both columns.

Keep also in mind that you can create indexes that enable you to search for strings ignoring their case. Before I explain that any further, just read this stackoverflow question and answer to understand: https://stackoverflow.com/questions/449 ... ate-nocase
The english grammar is freeware, you can use it freely - But it's not Open Source, i.e. you can not change it or publish it in altered way.
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

Hello NicTheQuick,
Thank you for all these explanations.
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by infratec »

No SQL SELECT, no further help possible.
We are not visionaries.
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

Here is my query, I don't know if it is optimized (I am French and the comments and variable names are in French)

Code: Select all

            ;= Chercher si cette musique est déjà dans la base de données
            Requete="SELECT * FROM MUSIQUES WHERE CHEMIN_ET_TITRE_COMPLET_EPURE=" + "'" + CheminEtTitreCompletEpure + "'"
            TitreTrouve=#False  
            If DatabaseQuery(Base, Requete) <> 0 ; La requête s'est bien déroulée
              While NextDatabaseRow(Base)
                Duree=GetDatabaseString(Base, 2)
                GenreFavori=GetDatabaseString(Base, 3)
                TitreTrouve=#True
              Wend
              FinishDatabaseQuery(Base)
              If Not TitreTrouve
                ;= Ce titre n'est pas dans la base, il va être ajouté
                ChercherDureeMp3(CheminEtTitreComplet)
                Duree=DureeMp3
                Requete= "INSERT INTO MUSIQUES (CHEMIN_ET_TITRE_COMPLET_EPURE, DUREE, GENRE) VALUES (" +
                         "'" + CheminEtTitreCompletEpure  + "'," +
                         "'" + Duree  + "'," +
                         "'" + Genre + "'" +
                         ")"
                If DatabaseUpdate(Base, Requete) = 0
                  MessageRequester("Base","Erreur ajout nouveau titre dans la table MUSIQUES - " + DatabaseError())
                  End
                EndIf 
              EndIf
            Else
              MessageRequester("Base", "Erreur lecture table MUSIQUES - " + DatabaseError())
              End
            EndIf   
I'm French
jak64
Enthusiast
Enthusiast
Posts: 502
Joined: Sat Aug 15, 2020 5:02 pm
Location: Ciboure (France)

Re: Optimize SQL query - how? (was: SQlite and primary key)

Post by jak64 »

To display more than 6000 songs in a ListIconGadget, it takes less than 2 seconds, I think it's very good (even if it's the first execution of the program after starting the computer).
Post Reply