List:General Discussion« Previous MessageNext Message »
From:Dan Baker Date:November 22 2005 3:42pm
Subject: Re: Tips for better performance
View as plain text  
"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 |                     |
+-----------------------+--------------+------+-----+---------------------+


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 



Thread
Tips for better performanceMarko Knezevic22 Nov
  • Re: Tips for better performanceDuncan Hill22 Nov
  • Re: Tips for better performanceScott Haneda22 Nov
  • Re: Tips for better performanceMarko Knezevic22 Nov
    • Re: Tips for better performanceDuncan Hill22 Nov
  • Re: Tips for better performanceRonan Lucio22 Nov
  • Re: Tips for better performanceMartijn Tonies22 Nov
  • Re: Tips for better performanceRonan Lucio22 Nov
  • Re: Tips for better performanceMartijn Tonies22 Nov
  • Re: Tips for better performanceMarko Knezevic22 Nov
  • Re: Tips for better performanceMarko Knezevic22 Nov
  • Re: Tips for better performanceDan Baker22 Nov
  • Re: Tips for better performanceRonan Lucio23 Nov
  • Re: Tips for better performanceMarko Knezevic23 Nov
  • Re: Tips for better performanceMarko Knezevic23 Nov
  • Re: Tips for better performanceRonan Lucio23 Nov