SQLite Forum

Timeline
Login

50 most recent events by user slavin occurring on or after 2021-11-20 20:51:19.

More ↑
2022-01-04
12:18 Reply: Failing builds on Apple M1 due to CPU arch and GNU Coreutils artifact: 992dff132f user: slavin

It seems your problem is with homebrew, not SQLite. The SQLite team's recommended way to build SQLite is here:

https://www.sqlite.org/howtocompile.html

You can build SQLite projects with homebrew if you like, but if you have problems you're going to need someone who understands homebrew.

2022-01-03
09:43 Reply: Reading SQLite database by using C Language artifact: 43935bc7fb user: slavin

It looks like you're trying to run the database file rather than the program you compiled. Your copy of Linux/Unix is unable to run that file, since it – correctly – isn't marked as executable.

What command are you typing at your bash prompt ?

2022-01-01
09:53 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? artifact: 458af611da user: slavin

Yeah, you're right. My post was a mess and I didn't understand your C code properly. I'm sorry about that.

I'm hoping that Ryan's has put his finger on the problem.

03:49 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? artifact: 4582af9296 user: slavin

It looks like you said you're checking it, but your code shows you checking the errors from sqlite_open_v2(), not the sqlite3_exec() on ATTACH.

What does sqlite3_extended_errcode() return when the ATTACH fails ? Is it SQLITE_OK ?

02:54 Reply: My code's ATTACH will not create a new db but sqlite3's shell will? artifact: ee9e157cd3 user: slavin

Does your program have permission to create new files in that folder ? Try making it create a new text file in the same folder.

2021-12-31
17:46 Reply: Question about sqlite backup api artifact: c1408ac3c3 user: slavin

As Larry wrote, the backup API represents the contents of the database. Not what appears in the database file on disk.

2021-12-29
01:52 Reply: try to insert dollar sign and percentage chars artifact: ba7d2b73c0 user: slavin

This is a problem with your shell, which I assume is bash, not with SQLite. SQLite has no problem with any character except for the single apostrophe, which is the character used to enclose text. bash uses dollar signs to indicate variables.

00:35 Reply: How SQLite database can be survived from a power failure during extending the page count ? artifact: 239fbf8f3a user: slavin

SQLite does numerous things to make sure that it will lose data as seldom as possible, and yield a corrupted database even less often. Your suggested change would make sense in a simple setup (e.g. a text file) but SQLite keeps change data in a journal file until it is sure it has updated the database file. A journaling file system can help. But SQLite just by itself is unusually resistant to many forms of hardware failure.

You might like to read this page

https://sqlite.org/transactional.html

and follow some links, then think through what would actually happen after a power failure. If you still have questions after that, please don't hesitate to reply to this thread.

2021-12-28
04:40 Reply: Pass a directory variable to .read? artifact: 34cc22aa43 user: slavin

With regard to 'a better way' in your post, I would recommend you learn how environment variables work in whatever shell you're using. You may be able to do things like

export SQLDIR="data"
echo "Working in directory $SQLDIR ."
sqlite3 test/$SQLDIR/test_people.db < $SQLDIR/write_people_db.sql

This example would work only in bash. Other shells may use a different format for setting and referring to variables. Read up on whatever shell you're using.

2021-12-24
03:49 Reply: Selecting certain sub string artifact: d2bbccf9fe user: slavin

There is no simple function, or simple combination of functions, in SQLite which can do what you want.

You could write such a function in your favourite language, and get SQLite to load it:

https://sqlite.org/c3ref/create_function.html

Note that your favourite language library or shim make have a version of sqlite3_create_function() that looks more like that language and would be easier for you to use.

But frankly I'd just have SQLite return that entire pathfilename string and process it in my software..

2021-12-21
16:42 Reply: Distributed backups from a database of databases artifact: 8673df4f53 user: slavin

A closed SQLite database all fits in one file. So just write code to read that file and store it however you want to store it, probably as a sequence of octets. Be certain that the file is closed - there are no open connections to it.

