SQLite3 takes more time to retrieve certain fields for the same indexed query-CodePudding

I am querying a table with index:

CREATE TABLE hwmd (cycle INT NOT NULL, id INT NOT NULL, ip INT NOT NULL, name CHAR(20) NOT NULL, uid INT NOT NULL, bin INT NOT NULL, num_data INT NOT NULL, task INT NOT NULL, utid INT NOT NULL, term_op INT NOT NULL, context INT NOT NULL);

CREATE INDEX idx_hwmd_id ON hwmd (id)

I make 2 queries on the same table with drastically varying speeds:

SELECT cycle, id FROM hwmd WHERE id = 100; // 38ms
SELECT cycle, id, ip FROM hwmd WHERE id = 100; // 1089ms

The second query that includes ip (INT field) takes 1089ms whereas the first query takes only 38ms.

I confirmed both queries use the same index:

sqlite> explain query plan SELECT cycle, id FROM hwmd WHERE id = 100; 
QUERY PLAN
`--SEARCH hwmd USING INDEX idx_hwmd_id (id=?)

sqlite> explain query plan SELECT cycle, id, ip FROM hwmd WHERE id = 100; 
QUERY PLAN
`--SEARCH hwmd USING INDEX idx_hwmd_id (id=?)

How can I explain these results? Why should the second query take so long? What can I do to fix it? Thanks

CodePudding user response：

Appreciate that the time for your query to execute includes the amount of latency in sending the result set from SQLite to whatever is calling the query. If we again compare your two queries:

SELECT cycle, id FROM hwmd WHERE id = 100;     -- 38ms
SELECT cycle, id, ip FROM hwmd WHERE id = 100; -- 1089ms

we can see that they differ only in that the second query selects an additional ip column. I would explain the difference in performance as being due to the extra latency in sending across the IP address field.

To get a better comparison of two queries, in general, use the EXPLAIN command with your database. This will only show the work needed to execute a query on the database itself, neglecting network latency.

CodePudding user response：

I was able to solve this mystery with the help of SQLite forums. The way to check query time only is to send the output of the query to /dev/null. In this particular case, printing to console was taking a long time.

sqlite> .once /dev/null
sqlite> select * from hwmd where id = 100;
Run Time: real 0.048 user 0.032339 sys 0.015133

Query executes in 48ms which is as expected.