56,675
社区成员
发帖
与我相关
我的任务
分享
EXPLAIN SELECT id, m_main_type, m_use_count, u_use_count, url FROM tmp_table_site_use_count;
+----+-------------+--------------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | tmp_table_site_use_count | ALL | NULL | NULL | NULL | NULL | 5 | |
+----+-------------+--------------------------+------+---------------+------+---------+------+------+-------+
EXPLAIN SELECT id, m_main_type, m_use_count, 0 AS u_use_count, url FROM table_site WHERE id NOT IN (1253419,1253478,1265366,1613649,1919261)
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | table_site | ALL | PRIMARY | NULL | NULL | NULL | 1719672 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
EXPLAIN SELECT id, m_main_type, m_use_count, 0 AS u_use_count, url
FROM table_site WHERE id NOT IN (1253419,1253478,1265366,1613649,1919261)
UNION ALL
SELECT id, m_main_type, m_use_count, u_use_count, url
FROM tmp_table_site_use_count
INTO OUTFILE '/tmp/main_policy_tmp_test.sql'
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
| 1 | PRIMARY | table_site | ALL | PRIMARY | NULL | NULL | NULL | 1719672 | Using where |
| 2 | UNION | tmp_table_site_use_count | ALL | NULL | NULL | NULL | NULL | 5 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
DROP PROCEDURE IF EXISTS procedure_table_site_use_issue_test;
CREATE PROCEDURE procedure_table_site_use_issue_test(IN out_file_path VARCHAR(1000))
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmp_table_site_use_count;
CREATE TEMPORARY TABLE tmp_table_site_use_count SELECT s.id AS id, s.m_main_type AS m_main_type, s.m_use_count AS m_use_count, u.u_use_count AS u_use_count, s.url AS url
FROM table_site_use_count AS u, table_site AS s where s.id = u.site_id and s.state = 1;
SET SESSION group_concat_max_len = 102400;
SELECT GROUP_CONCAT(DISTINCT site_id SEPARATOR ',') INTO @tmp_sites_concat FROM table_site_use_count;
SET @sql_str = CONCAT('SELECT id, m_main_type, m_use_count, u_use_count, url ',
'FROM tmp_table_site_use_count ',
'UNION ALL ',
'SELECT id, m_main_type, m_use_count, 0 AS u_use_count, url ',
'FROM table_site ',IF(@tmp_sites_concat IS NULL, '',CONCAT('WHERE id NOT IN (', @tmp_sites_concat,') ')),
'INTO OUTFILE \'',out_file_path,'\'');
PREPARE sql_query FROM @sql_str;
EXECUTE sql_query;
DEALLOCATE PREPARE sql_query;
DROP TEMPORARY TABLE IF EXISTS tmp_table_site_use_count;
END
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+
| 1 | PRIMARY | tmp_table_site_use_count | ALL | NULL | NULL | NULL | NULL | 5 | |
| 2 | UNION | table_site | ALL | PRIMARY | NULL | NULL | NULL | 1748669 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+--------------------------+------+---------------+------+---------+------+---------+-------------+