I have a table like this in MySQL with large number rows.

TABLE `Table_1` (
  `InternalId` int(10) NOT NULL AUTO_INCREMENT,
  `Id` varchar(50) NOT NULL,
  `Test_Column` varchar(100)  NULL,
  `Month` int(2) NOT NULL,
  `Year` int(2) NOT NULL,
  PRIMARY KEY (`InternalId`),
  KEY `IX_Id` (`Id`)
) ENGINE=InnoDB;

I need to query the table regularly for rows where Month / Year is in the past and Test_Column is not NULL and perform some action on them using a query like

Select * From Table_1 where Test_Column is NULL AND (Year < @currentYear OR (Year = @currentYear AND Month = @currentMonth))

What's the best way of creating indexes for these columns? Do I simply create an index on all 3 columns Year, Month, Test_Column and is the fact that Test_Column nullable something to give consideration to?

CodePudding user response：

Here's what I tried. Tested in MySQL 8.0.28:

alter table table_1 
  add column test_column_is_null bool as (test_column is null),
  add column date date as (date(concat(`Year`, '-', `Month`, '-01'))),
  add index (test_column_is_not_null, date);

Then I checked the EXPLAIN report for your query:

EXPLAIN 
SELECT * FROM Table_1 WHERE test_column_is_null = 1 
  AND date < DATE_FORMAT(NOW(), '%Y-%m-01')\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Table_1
   partitions: NULL
         type: range
possible_keys: test_column_is_null
          key: test_column_is_null
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

This shows that it uses the index I added, and the key_len shows that it is using both columns of the index (1 byte for the boolean, 3 bytes for the date, plus there's some bytes for nullability).

CodePudding user response：

Where is new data inserted in a table?

There is no general rule that states, in a clear-cut way, where to insert a new data.

How do we look for existing data? THERE IS NO OTHER CHOICE. WE HAVE TO GO THROUGH THE WHOLE TABLE UNTIL WE FIND IT OR "GET TO THE END WITHOUT BEING FOUND".

This is a big problem when tables are large.

The big difference: a table with 1,000 data where we are looking for a non-existent data.

In an unsorted table : 1,000 checks.
In a sorted table maximum : 10 checks.

Important : The indexes are a complementary and parallel structure, i.e. the data in the table are neither moved nor sorted.

Types of Indexes in MySQL.

Primary Key :

A primary key is an index set on a field in which each value is unique and none of the values is NULL.
The primary key can consist of more than one attribute.
Only one primary key can exist in a table.
When you create a primary key with data already existing in the table, you have to ensure that the data is not NULL and is not repeated.

How to create a primary key?

When creating the table :

CREATE TABLE tablename (
   field1 type NOT NULL, 
   field2 type, 
   ......,
   PRIMARY KEY (field1, ....) 
);

Subsequently with the command ALTER TABLE :

ALTER TABLE tablename ADD PRIMARY KEY (field1, ....);

Modifying the existing field(s) :

ALTER TABLE tablename MODIFY field INT NOT NULL, ADD PRIMARY KEY field;

Ordinary index :

They are indexes that act on any field/s and allow duplicate values. Their mission is to speed up the search when this/these fields are involved in the query.

CREATE TABLE tablename (
    field1 type,
    field2 type, 
    ......, 
    INDEX indexname (fieldN, ....) 
);

You can create several indexes at the same time:

CREATE TABLE tablename (
   field1 type , 
   field2 type, 
   ......, 
   INDEX indexname1 (fieldN, ....), 
   INDEX indexname2 (fieldM, ....), 
   ......
);

Create the index(es) after the table has been created:

ALTER TABLE tablename ADD INDEX indexname (field1, ....);

CREATE INDEX indexname ON tablename (field1, ....);

To view the indexes of a table :

SHOW INDEX FROM tablename;
SHOW COLUMNS FROM tablename;

Unique Index :

A unique index is the same as an ordinary index except that duplicate values are NOT allowed.

Unlike the primary key, several can exist in the same table and allow values.

How to create a unique index?

When creating the table :

CREATE TABLE tablename (
    field1 type, 
    field2 type, 
    ......,
    UNIQUE (fieldN, ....)
);

Subsequently with the command ALTER TABLE :

ALTER TABLE tablename ADD UNIQUE indexname (field1, ....);

Specific order of creation :

CREATE UNIQUE INDEX indexname ON tablename (field1,....);

Auto-increment or auto-increment index :

Auto increment fields are useful because they allow you to increment the value of a field automatically each time a new record is inserted. Only one field can be incremented in a record and that field must be the PRIMARY KEY OR A SINGLE INDEX, and also of NUMERICAL type.

CREATE TABLE tablename (
     field1 INT AUTO_INCREMENT,
     field2 type, 
     ...,
     PRIMARY KEY (field1)
);

When the table is already created :

ALTER TABLE tablename MODIFY field1 type AUTO_INCREMENT;

ALTER TABLE TABLENAME MODIFY field1 INT AUTO_INCREMENT, ADD PRIMARY KEY (field1);

All this information has been taken from my class notes : https://docs.google.com/document/d/1FZAF0P1tI_BaqNbZB2gbHax1LrpjBiSjtfCbrkKqEm8/edit?usp=sharing

Note : Adapt all that I have put in your case the ordinary index to your fields