Sfrgttk

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           |

+---+---------+-------------------------------+--+--------+---------------------------------------------------+----------------------------------------------+-----+----------------------------------------------------------------+--------+--------+--------------------------+

Don't do the extra SELECT, it is unnecessary.

SELECT 

    @row_nummer:= @row_nummer+1, -- this is just a test not final code

    delete_me_test_.*

from delete_me_test_;

A common technique for initializing the variable:

SELECT 

    @row_nummer:= @row_nummer+1, -- this is just a test not final code

    delete_me_test_.*

from delete_me_test_

JOIN ( SELECT @row_nummer := 0 );

My opinion on VIEWs: Don't use them. But if you want to discuss an issue with them, we need to see both the CREATE VIEW and the CREATE TABLE for the underlying table(s). Also EXPLAIN SELECT ....