Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.2.0
-
11.1.2-MariaDB-1:11.1.2+maria~ubu2204
Description
Version
11.1.2-MariaDB-1:11.1.2+maria~ubu2204
Table
CREATE TABLE "qms_occurrences" ( |
"id" int(11) NOT NULL, |
"ref_created_by" int(11) NOT NULL, |
"dt_created" date NOT NULL, |
"title" varchar(160) NOT NULL, |
"sector" varchar(80) NOT NULL, |
"description" text NOT NULL, |
"reason" varchar(160) NOT NULL, |
"disposition" varchar(160) NOT NULL, |
"state" smallint(6) NOT NULL, |
"ref_accepted_by" int(11) NOT NULL, |
"ref_analised_by" int(11) NOT NULL, |
"dt_analyse_due" date DEFAULT NULL, |
"dt_analysed" date DEFAULT NULL, |
"ref_approved_by" int(11) NOT NULL, |
"ref_executed_by" int(11) NOT NULL, |
"execute_state" smallint(6) DEFAULT NULL, |
"dt_execute_estim_start" date DEFAULT NULL, |
"dt_execute_estim_end" date DEFAULT NULL, |
"dt_execute_real_start" date DEFAULT NULL, |
"dt_execute_real_end" date DEFAULT NULL, |
"ref_verified_by" int(11) NOT NULL, |
"archived" tinyint(4) NOT NULL, |
"ref_category" int(11) NOT NULL, |
"ref_type" int(11) NOT NULL, |
"serial" varchar(40) NOT NULL, |
"ref_actionplan_analysis" int(11) NOT NULL, |
"priority" smallint(6) NOT NULL, |
"ref_previous" int(11) NOT NULL, |
"restriction" int(11) NOT NULL, |
"workflow" smallint(6) NOT NULL, |
"ref_owner" int(11) NOT NULL, |
"dt_approve_due" date DEFAULT NULL, |
"dt_execute_due" date DEFAULT NULL, |
"ref_actionplan_disposition" int(11) NOT NULL, |
"dt_verify_due" date DEFAULT NULL, |
"dt_last_verification" date DEFAULT NULL, |
"context_count" int(11) NOT NULL, |
"initiative_count" int(11) NOT NULL, |
"ref_event_accepted" int(11) NOT NULL, |
"ref_event_approved" int(11) NOT NULL, |
"ref_event_executed" int(11) NOT NULL, |
"ref_event_verified" int(11) NOT NULL, |
"next_update" date DEFAULT NULL, |
"options" varchar(32) NOT NULL, |
"stage_current" smallint(6) NOT NULL, |
"stage_origin" smallint(6) NOT NULL, |
"dt_accept_due" date DEFAULT NULL, |
"analysis_family" int(11) NOT NULL, |
"analysis_source" int(11) DEFAULT NULL, |
PRIMARY KEY ("id"), |
KEY "qms_occurrences_category" ("ref_category"), |
KEY "qms_occurrences_restriction" ("restriction"), |
KEY "qms_occurrences_state" ("state"), |
KEY "qms_occurrences_type" ("ref_type"), |
KEY "qms_occurrences_disposition" ("ref_actionplan_disposition"), |
KEY "qms_occurrences_analysis" ("ref_actionplan_analysis"), |
KEY "qms_occurrences_created" ("ref_created_by"), |
KEY "qms_occurrences_previous" ("ref_previous"), |
KEY "qms_occurrences_accepted" ("ref_accepted_by"), |
KEY "qms_occurrences_analised" ("ref_analised_by"), |
KEY "qms_occurrences_approved" ("ref_approved_by"), |
KEY "qms_occurrences_executed" ("ref_executed_by"), |
KEY "qms_occurrences_verified" ("ref_verified_by"), |
KEY "qms_occurrences_owner" ("ref_owner"), |
KEY "qms_occurrences_evaccepted" ("ref_event_accepted"), |
KEY "qms_occurrences_evapproved" ("ref_event_approved"), |
KEY "qms_occurrences_evexecuted" ("ref_event_executed"), |
KEY "qms_occurrences_evverified" ("ref_event_verified"), |
KEY "qms_occurrences_serial" ("serial"), |
KEY "qms_occurrences_workflow" ("workflow"), |
KEY "qms_occurrences_created_on" ("dt_created"), |
KEY "qms_occurrences_archived" ("archived"), |
KEY "qms_occurrences_dt_analysed" ("dt_analysed"), |
KEY "qms_occurrences_current" ("stage_current"), |
KEY "qms_occurrences_origin" ("stage_origin"), |
KEY "qms_categories_state" ("state"), |
KEY "ix_qms_occu_stage_origin" ("stage_origin"), |
KEY "ix_qms_occu_stage_current" ("stage_current"), |
KEY "ix_qms_occurrences_state_id" ("state", "id") |
)
|
Query with column indexed isnt using index, that situation has performance issue:
select O.state from qms_occurrences O where O.serial like '%TSI-85496%' |
id select_type table type possible_keys key key_len ref rows filtered Extra
|
-- ----------- ----- ---- ------------- ---- ------- ---- ------ -------- -----------
|
1 SIMPLE O ALL NULL NULL NULL NULL 189266 100.00 Using where
|
select O.serial from qms_occurrences O where O.serial like '%TSI-85496%' |
id select_type table type possible_keys key key_len ref rows filtered Extra
|
-- ----------- ----- ----- ------------- ---------------------- ------- ---- ------ -------- ------------------------
|
1 SIMPLE O index NULL qms_occurrences_serial 42 NULL 189266 100.00 Using where; Using index
|