List:General Discussion« Previous MessageNext Message »
From:Marko Knezevic Date:November 23 2005 1:13pm
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 | 
> |
> +-----------------------+--------------+------+-----+---------------------+

> 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 

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