No announcement yet.

Create an index without collation

  • Filter
  • Time
  • Show
Clear All
new posts

  • Create an index without collation


    We have Navicat Premium Essentials 11.2.16 running on Mac El Capitan 10.11.6.

    We're trying to create indexes that use integer columns that do not have COLLATE on them.

    When we try to create any index we always seem to have to have COLLATE even when the affinity of the field is integer.

    The reason this is important is that having the collate on an integer field makes a massive difference to the searching speed.

    As an example, one of our tables has 180M rows. One of the columns is an integer column.

    If we create an index like this

    CREATE INDEX "Table1_Idx3" ON Table1 ("Id" COLLATE NOCASE ASC);

    and search the table it takes around 30 mins on our Mac.

    If we simply change the index to

    CREATE INDEX "Table1_Idx3" ON Table1 ("Id")

    it takes around 15 mins.

    We have been in touch with the SQLite developers and they think that clearly adding in the COLLATE NOCASE ASC on a non text field is not good.

    We cannot see anyway to create these simple indexes the way we think they should be created,

    Is there something in Navicat we are missing? Every index entry seems to want a COLLATE with it even when this is unnecessary.



  • #2
    Hi Rob,

    Could you please provide us screenshots showing the detail steps to create the index?