An alternative way to do it is to have SQLite serialize the database:

https://www.sqlite.org/c3ref/serialize.html

This doesn't need the database to be closed, but it takes up a lot of memory.

By the way, instead of storing the SQLite databases in a MySQL database, had you considered storing the SQLite databases in a SQLite database ?

00:03 Reply: Rebind for NULL values artifact: 7bc5e0cf5e user: slavin

You ran into the specific meaning of NULL which was used when SQL was devised. It does not match the value 'null' as used in C. So please think of NULL has having a specific useful meaning. A bit like zero being a number, but not normally seen in the 'number of items' field.

So you can bind a parameter to null in the C sense. Or you can bind the same parameter to NULL in the SQL sense. And they should do two different things.

2021-12-18
21:58 Reply: \n breaks reading from SQLITE3 artifact: c91fd41791 user: slavin

Okay. So you're talking about the SQLite3 Command Line Shell, not sqlite3 itself.

You can change the characters that program uses using various dot commands. See the documentation at

https://sqlite.org/cli.html#changing_output_formats

13:33 Reply: \n breaks reading from SQLITE3 artifact: 180b7c0cf7 user: slavin

I'm confused about what you mean when you write "everything breaks". What is broken in what you just showed us ?

If you put a return character inside one of the text fields. You should find that it gets converted to a return character and will be shown as such in the output. You do need to be very sure that some part of your input process converts the two characters 'n' into a return character. There is no part of SQLite which does this, it has to be done in your code.

it might help you understand what you're seeing if before the SELECT line you enter the command '.mode table'. I'm not sure how you'd to that in whatever Linux shell you're using, but it should work if you type it in an interactive session.

00:28 Reply: \n breaks reading from SQLITE3 artifact: f459347f15 user: slavin

SQLite has no problem with the newline character inside test strings. It doesn't even notice that its an unusual character.

You have one of two problems. Either the correct string is not being stored in the database, or the correct string is being stored, but your process of reading it back out is returning an incorrect string to your program. You can use the SQLite Command Line Shell to find out which of these is happening. The Command Line Shell shows the strings exactly as they are stored in the database.

https://sqlite.org/cli.html

How are you calling SQLite ? Are you using the C API ? Or are you using a library for your favourite programming language ? If so, which language and library are you using ?

2021-12-17
17:14 Edit reply: Trying to "Open Database" Safari History.db file and get Error: "could not open safari database file reason: unable to open.. artifact: c20230743f user: slavin

Works find for me when I use the Command Line Shell provided by the sqlite3 team.

Quit Safari. Copy the History.db file somewhere else. (You can restart Safari now if you want.) Open the copy you made with the sqlite3:

simon@183 Desktop % sqlite3 History.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> .mode table
sqlite> SELECT * FROM history_items LIMIT 3;
+--------+---------------------------------------+------------------+-------------+--------------------+---------------------+-----------------------+---------------------------------------+-------------------+-------------+
|   id   |                  url                  | domain_expansion | visit_count | daily_visit_counts | weekly_visit_counts | autocomplete_triggers | should_recompute_derived_visit_counts | visit_count_score | status_code |
+--------+---------------------------------------+------------------+-------------+--------------------+---------------------+-----------------------+---------------------------------------+-------------------+-------------+
| 201295 | https://slashdot.org/                 |                  | 7197        | ?                  | a???                |                       | 1                                     | 5100              | 0           |
| 201315 | https://news.ycombinator.com/         | news.ycombinator | 4837        | ?                  |                     |                       | 1                                     | 5100              | 0           |
| 201321 | https://www.reddit.com/               | reddit           | 11152       | }                  | `	             |                       | 0                                     | 5920              | 0           |
+--------+---------------------------------------+------------------+-------------+--------------------+---------------------+-----------------------+---------------------------------------+-------------------+-------------+
sqlite> .quit
17:13 Reply: Trying to "Open Database" Safari History.db file and get Error: "could not open safari database file reason: unable to open.. artifact: 4183ccda91 user: slavin

