SQLite千万级表连接耗时久，SQLite Studio执行时长显示异常咨询

Hey there, let's dig into your SQLite performance issues and break down each of your questions with concrete explanations based on your query plan and setup.

Looking at your EXPLAIN QUERY PLAN output:

5 0 0 SCAN TABLE dsf AS d；7 0 0 SEARCH TABLE secd AS s USING INDEX secd_datadate (datadate=?)

The biggest red flag here is the full table scan on dsf (your 100-million-row table). For every single row in dsf, SQLite does an index lookup on secd for matching datadate values—but then it has to manually filter those results with substr(s.cusip,1,8)=d.CUSIP.

Since you don't have an index that covers both datadate and the first 8 characters of cusip, SQLite can't directly locate rows that satisfy both conditions. Instead, it pulls all secd rows with a matching datadate, then checks each one's cusip substring individually. Multiply that by 100 million rows from dsf, and you get the hours-long runtime.

Even when you removed the substr() and got the same results, if your d.CUSIP is exactly 8 characters and s.cusip starts with those 8, you still weren't using an index for the cusip match unless you had a combined index.

Linear-time joins only happen in ideal scenarios (like merge joins with properly sorted/combined indexes, or hash joins with enough memory). Here's why that's not happening for you:

• You’re doing a full scan of dsf—no index is being used to filter or order this table, so SQLite has to process every single row.
• Your secd index is only on datadate, not a combined index of datadate + the first 8 characters of cusip. Without this, SQLite can't jump directly to rows that meet both join conditions; it has to do extra filtering work after the initial index lookup.
• SQLite's query optimizer might not be choosing the most efficient join algorithm (like hash join) if it doesn't have enough memory or table statistics to make that call.

This is almost certainly a quirk in how SQLite Studio measures query time. Most SQL clients track two types of time:

1. Engine execution time: The time SQLite itself spends processing the query (CPU work, not waiting for disk).
2. Total client time: The full time from when you hit "run" until the last result is displayed (including disk IO waits, data transfer, and client-side rendering).

SQLite Studio is probably only showing you the engine execution time—but the bulk of your wait time comes from slow disk IO (your disk is only moving 7-8MB/s, typical for a slow mechanical drive). SQLite spends most of its time waiting for data to be read from disk, and that wait time isn't counted in the studio's reported runtime.

For your LIMIT 10000 query, SQLite might have found the 10k matching rows relatively quickly in terms of CPU work, but the disk took hours to feed all the necessary data to the engine.

When you ask SQLite Studio to count the total number of matching rows, it can't pull that number from an index directly—complex joins with filters require SQLite to execute the entire query without the LIMIT to count every matching row.

Since your base query already takes hours to run, doing it again (without stopping at 10k rows) means redoing all that full-table scanning and filtering work. Additionally, SQLite doesn't cache the full result set of your initial LIMIT query, so it has to start from scratch.

Here are actionable steps to speed things up:

1. Create a combined function index on secd:

   CREATE INDEX idx_secd_datadate_cusip8 ON secd(datadate, substr(cusip, 1, 8));
   

   This lets SQLite directly look up rows that match both datadate and the first 8 characters of cusip without manual filtering.
2. Add an index to dsf.DATE:
   If you can filter dsf with a WHERE clause (e.g., only recent dates), an index on DATE will let SQLite skip scanning the entire table. Even without a filter, an index might help the optimizer choose a better join order.
3. Upgrade to an SSD:
   Your disk is a major bottleneck here. An SSD can read data at hundreds of MB/s, which will drastically cut down on disk wait times.
4. Increase SQLite's cache size:
   Tell SQLite to use more memory for caching to reduce repeated disk reads:

   PRAGMA cache_size = 1000000; -- 1 million pages = ~4GB of cache (adjust based on your available RAM)
   

内容的提问来源于stack exchange，提问作者Kalev Maricq