3

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. Without using variables

With using variables

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           |
+---+---------+-------------------------------+--+--------+---------------------------------------------------+----------------------------------------------+-----+----------------------------------------------------------------+--------+--------+--------------------------+

1 Answer 1

0

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 ....

3
  • Hi & thx for the input. It didn't help either. I've added the exlplain result above. If viewes aren't good idea whats the purpose of views and whats the alternative? I might well place new question when i feel so.
    – krish KM
    Jan 14, 2017 at 1:01
  • A VIEW cannot run faster than the underlying SELECT. A VIEW can be used to hide thing from casual users, or to compensate for a schema change.
    – Rick James
    Jan 14, 2017 at 4:32
  • That's a long Explain! Dig into the code and find what leads to the first 2 lines and the last 3. They seem to be the worst; lets focus on them.
    – Rick James
    Jan 14, 2017 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.