svidgen
09-03-2009, 12:47 PM
Hey folks, any suggestions here would be thoroughly appreciated.
I have three tables in three separate databases:
school_dat.accounts AS sda
sda contains approximately 150k rows.
account_collisions.collisions AS acc
acc contains approximately 60k rows.
sugarcrm.accounts AS sa
sa contains approximately 5k rows.
I need to run two queries somewhat regularly, both EXPLAINed here:
mysql> explain select distinct(sda.uuid) from school_dat.accounts sda left join account_collisions.collisions acc on (sda.uuid=acc.nces_uuid and acc.pick=1) where acc.pick is null;
+----+-------------+-------+-------+---------------+-----------+---------+---------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+---------------------+--------+------------------------------+
| 1 | SIMPLE | sda | index | NULL | uuid | 39 | NULL | 150472 | Using index; Using temporary |
| 1 | SIMPLE | acc | ref | nces_uuid | nces_uuid | 39 | school_dat.sda.uuid | 2 | Using where; Distinct |
+----+-------------+-------+-------+---------------+-----------+---------+---------------------+--------+------------------------------+
2 rows in set (0.00 sec)
mysql> explain select distinct(sa.id) from sugarcrm.accounts sa left join account_collisions.collisions acc on (sa.id=acc.sugar_uuid and acc.pick=1) where acc.pick is null;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
| 1 | SIMPLE | sa | index | NULL | PRIMARY | 110 | NULL | 5425 | Using index; Using temporary |
| 1 | SIMPLE | acc | ALL | NULL | NULL | NULL | NULL | 61608 | Using where; Distinct |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
2 rows in set (0.00 sec)
The issue, of course, is that the second query does not appear to be taking advantage of any indexes in acc, causing that query to execute for significantly longer (~5min) than the first (~5sec). The second query returns significantly fewer rows and approximately the same number of JOIN matches as the first. The problem appears to be a lack of indexes. However, showing the relevant columns from each table, we see that the necessary indexes are [theoretically] present:
mysql> show columns from collisions like '%uuid';
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| sugar_uuid | varchar(36) | YES | MUL | NULL | |
| nces_uuid | varchar(36) | YES | MUL | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show columns from sugarcrm.accounts like 'id';
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(36) | NO | PRI | | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show columns from school_dat.accounts like 'uuid';
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uuid | varchar(36) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
Is there anything jumping out at anyone that would or could be causing problems?
Thanks
I have three tables in three separate databases:
school_dat.accounts AS sda
sda contains approximately 150k rows.
account_collisions.collisions AS acc
acc contains approximately 60k rows.
sugarcrm.accounts AS sa
sa contains approximately 5k rows.
I need to run two queries somewhat regularly, both EXPLAINed here:
mysql> explain select distinct(sda.uuid) from school_dat.accounts sda left join account_collisions.collisions acc on (sda.uuid=acc.nces_uuid and acc.pick=1) where acc.pick is null;
+----+-------------+-------+-------+---------------+-----------+---------+---------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+---------------------+--------+------------------------------+
| 1 | SIMPLE | sda | index | NULL | uuid | 39 | NULL | 150472 | Using index; Using temporary |
| 1 | SIMPLE | acc | ref | nces_uuid | nces_uuid | 39 | school_dat.sda.uuid | 2 | Using where; Distinct |
+----+-------------+-------+-------+---------------+-----------+---------+---------------------+--------+------------------------------+
2 rows in set (0.00 sec)
mysql> explain select distinct(sa.id) from sugarcrm.accounts sa left join account_collisions.collisions acc on (sa.id=acc.sugar_uuid and acc.pick=1) where acc.pick is null;
+----+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
| 1 | SIMPLE | sa | index | NULL | PRIMARY | 110 | NULL | 5425 | Using index; Using temporary |
| 1 | SIMPLE | acc | ALL | NULL | NULL | NULL | NULL | 61608 | Using where; Distinct |
+----+-------------+-------+-------+---------------+---------+---------+------+-------+------------------------------+
2 rows in set (0.00 sec)
The issue, of course, is that the second query does not appear to be taking advantage of any indexes in acc, causing that query to execute for significantly longer (~5min) than the first (~5sec). The second query returns significantly fewer rows and approximately the same number of JOIN matches as the first. The problem appears to be a lack of indexes. However, showing the relevant columns from each table, we see that the necessary indexes are [theoretically] present:
mysql> show columns from collisions like '%uuid';
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| sugar_uuid | varchar(36) | YES | MUL | NULL | |
| nces_uuid | varchar(36) | YES | MUL | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show columns from sugarcrm.accounts like 'id';
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | varchar(36) | NO | PRI | | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show columns from school_dat.accounts like 'uuid';
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uuid | varchar(36) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
Is there anything jumping out at anyone that would or could be causing problems?
Thanks