Menu
When I run a query on my laptop, it takes like a second to execute, but in the production environment, the query lasts 57 seconds (and it crashes the applications - that's written in Ruby On Rails).
I ran this query with EXPLAIN and found out that on my laptop is on one table in the query executed
Using join buffer (Block Nested Loop)
but this is missing in production (for this table, there's nothing in the EXTRA column).
Why is that? How to add Using join buffer (Block Nested Loop) also to production?
Thank you
EDIT: here's the query:
SELECT `shippers`.`company_name`
FROM `shippers`
LEFT OUTER JOIN `shipper_services`
ON `shipper_services`.`shipper_id` = `shippers`.`id`
LEFT OUTER JOIN `services` ON `services`.`id` = `shipper_services`.`service_id`
LEFT OUTER JOIN `shipper_freight_flow_industries`
ON `shipper_freight_flow_industries`.`shipper_id` = `shippers`.`id`
LEFT OUTER JOIN `freight_flow_industries`
ON `freight_flow_industries`.`id` = `shipper_freight_flow_industries`.`freight_flow_industry_id`
WHERE `shippers`.`id` IN (189459, 337334, 149804, 36046, 158403, 165682, 153399, 51471, 211796, 164984, 61118, 56497, 340353, 66525, 225635, 250376, 33237, 69140, 151777, 169530, 245255, 142246, 259597, 57889, 262986, 167803, 33459, 40561, 65878, 44356, 169545, 210358, 25555, 4563, 82538, 157765, 232509, 213248, 37380, 258965, 340616, 340926, 143314, 195553, 60845, 161463, 255789, 38942, 192219, 811, 64672, 13530, 340809, 66030, 157223, 347671, 176886, 157812, 348255, 37357, 337003, 5588, 24257, 164799, 230717, 153801, 171835, 66595, 176780, 250184, 3262, 201519, 223904, 241992, 254167, 242449, 5368, 23903, 52571, 7198, 40135, 340494, 11851, 171285, 26810, 231003, 193961, 341161, 17122, 56660, 348103, 145539, 176912, 19716, 196617, 34803, 75444, 62418, 149606, 158879, 242439, 63291, 80548, 170778, 184871, 254549, 337109, 151159, 255077, 38939, 191926, 337720, 205999, 247471, 258106, 40225, 52599, 141637, 207246, 247541, 258876, 52629, 65936, 164884, 192238, 247588, 13669, 26875, 41763, 52700, 143009, 154515, 174092, 192869, 210753, 248501, 13835, 27222, 43017, 52995, 154640, 165368, 176390, 235034, 248829, 261492, 16610, 29589, 43109, 143661, 195373, 211866, 236874, 33148, 67629, 145474, 166592, 212358, 236937, 263276, 353, 18193, 44479, 68389, 196133, 241530, 251862, 331361, 770, 45861, 68982, 145652, 157945, 177022, 214534, 241659, 253705, 332487, 349455, 20303, 46852, 61001, 147373, 158198, 178036, 200139, 220189, 241765, 253987, 334046, 350465, 21532, 46970, 149478, 170761, 178187, 334167, 350466, 3285, 22934, 48334, 77067, 170770, 184809, 201905, 224892, 254293, 23063, 51366, 203181, 37607, 63370, 80720, 163426, 170798, 203424, 226486, 243809, 244904, 63388, 81420, 163553, 170800, 203819, 228767, 244936, 6057, 24664, 52584, 152229, 164080, 170812, 192096, 229759, 257935, 25300, 52592, 65635, 82575, 152798, 164171, 170817, 192179, 206647, 229772)
AND ((
(ACOS(least(1,COS(0.5194174327134307)*COS(-1.664517065837707)*COS(RADIANS(shippers.latitude))*COS(RADIANS(shippers.longitude))+
COS(0.5194174327134307)*SIN(-1.664517065837707)*COS(RADIANS(shippers.latitude))*SIN(RADIANS(shippers.longitude))+
SIN(0.5194174327134307)*SIN(RADIANS(shippers.latitude))))*3963.1899999999996)
<= 5.0)) AND (store_location = "0" AND corporate_hq = "0" AND queued_item="0")
GROUP BY company_name ORDER BY count_of_facilities DESC)
production:
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | shippers | range | PRIMARY,idx_store | PRIMARY | 4 | NULL | 245 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | shipper_services | ref | idx_combo | idx_combo | 5 | db_production.shippers.id | 2 | Using index |
| 1 | SIMPLE | services | eq_ref | PRIMARY | PRIMARY | 4 | db_production.shipper_services.service_id | 1 | Using index |
| 1 | SIMPLE | shipper_freight_flow_industries | ALL | NULL | NULL | NULL | NULL | 241609 | |
| 1 | SIMPLE | freight_flow_industries | eq_ref | PRIMARY | PRIMARY | 4 | db_production.shipper_freight_flow_industries.freight_flow_industry_id | 1 | Using index |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------+--------+----------------------------------------------+
5 rows in set (0.00 sec)
Indexes:
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| shipper_freight_flow_industries | 0 | PRIMARY | 1 | id | A | 241609 | NULL | NULL | | BTREE | | |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
localhost:
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------------------+------+----------------------------------------------------+
| 1 | SIMPLE | shippers | range | PRIMARY,idx_store | PRIMARY | 4 | NULL | 245 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | shipper_services | ref | idx_combo | idx_combo | 5 | development.shippers.id | 2 | Using index |
| 1 | SIMPLE | services | eq_ref | PRIMARY | PRIMARY | 4 | development.shipper_services.service_id | 1 | Using index |
| 1 | SIMPLE | shipper_freight_flow_industries | ALL | NULL | NULL | NULL | NULL | 58 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | freight_flow_industries | eq_ref | PRIMARY | PRIMARY | 4 | development.shipper_freight_flow_industries.freight_flow_industry_id | 1 | Using index |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------------------+------+----------------------------------------------------+
Indexes:
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| shipper_freight_flow_industries | 0 | PRIMARY | 1 | id | A | 58 | NULL | NULL | | BTREE | | |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
856
This is a bug with Mysql https://bugs.mysql.com/bug.php?id=69721
You can run the query with
set optimizer_switch='block_nested_loop=off'
This post really asks 2 questions.
The implicit one is something like "How can I improve performance for this query on my production system?".
As already noticed by @Fabricator, the answer is "Add an index on shipper_id to the shipper_freight_flow_industries table", which is the recommended way as soon as joins create a huge number of combinations due to the size of the involved tables.
Regarding the explicit one:
Why is that? How to add Using join buffer (Block Nested Loop) also to production?
The time difference obviously comes from the size of the shipper_freight_flow_industries table: 58 in the laptop DB, 241,609 in the production DB.
And the fact that only the laptop uses a join buffer is likely due to the join_buffer_size variable (look at this MySql page): it's probably set to its default value (256K) on both systems.
So it's used on the laptop DB, but on the production system it's too small to allow using it for the number of rows involved.
As stated above, adding an index is the preferred way to improve this query performance, but it might be interesting to try using a very large join_buffer_size without an index, and compare the results.
This solution might be a good alternative in some peculiar cases, such as if this index has no other use than for this query and if furthermore it's dwelling other tasks like its table updates.
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
