Feature Question: Can you create indices/indexes with orm on sync()

[mspisars]

I'm coming from a SQLAlchemy environment and was wondering if there is a way to defined indexes on the model so that when I call db.sync() they are created after the table definition?

BTW, one of my main reasons for looking at orm is the performance... I updated sequalize's performance repo to the latest here mspisars/sequelize-performance@7721530 and orm is by far the fastest!

So, keep up the awesome job! People seem to ignore performance nowadays by throwing more power at the problem...

[dresende]

Thank you, I never tested performance :)

Indexes are created for the primary key and associations but I have plans to add them to other properties. I'm going to make some tests and link the commits here for you to try them.

[dresende]

This adds indexes on single properties. Perhaps another way of adding indexes on groups of properties is also needed, I'm not sure the best and simple syntax to do it (in orm).

[dresende]

See the notes on the latest commit and see if it fits your needs :)

[mspisars]

This is great... while we're on indexes.
Tested both PostgreSQL and MySQL, do we need to create the index for "id" since it is a primary key?

t=# \d person
                                  Table "public.person"
  Column   |          Type          |                      Modifiers
-----------+------------------------+-----------------------------------------------------
 id        | integer                | not null default nextval('person_id_seq'::regclass)
 name      | character varying(255) |
 surname   | character varying(255) |
 age       | real                   |
 male      | boolean                | not null
 continent | enum_person_continent  |
 photo     | bytea                  |
 data      | bytea                  |
Indexes:
    "person_pkey" PRIMARY KEY, btree (id)
    "person_id_idx" btree (id)

and

mysql> show indexes from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| person |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |
| person |          1 | id       |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

The above is just a base test with the example data from README.md

[dresende]

I'm not very familiar with postgresql, I don't know if it's necessary or not. Are you sure it's not necessary since the id column is a SERIAL?

[mspisars]

I don't think it is needed for PostgreSQL, but I am not sure about MySQL or SQLite so I guess that was more of a question.
The index creation syntax looks good, for both multi and single column.
Thank you for the quick turn around.

[dresende]

MySQL doesn't need it because of the PRIMARY column. I guess PostgreSQL doesn't also because of SERIAL. I'm going to read about it and remove if not needed.

[mspisars]

Sounds good.