sorry if this has been asked already. I've just moved from MySQL 5.6 to 5.7.17 and I'm struggling to understand this issue.
I have a view "delete_me_test_" which has around 26k rows.
SELECT
delete_me_test_.*
from delete_me_test_;
takes less than a second. However I'm trying to assign a value to a variable withing the select command. The goal is to reissue a row number to all returned rows which can be used by DataTable plugin(JQuery) from web-front-end.
To cut the story short, initializing or assigning a value withing a select command affects the performance drastically. I mean 10 seconds to return 50 rows.
This code here
SELECT
(select @row_nummer:= @row_nummer+1), -- this is just a test not final code
delete_me_test_.*
from delete_me_test_;
takes around 10 seconds and please see the explain graph with and without using the variable for the same query.
I'm still learning MySQL and not sure what exactly this issue could be but I can confirm this exact setup was working on 5.6 version without any issues. In 5.7 server gets 100% CPU usage and query never ends. Anyone could help on this please?
I must add this too: If I extract the actual query from the "delete_me_Test" view and add the variable there to test
SELECT
(select @row_nummer:= @row_nummer+1),
...
rest of the selct field statements
from many tables in the select command;
Query works as expected without any issue. Its just when I use it as a view it gets screwed.
I'm using MySQL on windows server 2008 with all updates are installed. Is this a bug?
EDIT: Explain of above query with variable assigned
+---+---------+-------------------------------+--+--------+---------------------------------------------------+----------------------------------------------+-----+----------------------------------------------------------------+--------+--------+--------------------------+
| 1 | PRIMARY | <derived2> | | system | | | | | 1 | 100.00 | |
+---+---------+-------------------------------+--+--------+---------------------------------------------------+----------------------------------------------+-----+----------------------------------------------------------------+--------+--------+--------------------------+
| 1 | PRIMARY | <derived3> | | ALL | | | | | 222724 | 100.00 | |
| 3 | DERIVED | tbl_su | | ALL | | | | | 26712 | 100.00 | |
| 3 | DERIVED | tbl_su_os_sub | | eq_ref | PRIMARY,sub_status_id_UNIQUE | sub_status_id_UNIQUE | 4 | ccmsv2.tbl_su.suOperationStatusID | 1 | 100.00 | |
| 3 | DERIVED | tbl_su_os_main | | eq_ref | PRIMARY,idtbl_OperatonStatusMain_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_su_os_sub.main_status_id | 1 | 100.00 | |
| 3 | DERIVED | tbl_su_requested_region | | eq_ref | PRIMARY,regionID_UNIQUE,regionID | PRIMARY | 4 | ccmsv2.tbl_su.tbl_SU_requested_Region | 1 | 100.00 | |
| 3 | DERIVED | tbl_su_maritalstatus | | eq_ref | PRIMARY,maritalStatusID_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_su.suMaritalStatusID | 1 | 100.00 | |
| 3 | DERIVED | tbl_su_gender | | eq_ref | PRIMARY,gender_ID_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_su.suGender | 1 | 100.00 | |
| 3 | DERIVED | tbl_su_sectionandcategory | | eq_ref | SectionAndCatID_UNIQUE | SectionAndCatID_UNIQUE | 4 | ccmsv2.tbl_su.suSectionAndCatID | 1 | 100.00 | Using where |
| 3 | DERIVED | tbl_su_added_by | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_su.added_by_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_su_updated_by | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_su.updated_by_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_section_category | | eq_ref | PRIMARY | PRIMARY | 4 | ccmsv2.tbl_su_sectionandcategory.sectionCatID | 1 | 100.00 | |
| 3 | DERIVED | tbl_section | | eq_ref | PRIMARY | PRIMARY | 4 | ccmsv2.tbl_su_sectionandcategory.sectionID | 1 | 100.00 | |
| 3 | DERIVED | tbl_oa_sub | | ref | fk_tbl_su_occupancyAgreement_sub_tbl_SU1_idx | fk_tbl_su_occupancyAgreement_sub_tbl_SU1_idx | 110 | ccmsv2.tbl_su.uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_room_bed | | eq_ref | PRIMARY,bedid_UNIQUE,bedid | bedid_UNIQUE | 4 | ccmsv2.tbl_oa_sub.bed_id | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_room | | eq_ref | PRIMARY,roomID_UNIQUE,roomID | roomID_UNIQUE | 4 | ccmsv2.tbl_property_room_bed.RoomID | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_room_floor | | eq_ref | PRIMARY,RoomFloorID_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_property_room.FloorID | 1 | 100.00 | Using where |
| 3 | DERIVED | tbl_property | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_property_room.property_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_oa | | eq_ref | PRIMARY,oA_ID_UNIQUE,Occupancyid | PRIMARY | 4 | ccmsv2.tbl_oa_sub.oa_main_id | 1 | 100.00 | |
| 3 | DERIVED | tbl_oa_occupancy_type | | eq_ref | PRIMARY,occypancyType_ID_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_oa.oA_typeID | 1 | 100.00 | Using where |
| 3 | DERIVED | tbl_oa_os_sub | | eq_ref | PRIMARY,sub_status_id_UNIQUE | sub_status_id_UNIQUE | 4 | ccmsv2.tbl_oa.oA_OperationStatusID | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_room_bed_status | | eq_ref | PRIMARY,bed_status_id_UNIQUE,bed_status_id | PRIMARY | 4 | ccmsv2.tbl_property_room_bed.statusID | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_room_type | | eq_ref | PRIMARY,ID_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_property_room.roomTypeID | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_region | | eq_ref | PRIMARY,regionID_UNIQUE,regionID | PRIMARY | 4 | ccmsv2.tbl_property.property_region_id | 1 | 100.00 | |
| 3 | DERIVED | tbl_oa_added_by | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_oa.oA_added_by_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_oa_updated_by | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_oa.oA_updated_by_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_local_manager | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_property.local_manager_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_regional_manager | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_property.regional_manager_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_su_os_middle | | eq_ref | PRIMARY,middle_status_id_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_su_os_sub.middle_status_id | 1 | 100.00 | |
| 3 | DERIVED | tbl_oa_os_middle | | eq_ref | PRIMARY,middle_status_id_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_oa_os_sub.middle_status_id | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_landlord | | eq_ref | PRIMARY,uuid_UNIQUE | PRIMARY | 110 | ccmsv2.tbl_property.landlord_uuid | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_proposed_to | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_property.proposed_on_behalf_uuid | 1 | 100.00 | Using index |
| 3 | DERIVED | tbl_property_proposed_by | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_property.proposed_by_uuid | 1 | 100.00 | Using index |
| 3 | DERIVED | tbl_landlord_company | | eq_ref | PRIMARY | PRIMARY | 110 | ccmsv2.tbl_property_landlord.company_uuid | 1 | 100.00 | Using index |
| 3 | DERIVED | tbl_oa_os_main | | eq_ref | PRIMARY,idtbl_OperatonStatusMain_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_oa_os_sub.booking_main_status_id | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_occupancy_type | | eq_ref | PRIMARY,occypancyType_ID_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_property.property_occupancy_typeID | 1 | 100.00 | |
| 3 | DERIVED | tbl_property_os_sub | | ref | PRIMARY,propertyStatusID | PRIMARY | 4 | ccmsv2.tbl_property.property_operation_statusID | 1 | 100.00 | Using where; Using index |
| 3 | DERIVED | tbl_property_os_main | | eq_ref | PRIMARY,idtbl_property_operationStatusMain_UNIQUE | PRIMARY | 4 | ccmsv2.tbl_property_os_sub.tbl_property_operationstatus_mainID | 1 | 100.00 | Using index |
| 3 | DERIVED | <derived4> | | ref | <auto_key0> | <auto_key0> | 4 | ccmsv2.tbl_su.safeguarding_type_id | 2 | 100.00 | |
| 3 | DERIVED | <derived5> | | ref | <auto_key0> | <auto_key0> | 4 | ccmsv2.tbl_property.safeguarding_type_id | 2 | 100.00 | |
| 5 | DERIVED | tbl_safeguarding_type | | ALL | | | | | 22 | 100.00 | |
| 4 | DERIVED | tbl_safeguarding_type | | ALL | | | | | 22 | 100.00 | |
| 2 | DERIVED | | | | | | | | | | No tables used |
+---+---------+-------------------------------+--+--------+---------------------------------------------------+----------------------------------------------+-----+----------------------------------------------------------------+--------+--------+--------------------------+
Best Answer
Don't do the extra
SELECT, it is unnecessary.A common technique for initializing the variable:
My opinion on
VIEWs: Don't use them. But if you want to discuss an issue with them, we need to see both theCREATE VIEWand theCREATE TABLEfor the underlying table(s). AlsoEXPLAIN SELECT ....