"Marko Knezevic" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
