-
EXPLAIN执行计划:
+------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+======+===============+======================+==============+========+======================+======================+===========+=======+========+============+==================+
| 1 | PRIMARY | | None | ALL | None | None | None | None | 2 | 100 | Using filesort |
+------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+
| 2 | DERIVED | user_aukd | None | range | idx_f_date_country_i | idx_f_date_country_i | 8 | None | 5 | 7.14 | Using index |
| | | ay_stat | | | d,idx_user_activeuse | d | | | | | condition; Using |
| | | | | | rkeepday_stat | | | | | | where |
+------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+
-
索引优化建议：
Executing SQL query where_clause f_date... ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 0% 0:00:00
Traceback (most recent call last):
File "sqlai_helper.py", line 215, in
Cardinality = count_column_value(table_name, where_field, mysql_settings, sample_size)
File "sql_count_value.py", line 48, in count_column_value
cursor.execute(sql)
File "pymysql/cursors.py", line 153, in execute
File "pymysql/cursors.py", line 322, in _query
File "pymysql/connections.py", line 558, in query
File "pymysql/connections.py", line 822, in _read_query_result
File "pymysql/connections.py", line 1200, in read
File "pymysql/connections.py", line 772, in _read_packet
File "pymysql/protocol.py", line 221, in raise_for_error
File "pymysql/err.py", line 143, in raise_mysql_exception
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\n LIMIT 100000\n ) AS subquery\n GR' at line 4")
[8060] Failed to execute script 'sqlai_helper' due to unhandled exception!
SQL：
SELECT datediff(now(), fdate) AS 'daynum',
fdate,
newusernum,
oneday,
secondday,
thirdday,
fourthday,
fifthday,
sixthday,
seventhday,
fifteenthday,
thirtiethday
FROM
(SELECT f_date AS fdate,
SUM(f_active_num) AS newusernum,
SUM(f_one_day) AS oneday,
SUM(f_second_day) AS secondday,
SUM(f_third_day) AS thirdday,
SUM(f_fourth_day) AS fourthday,
SUM(f_fifth_day) AS fifthday,
SUM(f_sixth_day) AS sixthday,
SUM(f_seventh_day) AS seventhday,
SUM(f_fifteenth_day) AS fifteenthday,
SUM(f_thirtieth_day) AS thirtiethday
FROM table_stat
WHERE f_date>='2024-08-17'
AND f_date<='2024-08-23'
AND f_country_id IN(0)
AND f_os IN(0)
AND f_language IN(0)
AND f_bind_type IN(0)
AND f_back_front =0
GROUP BY fdate) a
ORDER BY fdate DESC
LIMIT 0,50
EXPLAIN执行计划:
+------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+======+===============+======================+==============+========+======================+======================+===========+=======+========+============+==================+
| 1 | PRIMARY | | None | ALL | None | None | None | None | 2 | 100 | Using filesort |
+------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+
| 2 | DERIVED | user_aukd | None | range | idx_f_date_country_i | idx_f_date_country_i | 8 | None | 5 | 7.14 | Using index |
| | | ay_stat | | | d,idx_user_activeuse | d | | | | | condition; Using |
| | | | | | rkeepday_stat | | | | | | where |
+------+---------------+----------------------+--------------+--------+----------------------+----------------------+-----------+-------+--------+------------+------------------+
索引优化建议：
Executing SQL query where_clause f_date... ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 0% 0:00:00
Traceback (most recent call last):
File "sqlai_helper.py", line 215, in
Cardinality = count_column_value(table_name, where_field, mysql_settings, sample_size)
File "sql_count_value.py", line 48, in count_column_value
cursor.execute(sql)
File "pymysql/cursors.py", line 153, in execute
File "pymysql/cursors.py", line 322, in _query
File "pymysql/connections.py", line 558, in query
File "pymysql/connections.py", line 822, in _read_query_result
File "pymysql/connections.py", line 1200, in read
File "pymysql/connections.py", line 772, in _read_packet
File "pymysql/protocol.py", line 221, in raise_for_error
File "pymysql/err.py", line 143, in raise_mysql_exception
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\n LIMIT 100000\n ) AS subquery\n GR' at line 4")
[8060] Failed to execute script 'sqlai_helper' due to unhandled exception!
SQL：
SELECT datediff(now(), fdate) AS 'daynum',
fdate,
newusernum,
oneday,
secondday,
thirdday,
fourthday,
fifthday,
sixthday,
seventhday,
fifteenthday,
thirtiethday
FROM
(SELECT f_date AS fdate,
SUM(f_active_num) AS newusernum,
SUM(f_one_day) AS oneday,
SUM(f_second_day) AS secondday,
SUM(f_third_day) AS thirdday,
SUM(f_fourth_day) AS fourthday,
SUM(f_fifth_day) AS fifthday,
SUM(f_sixth_day) AS sixthday,
SUM(f_seventh_day) AS seventhday,
SUM(f_fifteenth_day) AS fifteenthday,
SUM(f_thirtieth_day) AS thirtiethday
FROM table_stat
WHERE f_date>='2024-08-17'
AND f_date<='2024-08-23'
AND f_country_id IN(0)
AND f_os IN(0)
AND f_language IN(0)
AND f_bind_type IN(0)
AND f_back_front =0
GROUP BY fdate) a
ORDER BY fdate DESC
LIMIT 0,50