Optimize SQL query - how? (was: SQlite and primary key)
Optimize SQL query - how? (was: SQlite and primary key)
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.
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.
Re: Optimize SQL query - how? (was: SQlite and primary key)
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:
If you have a key/index on the field name, the SQL cmd is faster.
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
Re: Optimize SQL query - how? (was: SQlite and primary key)
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.
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.
Re: Optimize SQL query - how? (was: SQlite and primary key)
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.
But you don't understand my answer:
You need an ORDER BY clause for sorting.
A key/index alone has no influence on sorting.
Re: Optimize SQL query - how? (was: SQlite and primary key)
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...
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...
Re: Optimize SQL query - how? (was: SQlite and primary key)
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.
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.
Re: Optimize SQL query - how? (was: SQlite and primary key)
Ok, I understood, I think that internally the SQL engine does what it takes to optimize table access.
Re: Optimize SQL query - how? (was: SQlite and primary key)
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.
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.
Re: Optimize SQL query - how? (was: SQlite and primary key)
You should rename this topic.
Optimize SQL query - how?
Optimize SQL query - how?
The nice thing about standards is there are so many to choose from. ~ Andrew Tanenbaum
Re: Optimize SQL query - how?
OK, I did.
- NicTheQuick
- Addict
- Posts: 1223
- Joined: Sun Jun 22, 2003 7:43 pm
- Location: Germany, Saarbrücken
- Contact:
Re: Optimize SQL query - how?
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
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.
Re: Optimize SQL query - how? (was: SQlite and primary key)
Hello NicTheQuick,
Thank you for all these explanations.
Thank you for all these explanations.
Re: Optimize SQL query - how? (was: SQlite and primary key)
No SQL SELECT, no further help possible.
We are not visionaries.
We are not visionaries.
Re: Optimize SQL query - how? (was: SQlite and primary key)
Here is my query, I don't know if it is optimized (I am French and the comments and variable names are in French)
I'm 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
Re: Optimize SQL query - how? (was: SQlite and primary key)
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).