SELECT query running for 9h (till now)
Hi there,
I got a problem with a complex MySQL SELECT query.
The query was running without problems some time ago, I havn't monitored it for a while, then I had to add a new row to a table (not involved in the SELECT query we're talking about) and the query is now slow as hell.
Before, it took about 15 minutes for the execution, that was OK because it is only done once a day with a cronjob, but now it is already running over 9 hours and I can't tell how long it will last until it finishes.
The query sould build a reference table by joining the primary keys of eaech table together (all involved cols are indexed).
This is the query:
SELECT DISTINCT ref.object_id AS s_object_id,
r.object_id AS r_object_id,
ref.object_id_l AS l_object_id,
ref.object_id_p AS p_object_id,
ref.object_id_i AS i_object_id,
ref.object_id_d AS d_object_id,
ref.object_id_lia AS lia_object_id,
r.router_ce_name AS hostname,
ref.vpn_id AS vpn_id,
ref.sp_no AS sp_no,
CONCAT( ref.vpn_id, ".", ref.sp_no ) AS sp_id,
IF( s1.sha_sp_vpn_id, s2.object_id, ref.object_id ) AS master_s_object_id,
IF( s1.sha_sp_vpn_id, s1.sha_sp_vpn_id, ref.vpn_id ) AS master_vpn_id,
IF( s1.sha_sp_vpn_id, s1.sha_sp_sp_no, ref.sp_no ) AS master_sp_no,
IF( s1.sha_sp_vpn_id, CONCAT( s1.sha_sp_vpn_id, ".", s1.sha_sp_sp_no ),
CONCAT( ref.vpn_id, ".", ref.sp_no ) ) AS master_sp_id
FROM (
ref_sp_id AS ref
INNER JOIN (
peport AS p
INNER JOIN (
local_loop AS l
INNER JOIN router_ce AS r ON r.object_id = l.r_object_id
) ON l.object_id = p.l_object_id
) ON p.object_id = ref.object_id_p
LEFT JOIN (
sp_core AS s1
INNER JOIN sp_core AS s2 ON ( s1.SHA_SP_SP_NO = s2.sp_no )
AND (
s1.SHA_SP_VPN_ID = s2.vpn_id
)
) ON ref.object_id = s1.object_id
)
WHERE ref.object_id_p >0
AND NOT p.status_comp = 'canceled';
Sorry I'm new here... don't know if there is something like syntax-highlighting implemented in this forum... hope you can read this.
What can I do to optimize the query?
EXPLAIN says:
+----+-------------+-------+------+------------------------------------------------------+-----------+---------+---------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+------------------------------------------------------+-----------+---------+---------------------+--------+------------------------------+
| 1 | SIMPLE | ref | ALL | object_id_p | NULL | NULL | NULL | 720884 | Using where; Using temporary |
| 1 | SIMPLE | p | ref | object_id,status_comp,l_object_id | object_id | 8 | rot.ref.object_id_p | 1 | Using where |
| 1 | SIMPLE | l | ref | object_id,r_object_id | object_id | 8 | rot.p.l_object_id | 1 | |
| 1 | SIMPLE | r | ref | object_id | object_id | 8 | rot.l.r_object_id | 1 | |
| 1 | SIMPLE | s1 | ref | object_id,sha_sp_vpn_id,sha_sp_sp_no,sha_sp_vpn_id_2 | object_id | 8 | rot.ref.object_id | 1 | |
| 1 | SIMPLE | s2 | ref | vpn_id,sp_no | sp_no | 9 | rot.s1.sha_sp_sp_no | 19 | |
+----+-------------+-------+------+------------------------------------------------------+-----------+---------+---------------------+--------+------------------------------+
I already tried to advertise more memory to mysql, my.cnf looks like this:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip_external_locking
key_buffer = 256M
max_allowed_packet = 2M
table_cache = 12000
table_open_cache = 12000
max_connections = 2000
sort_buffer_size = 256M
net_buffer_length = 32K
read_buffer_size = 256M
read_rnd_buffer_size = 256M
myisam_sort_buffer_size = 256M
key_buffer_size = 256M
max_heap_table_size=768M
tmp_table_size=768M
server-id = 1
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout
Monitoring "top" I don't think memory is the main problem, but the CPU is running at 100% for the whole time.
Any ideas?
Thank you in advance!