SQLite/Android – First Time Using an Index

indexsqlite

I'm creating a SQLite Database for Android. I can do fairly simple SQL, but I haven't used an index before. I'm working on a table with >150,000 records and I've never dealt with a database so large. I know that I need to add at least one index to make my queries faster. Here is the table:

CREATE TABLE IF NOT EXISTS T 
(
    a TEXT, b TEXT, c INT, d TEXT // ... 14 more TEXT columns
)

My only queries on this table are only on a, b, and c like this:

SELECT * FROM T WHERE c = ?
SELECT * FROM T WHERE a LIKE ?% AND b LIKE ?%
SELECT * FROM T WHERE a LIKE ?%
SELECT * FROM T WHERE b LIKE ?%

This table is filled once, and no changes are made to it after that. It's acceptable to take some extra time after import to add the index.

Can some one more knowledgeable than me tell me what index or indices I should create and if I should wait until after filling the table to add them?

This is my first question on this site. I thank you for you looking at this for me!

Best Answer

WHERE c = ?

This lookup can be optimized with a simple index on the c column:
```
CREATE INDEX T_c on T(c);
```
WHERE a LIKE ? || '%'

There are several restrictions on indexes that can be used for LIKE searches. In this case, you need a case-insensitive index:
```
CREATE INDEX T_a_ci ON T(a COLLATE NOCASE);
```

WHERE b LIKE ? || '%'

same here:

CREATE INDEX T_b_ci ON T(b COLLATE NOCASE);

WHERE a LIKE ? || '%' AND b LIKE ? || '%'

The database can use at most one index per table in a query, so it will use one of the indexes above. If you have run ANALYZE, it might be able to choose the better one.

To check which indexes are actually being used, use EXPLAIN QUERY PLAN.

Whenever you change tables, any indexes must be updated too. When you're filling an empty table, it is usually a better idea to create indexes afterwards.

When your application does many updates to some table, it might be worthwhile omitting some index(es) that are useful only for seldomly-used queries.

Related Solutions

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

SQLite Error – No Such Column TABLE.ROWID

your first query is standard SQL (the parentheses are redundant, only needed by MS-Access) and should work just fine in SQLite, with or without the parentheses:

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM
        Message 
    INNER JOIN 
        ( Contact INNER JOIN contact_m2m_message 
            ON Contact.contactID = contact_m2m_message.contactID
        )
      ON Message.msgID = contact_m2m_message.messageID ;

Edit: Actually, SQLite needs the parentheses, too, at least the version provided in SQL-Fiddle.

The 2nd query is not valid because you have not provided an alias for the derived table:

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM 
        Message 
    INNER JOIN 
        ( Select Contact.contactNumber, Contact.contactName 
          FROM Contact INNER JOIN contact_m2m_message 
               ON Contact.contactID = contact_m2m_message.contactID
        )     --<-------------------------------------- no alias
            ON Message.msgID = contact_m2m_message.messageID;

To make it work wirh a derived table, you have to provide an alias for the derived table and also add the contact_m2m_message.messageID in the SELECT list of the derived table (and remove columns that are not used):

SELECT 
    Message.messageContent, Message.messageDateTime 
FROM 
        Message 
    INNER JOIN 
        ( Select -- Contact.contactNumber, Contact.contactName,
                 contact_m2m_message.messageID 
          FROM Contact INNER JOIN contact_m2m_message 
               ON Contact.contactID = contact_m2m_message.contactID
        ) AS cm
            ON Message.msgID = cm.messageID;

But I don't think you should use that. The first query should work. You can re-arrange the order how of the tables are joined. Using table aliases also helps in readibility:

SELECT 
    m.messageContent, m.messageDateTime 
FROM 
    Message  AS m
  INNER JOIN
    contact_m2m_message  AS cm
      ON m.msgID = cm.messageID 
  INNER JOIN
    Contact  AS c 
      ON  c.contactID = cm.contactID ;

Best Answer

Related Solutions

Mysql – Adding index to large thesql tables

SQLite Error – No Such Column TABLE.ROWID

Related Question