Very good points, Corentin and Surmulot.
From everything that was corrected, getting the 3.5.6 version is the best thing to do even if breaks compatibility with older projects. There's a lot of good stuff added and even a lot of memory, I/O and security flaws corrected.
^ and encryption
About Indexes :
When you insert data into your tables it's written in the order you create it, for example if you create a table with highscores :
Corentin 0
Locaz00 1
byo 2000
Surmulot 100
The data doesn't have any logical order. Thus when you search for the highest score you have to loop through every row, and the same problem appears if you're looking for Surmulot's score : you'll have to loop through every players' score... Having an index on a table is like creating a dictionary on this table : CREATE INDEX PlayerHighscore ON Highscores(Player) will speed up queries where you are looking for Highscores.Player , just like if you were searching for Locaz00 in the dictionary : you don't have to read every word to find your name. CREATE INDEX ScoreIndex ON Highscores.Score will speed up searches on the Score field.
It's not like creating a whole ordered table it's just that you have an index on field-values making searching far easier. You could think you should create indexes everywhere, but you definitely shouldn't, actually creating the good indexes is a difficult task. If you have an index or more on a table, any insertion on this table will be much slower, because your SQL server will have to keep indexes up to date. So, you have to think carefully on where to use indexes. Some people use the EXPLAIN function to see if it's useful, since I never understood anything about EXPLAIN function, I mostly compare execution time of my queries to see if indexes are necessary.
There is one sure thing about where to use indexes : whenever a field is used as a junction, an index will dramatically speed things up.
EDIT : You destroy an index using DROP INDEX "name"
Good information, Corentin. This will benefit many users.
Since we're talking about SQLite object, i'll just add this piece of information most people doesn't seem to know :
SQLite DB object can handle in-memory databases ! When you open the database just enter ":memory:" and the database will be created in memory. This of course makes everything much faster. The only problem is that we can't load/save databases from HD. I never tried it but if you really need to load/save to memory, maybe you can ATTACH/ DETACH DATABASE, never tried this trick.
In MMF 1.5, I made some tests to see which data-storing extension was the fastest, and SQLite seemed to be faster than every object I tried (INI, Arrays ...). If you still need to accelerate things, you can often use transactions, which are faster because they only access HD one time for the whole transaction.
Thank you, Corentin. Very interesting !
I'm currently developing an updated SQLite3 extension. It's close to a release. It uses the 3.5.6 version and I included some requested actions like "Rebuild table without columns", "Save results to file" and "Add columns to table". i'm currently hunting bugs right now.
This is absolutely great news byo !
If you could add the ability to load/save database to/from memory, it would be great ! Though SQlite2 always did the job very well, an sqlite 3 extension is a nice step forward.
If you need help de-bugging I'd be glad to contribute, though i don't have much free time at the moment.
It can currently load a database into memory and you can do all the operations there. I have yet to implement something like "Save file from memory database".
I could send the BETA version to you if you wish to test it. Thanks for the interest.
Great news ! I sent you a PM.
Posting Permissions
Reply With Quote