Reputation: 835
Slow MySQL Query
The following SQL query takes approximately 1 min. 15 sec. to execute when entered directly into the SQL console. There are 6636 records in the qv_attribute table.
Is the execution time for this query normal? Is there any way to reduce the time it takes to run this operation by changing database settings or optimizing the query?
SELECT a.node_id,
MAX(CASE WHEN(b.att_id = 9003) THEN a.att_description END) AS 'ICS_Owner',
MAX(CASE WHEN(b.att_id = 9004) THEN a.att_description END) AS 'Service',
MAX(CASE WHEN(t.rel_id = 12092) THEN t.att_description END) AS 'Contact_Name',
MAX(CASE WHEN(t.rel_id = 12610) THEN t.att_description END) AS 'Address_1',
MAX(CASE WHEN(t.rel_id = 9007) THEN t.att_description END) AS 'Address_2',
MAX(CASE WHEN(t.rel_id = 12612) THEN t.att_description END) AS 'City',
MAX(CASE WHEN(t.rel_id = 12614) THEN t.att_description END) AS 'State',
MAX(CASE WHEN(t.rel_id = 13508) THEN t.att_description END) AS 'a',
MAX(CASE WHEN(t.rel_id = 13509) THEN t.att_description END) AS 'b',
MAX(CASE WHEN(b.att_id = 13132) THEN a.att_description END) AS 'Description',
MAX(CASE WHEN(des.node_id = 10824) THEN des.att_description END) AS 'Row_Description'
FROM `qv_attribute` t, `qv_attribute` a, `qv_attribute` b, `qv_attribute` des
WHERE b.att_id IN (9003,9004,13132,14542)
AND b.att_name = a.att_name
AND ((a.node_id = 1554 AND
((t.node_id = 1776 AND des.att_order = 1)
OR (t.node_id = 1780 AND des.att_order = 2)
OR (t.node_id = 1781 AND des.att_order = 3)
OR (t.node_id = 1782 AND des.att_order = 4)
OR (t.node_id = 1783 AND des.att_order = 5)
OR (t.node_id = 1784 AND des.att_order = 6)
OR (t.node_id = 1778 AND des.att_order = 7)
OR (t.node_id = 1777 AND des.att_order = 8)))
OR (a.node_id = 1574 AND
((t.node_id = 1574 AND des.att_order = 1)
OR (t.node_id = 1779 AND des.att_order = 2)
OR (t.node_id = 1576 AND des.att_order = 3)
OR (t.node_id = 1577 AND des.att_order = 4)
OR (t.node_id = 1710 AND des.att_order = 5)
OR (t.node_id = 1711 AND des.att_order = 6)
OR (t.node_id = 1712 AND des.att_order = 7)))
OR (a.node_id = 1803 AND
((t.node_id = 1838 AND des.att_order = 1)
OR (t.node_id = 1839 AND des.att_order = 2)
OR (t.node_id = 1840 AND des.att_order = 3)
OR (t.node_id = 1805 AND des.att_order = 4)))
OR (a.node_id = 1831 AND
((t.node_id = 1842 AND des.att_order = 1)
OR (t.node_id = 1847 AND des.att_order = 2)
OR (t.node_id = 1833 AND des.att_order = 3)
OR (t.node_id = 1848 AND des.att_order = 4)
OR (t.node_id = 1845 AND des.att_order = 5)
OR (t.node_id = 1846 AND des.att_order = 6)
OR (t.node_id = 1841 AND des.att_order = 7)
OR (t.node_id = 1844 AND des.att_order = 8)
OR (t.node_id = 1843 AND des.att_order = 9)))
OR (a.node_id = 1810 AND
((t.node_id = 1854 AND des.att_order = 1)
OR (t.node_id = 1849 AND des.att_order = 2)
OR (t.node_id = 1851 AND des.att_order = 3)
OR (t.node_id = 1853 AND des.att_order = 4)
OR (t.node_id = 1852 AND des.att_order = 5)
OR (t.node_id = 1812 AND des.att_order = 6)
OR (t.node_id = 1850 AND des.att_order = 7))))
GROUP BY a.node_id, t.node_id
EXPLAIN EXTENDED:
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | b | range | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | des | ALL | NULL | NULL | NULL | NULL | 6695 | Using where |
| 1 | SIMPLE | t | range | node_id | node_id | 4 | NULL | 629 | Using where |
| 1 | SIMPLE | a | range | node_id | node_id | 4 | NULL | 139 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
Upvotes: 0
Views: 136
Answers (3)
Reputation: 70538
Just think of the magic you could do if you created a table that looked like this:
a_node_id t_node_id att_order
1554 1776 1
1554 1780 2
1554 1781 3
1554 1782 4
1554 1783 5
1554 1784 6
1554 1778 7
1554 1777 8
1574 1574 1
1574 1779 2
1574 1576 3
1574 1577 4
1574 1710 5
1574 1711 6
1574 1712 7
1803 1838 1
1803 1839 2
1803 1840 3
1803 1805 4
etc
Additional hint : Put this in a table and left join to this table -- much faster than the where clause you currently have.
Upvotes: 2
Reputation: 11488
I think the problem is you're not specifying any join conditions for t or des, and you makeing tons of comparisons to them. So you're repeating every match of a and b with every combination of t and des, which is probably not what you're intending.
The resulting table has the number of rows equal to (a join b) * t * des
Here's what your where clause is boiling down to:
AND ((a.node_id = 1554 AND
((t.node_id IN 1776, 1780, 1781, 1782, 1784, 1778, 1777) AND des.att_order BETWEEN 1 AND 8)
OR (a.node_id = 1574 AND
((t.node_id IN 1779, 1576, 1577, 1710, 1711, 1712) AND des.att_order BETWEEN 1 AND 7)
...
That query is pretty ugly, but at ~7k rows a pivot with a bunch of filters shouldn't take over a minute
Upvotes: 2
Reputation: 9007
Assuming that you already have indexed your table - the slowdown comes from your CASE WHEN combinations. Split it into 11 separate queries and you'll observe a speedup.
On a side note, if you have to run this query often enough where 1 minute is too long - you may want to look into redesigning the database structure to keep some redundant, but quick-query information.
Good luck!
Upvotes: 0