Works find for me when I use the Command Line Shell provided by the sqlite3 team.

Quit Safari. Copy the History.db file somewhere else. (You can restart Safari now if you want. Open the copy you made with the sqlite3:

simon@183 Desktop % sqlite3 History.db
SQLite version 3.36.0 2021-06-18 18:58:49
Enter ".help" for usage hints.
sqlite> .mode table
sqlite> SELECT * FROM history_items LIMIT 3;
+--------+---------------------------------------+------------------+-------------+--------------------+---------------------+-----------------------+---------------------------------------+-------------------+-------------+
|   id   |                  url                  | domain_expansion | visit_count | daily_visit_counts | weekly_visit_counts | autocomplete_triggers | should_recompute_derived_visit_counts | visit_count_score | status_code |
+--------+---------------------------------------+------------------+-------------+--------------------+---------------------+-----------------------+---------------------------------------+-------------------+-------------+
| 201295 | https://slashdot.org/                 |                  | 7197        | ?                  | a???                |                       | 1                                     | 5100              | 0           |
| 201315 | https://news.ycombinator.com/         | news.ycombinator | 4837        | ?                  |                     |                       | 1                                     | 5100              | 0           |
| 201321 | https://www.reddit.com/               | reddit           | 11152       | }                  | `	             |                       | 0                                     | 5920              | 0           |
+--------+---------------------------------------+------------------+-------------+--------------------+---------------------+-----------------------+---------------------------------------+-------------------+-------------+
sqlite> .quit
17:07 Reply: Trying to "Open Database" Safari History.db file and get Error: "could not open safari database file reason: unable to open.. artifact: 437d76cba3 user: slavin

You're right. Thanks for the correction.

16:48 Reply: Trying to "Open Database" Safari History.db file and get Error: "could not open safari database file reason: unable to open.. artifact: 4d41b15f5f user: slavin

That's not an error message. It's a comment to a line in a C program.

2021-12-16
14:39 Reply: Sqlite connection management with multiple client access? artifact: 931302fce9 user: slavin

Because it's important, and may be the cause of your problem, can you tell us which of the many things called 'RMI' you're referring to ?

Are you using a real computer which you have physical access to, or some kind of virtual setup, as in a Virtual Machine or AWS/GCP/Azure/VMware ?

Does that real computer have a real storage device inside or next to it, accessing it via storage calls, or it it storing the database on a virtual or network drive ?

2021-12-15
00:14 Reply: Backup API with network filesystem copy artifact: bc44bb9f50 user: slavin

I think from your question that you are backing up from a local source to a network destination.

The reason SQLite users are told to avoid network file systems is that the locking systems are often faulty. If the backup is backing up to a network file system, and nothing is trying to open the backup target, there's no contention for access to the target, so you should be okay.

On the other hand, if something is trying to open the backup target while the backup is proceeding, you may run into the problems. To minimise problems I would delete or rename any file with the same path/filename as the target before you start the backup. Though that may not be sufficient to avoid problems.

2021-12-14
14:32 Reply: Sqlite connection management with multiple client access? artifact: b6a1bc7136 user: slavin

If I understand this correctly, you have one (old C) application making changes to the database, and many (new Java) applications trying to read from the same database, all at the same time. Or possibly the Java programs do writing too. The most likely scenario is that one or other application was not read with multiple simultaneous access in mind. It is not being 'polite' by committing its transactions as soon as possible.

SQLite does let simultaneous access happen, and there's no reason why WAL mode would prevent it, but the people who programmed the applications have to have had this in mind when they wrote the software: rather than optimize each program for speed, they have to optimize each program for leaving the database free. This means using transactions properly: BEGIN, a short burst of commands, then COMMIT as soon as possible. Or for a single command you can rely on automatic transactions and not use either command.

It's possible that you're using a JAVA library which constructs its own transactions. Perhaps whoever used that library misunderstood the implications of this, or perhaps even the library is broken and was never tested to make sure it really did commit transactions. I don't know enough about common SQLite3 libraries to comment.

You might be able to use the SQLite CLI to access the same database, and use that to test whether the above is true. You can use BEGIN and COMMIT commands around read/write commands to keep the database locked in different ways, and see whether you can simulate what you're seeing in your applications.

2021-12-13
20:16 Reply: FYI: binary dump artifact: 612faafe92 user: slavin

Given the idea that indexes take up space, and that you're trying to save space, here's an idea: potential indexes.

CREATE POTENTIAL INDEX album_ISAN ON album(ISAN)

(Note that you cannot combine POTENTIAL with UNIQUE.) This has SQLite store the CREATE command but not actually create the index until it decides it would be the right one to use. At that point, the index would be created, stored and used. The VACUUM command would not preserve these indexes, just the command which would allow them to be recreated. Or perhaps that should be an option for the VACUUM command.

A way for one programmer to implement something like this, without changing SQLite itself, would be, before taking the copy, to write a routine to store CREATE INDEX commands which don't have UNIQUE in a table, then delete the index, before doing the VACUUM. Their routine to open the database would check the table then CREATE all these indexes using IF NOT EXISTS.

Top-of-my-head initial thoughts on the subject. POTENTIAL is probably a poor word for it, and there are probably better ways to implement the idea.

13:52 Reply: Database file name encoding -- is it really UTF-8? artifact: 025b2dec58 user: slavin

The open-file API can do its own re-encoding. You may pass it a parameter in UTF-16, but that doesn't mean it will use that form of the string when it calls the file system.

So yes, use UTF-8 as the documentation says. If you can show use that you did this and got the wrong result, then we'll take a look at it.

13:49 Reply: Importing XML artifact: 74f5713016 user: slavin

I want to endorse this solution. It does mean you'll have a two-stage process but both of those stages are very frequently done, and there are good, flexible, tools to do them.

2021-12-12
00:11 Reply: SQLite3 select (with table scan) not saturating NVMe throughput artifact: dce47cad4e user: slavin

My guess is that the difference between raw reading speed (what you'd get if you read a long text file) and what SQLite does is locking.

Can you try opening the file as read-only ? If that doesn't change anything can you try opening the file in immutable mode ? One of the following may be useful.

https://sqlite.org/c3ref/open.html

https://sqlite.org/uri.html

2021-12-11
13:20 Reply: Close/Dispose does not release lock artifact: 121264616d user: slavin

Please use Process Manager or the 'openfiles' utility to identify which process has your file locked. It might be the sqlite3 command line utility, but it might not. If this doesn't explain to you what's happening, please start a new thread for this, with the results of the above, plus the following.

When using your scripts please delete pp.csv before using each one.

Please tell us what 'dir' or 'ls' says about the file size of pp.csv once you have quit sqlite3. Are the two sizes reported as being the same ?

Also, as well as scripting sqlite with loadprices.txt, please try typing each set of commands manually and see whether that gives you the same result. Since you understand hex editors please check both versions of loadprices.txt to make sure whether both or neither end in a return character, or with the final 't' of '#end loadprices.txt'.

Please replace your SELECT command (or add a new one) with 'SELECT COUNT(*) FROM prices' and see whether the two versions give the same number.

When you say you 'sort' something, what does that mean ? Are you doing it in Excel or with a command-line utility ?

2021-12-08
16:02 Edit reply: Suggestions for learning SQL(ite) artifact: ef4e850766 user: slavin

Something which can be useful in these posts is to tell us what your favoured programming language is, and anything else about what you already know. For instance, if you know Python, this is pretty good

https://tech.marksblogg.com/sqlite3-tutorial-and-guide.html

A C programmer, on the other hand, might be more comfortable with this

https://zetcode.com/db/sqlitec/

It can also be useful to know if you're familiar with other SQL engines. I've seen pages that explain the differences between MySQL and SQLite, or between SQLServer and SQLite.

16:00 Reply: Suggestions for learning SQL(ite) artifact: c9213dd5d1 user: slavin

Something which can be useful in these posts is to tell us what your favoured programming language is. For instance, if you know Python, this is pretty good

https://tech.marksblogg.com/sqlite3-tutorial-and-guide.html

A C programmer, on the other hand, might be more comfortable with this

https://zetcode.com/db/sqlitec/

01:19 Reply: How to skip intermediate data of DB in file? artifact: 5ffb535839 user: slavin

That is actually what I wrote, and what I meant. I didn't write that the memory blocks would be released, just that they would no longer be occupied by SQLite data (or at least, not data SQLite was holding on to, or had allocated to itself). As you wrote, releasing memory allocated to a process is that process' problem.

2021-12-07
20:49 Reply: How to skip intermediate data of DB in file? artifact: 6e5d04211a user: slavin

SQLite does not use a background process or a server process. It does things only when you call one of the functions of the SQLite API. So if you want to "stop the stream of SQL-operations" just stop calling the SQLite API.

To clear memory of accumulated data of SQLite, close all SQLite connections. Note that you cannot properly close a SQLite connection unless all statements of that connection have had sqlite3_finalize() or sqlite3_reset() called on them.

2021-12-06
22:34 Reply: Venting about inconvenient limitations, feel free to ignore artifact: aaa0071309 user: slavin

Use either sqlite3_finalize() or sqlite3_reset() once you're done with your statement. Same advice we've always given.

Glad you found your bug.

22:31 Reply: SQLite on Windows for ARM artifact: 94659f0cfc user: slavin

Do you actually have a Windows VM that running on an M1 Mac ? If so, which one ?

There's nothing to prevent SQLite to run on such a thing. The SQLite amalgamation code runs on M1 Macs under macOS. SQLite runs on Windows VMs. In both cases you just have to add it to an appropriate project and use a compile that understands its C code, not C++ code.

22:27 Reply: Asking for code review as well proposing to add new extension that implements rot47 function artifact: 1571d1a861 user: slavin

It is customary for rot13() routines to operate on both upper case and lower case, even if the original spec was defined at a time lower case was not an issue. You may want to make sure that lower case letters are changed in the same way as their upper case equivalents.

2021-12-03
17:07 Reply: SQLite_Master data out of nowhere artifact: 3eb931ddda user: slavin

What you're doing should not cause the behaviour you're seeing. But perhaps it does explain why SQLite does have the sqlite_master table handy when it wants to corrupt your file. It should not be possible to corrupt your database by using VACUUM, and if you stop using VACUUM it probably just means that your data-corruption bug remains, and it'll overwrite your database with something else instead.

There's nothing wrong with VACUUM. It has no reputation for being buggy. But in a production setting it's normally used only for special occasions like a quarterly maintenance routine. SQLite automatically reuses any space saved by deleting data, and the large amount of time VACUUM takes more than makes up for time saved during normal operations on all but the slowest rotating hard disk. In addition, it involves a lot of operations for SSDs and Flash memory, reducing the lifetime of such devices.

The only time I suggest VACUUM used in normal operation is if you are copying/transferring a copy of your database and you want it to be as small as possible so it can be copied/transferred as quickly as possible.

Was your problem a one-off event, or does it happen more often than that ?

2021-12-02
22:23 Reply: Will the database file be corrupted if using MEMORY journal mode ? artifact: 1ed143ec7a user: slavin

Good grief. I had been working on that assumption for years. Yet I now see in the file format there's only a way to saw whether WAL is being used or not. Thanks for the correction.

17:07 Reply: SQLite_Master data out of nowhere artifact: f285aadd6e user: slavin

Sorry, I have only bad news.

This isn't a commonly-found problem. The normal cause of these things is that something in your code is stomping on memory SQLite itself uses: a pointer gets the wrong value, or the wrong part of memory is overwritten. The cause can be a bug in your code, a bug in a library you're calling, a bug in your OS, or a hardware glitch. I'm not saying that there's 0% chance that it's a bug in SQLite, merely that I don't remember anyone else mentioning that problem.

Perhaps someone else can think of a reason why SQLite might want to read sqlite_master after the database has been opened. I can't. Mmmm … unless you do a VACUUM in your code.

16:57 Reply: Benchmarking SQLite on Apple's M1 ? artifact: 8eb4f275c0 user: slavin

Well that's great. Someone else did all the work.

I find the slot between Ryzen 7 and Ryzen 9 interesting. I didn't know what to expect.

12:58 Reply: SQLite WAL Pages Checkpointed artifact: 116a459453 user: slavin

Did you know about the session extension

https://www.sqlite.org/sessionintro.html

? It doesn't work the way your system does, but it's a supported part of SQLite, does the job efficiently, and doesn't require external access into a journal file.

12:53 Post: Benchmarking SQLite on Apple's M1 ? artifact: 6ebf797093 user: slavin

Triggered by Will Iverson's post about compiling for the M1 …

The codebase for SQLite is highly optimised, and written in C which is well suited to RISC optimisation. So compilation for the RISC platform of x86_64 should indeed produce 'crazy fast' results. Also, the M1 Macs are 'system-on-a-chip', which means that I/O operations aren't subject to such bad timing delays as they move around a motherboard. Since for most uses SQLite is I/O bound, this will also make a difference.

This should mean that SQLite running on M1 Macs should yield fast results. I have access to both the first M1 Pro MacBook Pro, and the last generation of Intel MacBook Pros, and was interested in comparative benchmarks. But I realised I didn't know enough to figure out what compilation command-line to use, or what suite to run as a benchmark. So I'm hoping someone else will do it. Or perhaps there's a document somewhere which lays out how to benchmark SQLite.

If this is what you were doing in the first place, Will, I'd love to see your results.

12:38 Reply: Will the database file be corrupted if using MEMORY journal mode ? artifact: d044303d88 user: slavin

Thanks for that information, which should make it easier for readers to figure out what changes might help you.

In your situation I would try three different journalling modes:

PRAGMA journal_mode = TRUNCATE
PRAGMA journal_mode = PERSIST
PRAGMA journal_mode = WAL

One of these modes may be faster than the others, or they may be the same, depending on what hardware, OS, and file system you're using. Remember, the journal mode is stored in the database so you need to issue those commands just once after database creation, not every time you open the database.

2021-12-01
13:29 Reply: Will the database file be corrupted if using MEMORY journal mode ? artifact: 18db58c61e user: slavin

Does your device generate write single commands at a regular interval, e.g. a data-logger, or a batch of write commands in a rush, and then a pause until the next batch ?

What medium is your database stored on ? Flash memory ?

2021-11-29
16:26 Reply: Need some help artifact: acc6156b95 user: slavin

One day, we should actually do someone's homework for them. You know … just to mess with them.

2021-11-26
16:02 Reply: sqlite wal policy is steal & force and has undo and no redo ? artifact: 34e0fa8231 user: slavin

SQLite's journal modes are a picky detail of the internal workings of SQLite. Switching from one journal mode to another does not change what commands a programmer can give SQLite, or what SQL commands SQLite can implement unless the programmer disables journalling entirely.

To find out SQLite's abilities relating to 'undo' and 'redo', read these two pages in the order I give them:

https://www.sqlite.org/lang_transaction.html

https://www.sqlite.org/lang_savepoint.html

2021-11-25
16:56 Reply: How to rerank one row in a big set of rows? artifact: 52544c7f4d user: slavin

Usually you don't store ranks. Calculate a rank by iterating through an index of scores until you find the row you want.

If you have a situation where it is useful to store ranks, and ranks don't change often, don't store ranks in the same table where your scores are stored. Make a special table for just id, score and rank, and have a procedure which deletes all the rows in the table and makes new ones. Call this procedure when a new set of scores are in.

16:51 Reply: Sqlite high IO load makes usage impossable artifact: 89a9324526 user: slavin

Most important and easiest:

If you have a known set of SELECT statements to execute, for example you know you're going to need to look up four values, put them in a transaction, i.e. surround them in BEGIN and END.

Less easy:

Where convenient, use a number of pre-prepared statements and just rebind and restep them, rather than preparing a new statement every time.

2021-11-23
21:49 Reply: Sqlite3 doesn't support row level locking? artifact: a6a973e2ac user: slavin

Are you inserting many rows in one big task ? If so, combine them in a transaction, by putting BEGIN before the first INSERT and COMMIT after the last one.

2021-11-22
13:51 Reply: Proposed slightly-incompatible change to date-time functions artifact: e550e68073 user: slavin

In new step 2 will you be detecting both 'unixepoch' and ' unixepoch' (space character before the word) ? If you're allowing both in a liberal manner, can you allow 'epoch' and ' epoch' too ? But keeping compatibility by allowing just 'unixepoch' is okay too.

What happens the value doesn't qualify for any of new steps 1 to 4 ? I suppose that would be either NULL or BLOB but perhaps there are other things. Perhaps the value is TEXT but the first character is not digit, plus, minus, or decimal separator.

2021-11-21
21:15 Reply: big performance hit when querying 2 values in a single query instead of two artifact: 0e26408526 user: slavin

One SELECT is meant to return a number of rows, and the values on each row relate to those rows. In other words, SQL does processing based on that idea: it tries to use one operation per table to get the results you asked for. Let's look at your SELECT:

select 'max id & count distinct:', max(id), count(distinct id) from pro_comment;

The max(id) only needs one row to be retrieved: the one which has the biggest value for id. Since there's an index on this value, it can do this by looking at whatever row is last in this index. Fast and easy to find.

But then in the same query you ask for count(distinct id). Which requires more than one row to be read from the table. It has to scan a whole index. Well, that's okay, it has the index it needs.

But in the combined query, SQL is meant to retrieve both these figures with the same operation. That could be a lot more complicated. Let's see what it really does. Let's look at EXPLAIN QUERY PLAN on all three of those queries:

sqlite> EXPLAIN QUERY PLAN select 'max id:', max(id) from pro_comment;
QUERY PLAN
`--SEARCH pro_comment USING COVERING INDEX pro_comment_id
sqlite> EXPLAIN QUERY PLAN select 'count distinct:', count(distinct id) from pro_comment;
QUERY PLAN
`--SCAN pro_comment USING COVERING INDEX pro_comment_id
sqlite> EXPLAIN QUERY PLAN select 'max id & count distinct:', max(id), count(distinct id) from pro_comment;
QUERY PLAN
|--USE TEMP B-TREE FOR count(DISTINCT)
`--SCAN pro_comment USING COVERING INDEX pro_comment_id

As you see, SQLite, in trying to get both figures from the same search, thinks it has to make a temporary index of the data. The solution is to accept that you want two different figures that aren't related, and ask for them in separate statements. Which is what you did yourself in your demonstration of the problem. Well done.

It could be that this particular optimisation is easy and fast to identify. In which case, the developers (who are reading your thread) might add it. But if you have SQLite check for every possible optimisation every time it does a search, every search is going to take a long time while SQLite checks to see if it qualifies for every possible optimisation. Which, for most SELECTs most of the time, will slow SQLite down.

2021-11-20
20:51 Reply: Language pairs strings how to artifact: ccd1c24c99 user: slavin

I don't think there is one solution which I could recommend in all cases. It comes down to different levels of data.

A) You could make two different database files. One has the strings in English, one in Spanish.

B) You could make one database with two tables: one table with the English strings, one with the Spanish strings.

C) You could make one table but have two different rows for each purpose: one row has the English strings, the other row has the Spanish strings.

D) Or you could have both languages in the same row:

CREATE TABLE IF NOT EXIST Headers (HE1, HE2, HE3, HS1, HS2, HS3);

Part of it is about whether you might add another language to your solution. For instance, if you might eventually add another language, you would not want to use the HE1, HS2 solution because making the changes would be tedious.

More ↓