"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 | |
+-----------------------+--------------+------+-----+---------------------+
SELECT users.user_name assigned_user_name, accounts.* FROM accounts LEFT
JOIN users ON accounts.assigned_user_id=users.id where accounts.deleted=0
ORDER BY name asc LIMIT 20,20
Explain says:
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,accounts,index,NULL,Name,151,NULL,888466,Using where
1,SIMPLE,users,eq_ref,PRIMARY,PRIMARY,36,sugarcrm.accounts.assigned_user_id,1,
I have also other SELECTS but with different ORDER BY's (this select uses
name, but also it could be sorted on city, state, phone_fax, phone_office,
phone_alternate..)
FIRST:
Typically, you need indexes on fields that are specified in your JOIN/WHERE
clause:
accounts.assigned_user_id
users.id
accounts.deleted
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.
DanB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
