> "Marko Knezevic" <marko.knezevic@stripped> wrote in message
> news:001601c5ef6f$0c9cd420$f40c0e0a@markonb...
> Here are my tables and queries i am running on them with index
> explanations.
> Hope this will help.
>
> ACCOUNTS TABLE:
> +-----------------------------+--------------+------+-----+---------------------+
> | Field | Type | Null | Key | Default
> +-----------------------------+--------------+------+-----+---------------------+
> | id | varchar(36) | | PRI |
> | assigned_user_id | varchar(36) | YES | MUL | NULL
> | deleted | tinyint(1) | | | 0
> +-----------------------------+--------------+------+-----+---------------------+
>
>
> USERS TABLE:
> +-----------------------+--------------+------+-----+---------------------+
> | Field | Type | Null | Key | Default
> |
> +-----------------------+--------------+------+-----+---------------------+
> | id | varchar(36) | | PRI |
> |
> +-----------------------+--------------+------+-----+---------------------+
> FIRST:
> Typically, you need indexes on fields that are specified in your
> JOIN/WHERE clause:
> accounts.assigned_user_id
> users.id
> accounts.deleted
We added those indexes and gained performance improvements. Thanx. Now we
are adding more indexes and examine queries.
> SECOND:
> Why are your "id" fields all VARCHAR(36)? These are huge keys! I would
> recommend using an INT as the id (that allows over 4 billion unique id's).
> If you have some internal id that is 36 characters long, have two id
> fields -- one for the relational-key-id (INT), and then your long string
> id for your clients to use.
Well, this is "feature" of SugarCRM (http://www.sugarcrm.com) they use
varchar because they are using UUID() as ID, i don't know why, but i presume
that they want to have every ID in their DB to be unique, so that's
explanation for varchar.
Regards
Marko
