I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The main table being selected from is largish
(37,247,884 rows with 282 columns). Caching is off for my testing, so
it's not related to that. To short circuit anyone asking, these
queries are generated by python code, which is why there's an IN
clause with 1 value, as oppose to an =.
Here are the queries and their explains. The significant difference is
that the faster query has "Using
intersect(data_cst_bbccbce0,data_cst_fba12377)" in the query plan -
those 2 indexes are on the 2 columns in the where clause, so that's
why the second one is faster. But I am wondering what I can do to make
the first one faster.
4 hour query:
SELECT MIN(data_tool.name) as tool,
MIN(data_cst.date_time) "start",
MAX(data_cst.date_time) "end",
MIN(data_target.name) as target,
MIN(data_lot.name) as lot,
MIN(data_wafer.name) as wafer,
MIN(measname) as measname,
MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id
Explain:
+----+-------------+-----------------+--------+-------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+-----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref
| rows | Extra |
+----+-------------+-----------------+--------+-------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+-----------------------------+------+---------------------------------+
| 1 | SIMPLE | data_target | const | PRIMARY
| PRIMARY | 4 | const
| 1 | Using temporary; Using filesort |
| 1 | SIMPLE | data_measparams | index | PRIMARY
| PRIMARY | 4 | NULL
| 767 | Using index |
| 1 | SIMPLE | data_cst | ref |
data_cst_4262c4f4,data_cst_01213b5c,data_cst_bbccbce0,data_cst_b0229011,data_cst_65c0aaaa,data_cst_fba12377,data_cst_634020d0
| data_cst_634020d0 | 5 | motor_gf.data_measparams.id | 48 |
Using where |
| 1 | SIMPLE | data_tool | eq_ref | PRIMARY
| PRIMARY | 4 |
motor_gf.data_cst.tool_id | 1 | NULL |
| 1 | SIMPLE | data_recipe | eq_ref | PRIMARY
| PRIMARY | 4 |
motor_gf.data_cst.recipe_id | 1 | NULL |
| 1 | SIMPLE | data_lot | eq_ref | PRIMARY
| PRIMARY | 4 |
motor_gf.data_cst.lot_id | 1 | NULL |
| 1 | SIMPLE | data_wafer | eq_ref | PRIMARY
| PRIMARY | 4 |
motor_gf.data_cst.wafer_id | 1 | NULL |
+----+-------------+-----------------+--------+-------------------------------------------------------------------------------------------------------------------------------+-------------------+---------+-----------------------------+------+---------------------------------+
Faster query:
SELECT MIN(data_tool.name) as tool,
MIN(data_cst.date_time) "start",
MAX(data_cst.date_time) "end",
MIN(data_target.name) as target,
MIN(data_lot.name) as lot,
MIN(data_wafer.name) as wafer,
MIN(measname) as measname,
MIN(data_recipe.name) as recipe
FROM data_cst
INNER JOIN data_tool ON data_tool.id = data_cst.tool_id
INNER JOIN data_target ON data_target.id = data_cst.target_name_id
INNER JOIN data_lot ON data_lot.id = data_cst.lot_id
INNER JOIN data_wafer ON data_wafer.id = data_cst.wafer_id
INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id
INNER JOIN data_recipe ON data_recipe.id = data_cst.recipe_id
WHERE data_target.id IN (172) AND
data_recipe.id IN (148) AND
data_cst.date_time BETWEEN '2015-01-26 00:00:00' AND '2015-01-26 23:59:59'
GROUP BY wafer_id, data_cst.lot_id, target_name_id
Explain:
+----+-------------+-----------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+---------------------------------------+------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys
| key
| key_len | ref | rows | Extra
|
+----+-------------+-----------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+---------------------------------------+------+-------------------------------------------------------------------+
| 1 | SIMPLE | data_target | const | PRIMARY
| PRIMARY
| 4 | const | 1 | Using
temporary; Using filesort |
| 1 | SIMPLE | data_recipe | const | PRIMARY
| PRIMARY
| 4 | const | 1 | NULL
|
| 1 | SIMPLE | data_cst | index_merge |
data_cst_4262c4f4,data_cst_01213b5c,data_cst_bbccbce0,data_cst_b0229011,data_cst_65c0aaaa,data_cst_fba12377,data_cst_634020d0
| data_cst_bbccbce0,data_cst_fba12377 | 4,4 | NULL
| 302 | Using
intersect(data_cst_bbccbce0,data_cst_fba12377); Using where |
| 1 | SIMPLE | data_tool | eq_ref | PRIMARY
| PRIMARY
| 4 | motor_gf.data_cst.tool_id | 1 | NULL
|
| 1 | SIMPLE | data_measparams | eq_ref | PRIMARY
| PRIMARY
| 4 | motor_gf.data_cst.meas_params_name_id | 1 | Using
index |
| 1 | SIMPLE | data_lot | eq_ref | PRIMARY
| PRIMARY
| 4 | motor_gf.data_cst.lot_id | 1 | NULL
|
| 1 | SIMPLE | data_wafer | eq_ref | PRIMARY
| PRIMARY
| 4 | motor_gf.data_cst.wafer_id | 1 | NULL
|
+----+-------------+-----------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+---------------------------------------+------+-------------------------------------------------------------------+
Here are the indexes on data_cst:
+----------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed |
Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| data_cst | 0 | PRIMARY | 1 | id
| A | 37247884 | NULL | NULL | | BTREE
| | |
| data_cst | 1 | data_cst_d026deb4 | 1 |
data_file_id | A | 365175 | NULL | NULL |
| BTREE | | |
| data_cst | 1 | data_cst_4262c4f4 | 1 | tool_id
| A | 12 | NULL | NULL | | BTREE
| | |
| data_cst | 1 | data_cst_0d392858 | 1 |
wafer_date | A | 361629 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_01213b5c | 1 | date_time
| A | 9311971 | NULL | NULL | | BTREE
| | |
| data_cst | 1 | data_cst_bbccbce0 | 1 |
target_name_id | A | 34713 | NULL | NULL |
| BTREE | | |
| data_cst | 1 | data_cst_b0229011 | 1 | wafer_id
| A | 57659 | NULL | NULL | | BTREE
| | |
| data_cst | 1 | data_cst_65c0aaaa | 1 | lot_id
| A | 22277 | NULL | NULL | | BTREE
| | |
| data_cst | 1 | data_cst_fba12377 | 1 | recipe_id
| A | 4788 | NULL | NULL | | BTREE
| | |
| data_cst | 1 | data_cst_3e838ccb | 1 |
user_name_id | A | 42 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_634020d0 | 1 |
meas_params_name_id | A | 775997 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_b84e5788 | 1 |
run_name_id | A | 5439 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_2030f483 | 1 |
image_pr_top_id | A | 37247884 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_798133fb | 1 |
image_measurer_id | A | 37247884 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_ced012e5 | 1 |
wafer_map_image_id | A | 354741 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_c90ac9f6 | 1 |
ler_file_path_id | A | 37247884 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_d3c8ac46 | 1 |
wf_file_path_id | A | 37247884 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_732917fa | 1 |
ece_file_path_id | A | 37247884 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_5bb7136a | 1 |
epe_file_path_id | A | 37247884 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_a8290ba2 | 1 |
mcd_file_path_id | A | 18623942 | NULL | NULL |
YES | BTREE | | |
| data_cst | 1 | data_cst_fc65a7fb | 1 | ep
| A | 760160 | NULL | NULL | YES | BTREE
| | |
| data_cst | 1 | data_cst_dbe16c2b | 1 | roiname
| A | 12415961 | NULL | NULL | YES | BTREE
| | |
+----------+------------+-------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Thanks for taking the time to read this, and for any help or pointers
you can give me.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
