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