sqlite - index in table not found

dorival · 2015-08-27 18:35:03

My system started to slow performance... and investigating the slow performance, I not found a index in a table.
In my model, I have:

  TMyTable = class( TSQLRecord )
    ...
  published
    property Field1 : Integer          read FField1 write FField1 stored AS_UNIQUE;
    property Field2 : Integer index 10 read FField2 write FField2;
  end;

and in my database, in the dump file I found just de index of "Field1". The second index not exist (at least I didn't find it).
I created the index with command below:

CREATE INDEX IF NOT EXISTS IndexMyTableField2 ON MyTable(Field2);

and the system performance went back to normal...

Did I use de corret way to create a index (in the second example)?

Last edited by dorival (2015-08-27 19:15:51)

ab · 2015-08-28 06:52:12

No, "index 10" won't be used at all to create an index.
"index ###" is used to define the field length of a VARCHAR column in external database, or identify a dynamic array property.

To create an index, you have to override the TSQLRecord.InitializeTable method.
See e.g.

class procedure TSQLArticle.InitializeTable(Server: TSQLRestServer;
  const FieldName: RawUTF8; Options: TSQLInitializeTableOptions);
begin
  inherited;
  if (FieldName='') or (FieldName='PublishedMonth') then
    Server.CreateSQLIndex(TSQLArticle,'PublishedMonth',false);
end;

See http://synopse.info/files/html/Synopse% … #TITLE_476

dorival · 2015-08-31 12:36:17

ok thank you ab

dorival · 2015-09-01 11:02:14

How can I force to run the method InitializeTable? (cause my model already created the table on database)

I was trying run CreateMissingTables (TSQLRestServerDB) with parameter user version and without options, but it not work - maybe I made confusion about that.

mORMot Open Source

#1 2015-08-27 18:35:03

sqlite - index in table not found

#2 2015-08-28 06:52:12

Re: sqlite - index in table not found

#3 2015-08-31 12:36:17

Re: sqlite - index in table not found

#4 2015-09-01 11:02:14

Re: sqlite - index in table not found

Board footer