List:General Discussion« Previous MessageNext Message »
From:Marko Knezevic Date:November 22 2005 2:13pm
Subject:Re: Tips for better performance
View as plain text  
> Marko,
>
> I have a table with 5,000,000 records that takes about 1 second
> to show the results.
>
> Perhaps your queries aren´t optimized properly.
>
> Ronan

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 
| Extra |

+-----------------------------+--------------+------+-----+---------------------+-------+

| id                          | varchar(36)  |      | PRI | 
|       |

| date_entered                | datetime     |      |     | 0000-00-00 
00:00:00 |       |

| date_modified               | datetime     |      |     | 0000-00-00 
00:00:00 |       |

| modified_user_id            | varchar(36)  |      |     | 
|       |

| assigned_user_id            | varchar(36)  | YES  | MUL | NULL 
|       |

| created_by                  | varchar(36)  | YES  |     | NULL 
|       |

| name                        | varchar(150) | YES  | MUL | NULL 
|       |

| parent_id                   | varchar(36)  | YES  |     | NULL 
|       |

| account_type                | varchar(25)  | YES  |     | NULL 
|       |

| industry                    | varchar(25)  | YES  |     | NULL 
|       |

| annual_revenue              | varchar(25)  | YES  |     | NULL 
|       |

| phone_fax                   | varchar(25)  | YES  | MUL | NULL 
|       |

| billing_address_street      | varchar(150) | YES  |     | NULL 
|       |

| billing_address_city        | varchar(100) | YES  |     | NULL 
|       |

| billing_address_state       | varchar(100) | YES  |     | NULL 
|       |

| billing_address_postalcode  | varchar(20)  | YES  |     | NULL 
|       |

| billing_address_country     | varchar(100) | YES  |     | NULL 
|       |

| description                 | text         | YES  |     | NULL 
|       |

| rating                      | varchar(25)  | YES  |     | NULL 
|       |

| phone_office                | varchar(25)  | YES  | MUL | NULL 
|       |

| phone_alternate             | varchar(25)  | YES  | MUL | NULL 
|       |

| email1                      | varchar(100) | YES  |     | NULL 
|       |

| email2                      | varchar(100) | YES  |     | NULL 
|       |

| website                     | varchar(255) | YES  |     | NULL 
|       |

| ownership                   | varchar(100) | YES  |     | NULL 
|       |

| employees                   | varchar(10)  | YES  |     | NULL 
|       |

| sic_code                    | varchar(10)  | YES  |     | NULL 
|       |

| ticker_symbol               | varchar(10)  | YES  |     | NULL 
|       |

| shipping_address_street     | varchar(150) | YES  |     | NULL 
|       |

| shipping_address_city       | varchar(100) | YES  |     | NULL 
|       |

| shipping_address_state      | varchar(100) | YES  |     | NULL 
|       |

| shipping_address_postalcode | varchar(20)  | YES  |     | NULL 
|       |

| shipping_address_country    | varchar(100) | YES  |     | NULL 
|       |

| deleted                     | tinyint(1)   |      |     | 0 
|       |

+-----------------------------+--------------+------+-----+---------------------+-------+



USERS TABLE:

+-----------------------+--------------+------+-----+---------------------+-------+

| Field                 | Type         | Null | Key | Default             | 
Extra |

+-----------------------+--------------+------+-----+---------------------+-------+

| id                    | varchar(36)  |      | PRI |                     | 
|

| user_name             | varchar(20)  | YES  | MUL | NULL                | 
|

| user_password         | varchar(30)  | YES  | MUL | NULL                | 
|

| user_hash             | varchar(32)  | YES  |     | NULL                | 
|

| first_name            | varchar(30)  | YES  |     | NULL                | 
|

| last_name             | varchar(30)  | YES  |     | NULL                | 
|

| reports_to_id         | varchar(36)  | YES  | MUL | NULL                | 
|

| is_admin              | char(3)      | YES  |     | 0                   | 
|

| receive_notifications | char(1)      | YES  |     | 1                   | 
|

| description           | text         | YES  |     | NULL                | 
|

| date_entered          | datetime     |      |     | 0000-00-00 00:00:00 | 
|

| date_modified         | datetime     |      |     | 0000-00-00 00:00:00 | 
|

| modified_user_id      | varchar(36)  | YES  |     | NULL                | 
|

| created_by            | varchar(36)  | YES  |     | NULL                | 
|

| title                 | varchar(50)  | YES  |     | NULL                | 
|

| department            | varchar(50)  | YES  |     | NULL                | 
|

| phone_home            | varchar(50)  | YES  |     | NULL                | 
|

| phone_mobile          | varchar(50)  | YES  |     | NULL                | 
|

| phone_work            | varchar(50)  | YES  |     | NULL                | 
|

| phone_other           | varchar(50)  | YES  |     | NULL                | 
|

| phone_fax             | varchar(50)  | YES  |     | NULL                | 
|

| email1                | varchar(100) | YES  |     | NULL                | 
|

| email2                | varchar(100) | YES  |     | NULL                | 
|

| status                | varchar(25)  | YES  |     | NULL                | 
|

| address_street        | varchar(150) | YES  |     | NULL                | 
|

| address_city          | varchar(100) | YES  |     | NULL                | 
|

| address_state         | varchar(100) | YES  |     | NULL                | 
|

| address_country       | varchar(25)  | YES  |     | NULL                | 
|

| address_postalcode    | varchar(9)   | YES  |     | NULL                | 
|

| user_preferences      | text         | YES  |     | NULL                | 
|

| deleted               | tinyint(1)   |      | MUL | 0                   | 
|

| portal_only           | tinyint(1)   | YES  |     | 0                   | 
|

| employee_status       | varchar(25)  | YES  |     | NULL                | 
|

| messenger_id          | varchar(25)  | YES  |     | NULL                | 
|

| messenger_type        | varchar(25)  | YES  |     | NULL                | 
|

+-----------------------+--------------+------+-----+---------------------+-------+



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..)



I tried to create indexses like idx_phones (phone_off, phone_alt, phone_fax) 
or indexes like idx_something (name, assigned_user_id, phone_off, phone_alt, 
phone_fax, deleted) .. and couple of others.. but no indeks helps here..

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