-1

I have a Table which has 2 Million Records and No primary or Unique Keys. I added Two Indexes and I'm expecting an Index to be used for my Select Query.

When I saw the Explain Plan , Optimizer is not using any of the Indexes I created.

I am quering a broader range of data which I cannot reduce as per my exisitng Use case and I want Optimizer to Pick Indexes.

I don't want to Use FORCE INDEX Clause, I wanted to know how i can make Optimizer to use INDEXS

Below is the Table SCHEMA

CREATE TABLE EVENT_DETAILS
(
  ORG_ID CHAR(32) NOT NULL,
  CONFIG_KEY CHAR(32) NOT NULL,
  COMPONENT_NAME VARCHAR(512) NOT NULL,
  REQUEST_ID  CHAR(32) NOT NULL,
  UPDATE_DATE INT NOT NULL,
  UPDATE_TIME INT NOT NULL,
  CLIENT CHAR(16) NOT NULL,
  STATUS INT NOT NULL,
  EVENT_DATA VARCHAR(1024),
  MESSAGE VARCHAR(512),
  INSERTED_TIME     TIMESTAMP      DEFAULT CURRENT_TIMESTAMP NOT NULL
);


CREATE TABLE DOMAIN_KEYS (
  DOMAIN_KEY char(32) COLLATE utf8_unicode_ci NOT NULL,
  DOMAIN_IDENTITY varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  DOMAIN_DISPLAY_NAME varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  ORG_ID char(32) COLLATE utf8_unicode_ci NOT NULL,
  LOCATION_KEY int(11) DEFAULT '0',
  DESCRIPTION varchar(4000) COLLATE utf8_unicode_ci DEFAULT NULL,
  CATEGORY int(11) NOT NULL DEFAULT '1',
  ENVIRONMENT_TYPE int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (DOMAIN_KEY)
);

CREATE TABLE ORGANISATIONS (
  ORG_ID char(32) NOT NULL,
  ORG_IDENTITY varchar(256) NOT NULL,
  ORG_NAME varchar(256) NOT NULL,
  ORG_DESC varchar(256) DEFAULT NULL,
  PARENT_ORG_ID char(32) NOT NULL,
  PRIMARY KEY (ORG_ID)
);

Below are the Indexes created

CREATE INDEX MYINDEX1 ON EVENT_DETAILS (ORG_ID,CONFIG_KEY,COMPONENT_NAME,UPDATE_DATE);
CREATE INDEX MYINDEX2 ON EVENT_DETAILS (UPDATE_DATE,COMPONENT_NAME);

Below is the Query

SELECT ED.ORG_ID, 
       ORG.ORG_IDENTITY, 
       ORG.ORG_NAME, 
       ED.CONFIG_KEY, 
       REPLACE(REPLACE(ED.COMPONENT_NAME,'-CURRENT',''),'-HISTORICAL','') AS COMP_NAME,
       DO.DOMAIN_IDENTITY,
       DO.DOMAIN_DISPLAY_NAME,
       GROUP_CONCAT(ED.STATUS ORDER BY ED.INSERTED_TIME DESC) AS ALL_STATUS 
FROM EVENT_DETAILS ED , 
     ORGANISATIONS ORG , 
     DOMAIN_KEYS DO   
WHERE UPDATE_DATE > '20191205' 
  AND ORG.ORG_ID = ED.ORG_ID 
  AND DO.DOMAIN_KEY=ED.CONFIG_KEY 
  AND DO.LOCATION_KEY <> 0 
GROUP BY ED.ORG_ID,
      ORG.ORG_IDENTITY, 
      ORG.ORG_NAME, 
      ED.CONFIG_KEY,
      DO.DOMAIN_IDENTITY,
      DO.DOMAIN_DISPLAY_NAME,
      COMP_NAME 
HAVING ALL_STATUS LIKE '0,0,0%';

enter image description here

I wanted to Reduce the Temp Tables created as well , because I observed Free Local Storage of my DB Machine going down when the Query is executing with high volumes of data

Some one please suggest the required Schema Changes and if Query can be Optimized.

Query Execution Break Up

enter image description here

  • @RickJames Innodb is the Engine. Query takes on avg around 95 Seconds to complete and out of which 91 Seconds MySQL is in Creating Sort Index Phase, Please find the Break Up added – user3865748 Dec 20 '19 at 6:27
  • How much RAM? What is the value of innodb_buffer_pool_size? How many rows are returned by the query? – Rick James Dec 20 '19 at 6:36
  • 350 Records in DOMAIN_KEYS have Location Key as 0 while the Total Number of Records are 900. – user3865748 Dec 20 '19 at 8:44
0
  • Use InnoDB.
  • Have a PRIMARY KEY on every table
  • Don't blindly use VARCHAR(256) find a reasonable limit.
  • Use the JOIN .. ON syntax, not the commalist syntax.
  • Use DATETIME instead of a pair of INTs
  • CHAR(32) smells like some kind of hash -- such is very inefficient for indexing and JOINing.
  • If those are hex strings, use CHARACTER SET ascii, not utf8.
  • Hashes can be made smaller with HEX and UNHEX and using BINARY(16).
  • If more than about 20% of ED satisfies UPDATE_DATE > '20191205', the Optimizer will eschew an index starting with that column for a table scan.
|improve this answer|||||
  • yes more than 20% of ED Satisfies UPDATE_DATE > '20191205', So what are the good practices that are done when a huge table is queried for a broader range so that Optimizer picks the Index ?? – user3865748 Dec 20 '19 at 8:40
  • @user3865748 - First, trust the Optimizer; it thinks that a table scan will be faster than using the index. Second, be skeptical, sometimes the Optimizer estimates wrong. Don't use FORCE INDEX; it may help today, but hurt tomorrow when the data distribution has changed. In the end, look around for other things that may "help", which is what my answer focused on. – Rick James Dec 20 '19 at 18:51
0

MySQL will only use one index per table per query, on a composite index it will use the columns from left to right, the where clause needs to have the columns specified in the same order. I'd experiment with creating an index on ED that covers ORG_ID and UPDATE_DATE, alter the order of the columns so the filter that is most selective comes first.

|improve this answer|||||
  • The point here I observed is when Optimizer comes to Know that User Selected a Broader Range of data (Like Query Selecting 30% of complete data and Operating on it) Optimizer is not using the Index and it is going for Full table Scan as the Cost of going for Full Table Scan is lesser than using the Index. – user3865748 Dec 20 '19 at 9:29
  • If the combination of UPDATE_DATE and ORG_ID is still returning a large portion of the data you might be stuck. As a slight aside, I do find MySQL to be pretty slow for grouping and sorting operations, especially if an index is used on the associated table earlier in the query. – JHackney Dec 20 '19 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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