MySQL – Query Regarding Covering Index with Primary Key

MySQL

I have the following table (there are more columns in the real one).

CREATE TABLE `test`.`j_data` (
`j_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`j_name` VARCHAR(80) NULL DEFAULT NULL
PRIMARY KEY (`j_id`));

We regularly run queries where the main column used is the PK, e.g.

SELECT * FROM j_data WHERE j_id = 123;

But we are now looking to run a lot of queries like:

SELECT j_id FROM j_data WHERE j_name = xxx;

Would this benefit from a covering index on j_name and j_id.

ADD INDEX `j_name_id` (`j_name` ASC, `j_id` ASC);

Or would an index on j_name only suffice? It's quite a large table so I don't want to spend time adding the index if it is unlikely to help.

Best Answer

Short answer: Yes.

Long answer:

You are using InnoDB, correct? (Run SHOW CREATE TABLE j_data to find out what the Engine is.)

In InnoDB, each "secondary index" (contrast "primary key") silently contains a copy of the primary key.

So... INDEX(j_name) happens to be identical to INDEX(j_name, j_id).

Tip: At some point you will realize that prefixing all columns with the table name ("j_") is unnecessary clutter.

Related Solutions

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

Based on the previous question and this, the following should do what you want. As with Pieter's update it doesn't assume that the ID's are in sequential order, rather it uses the ReqTime to determine order.

UPDATE Visits R
  INNER JOIN (
    SELECT ID,
      TIME_TO_SEC(TIMEDIFF(
        (SELECT B.ReqTime
         FROM Visits B
         WHERE B.ReqTime > A.ReqTime
         ORDER BY B.ReqTime
         LIMIT 1
         )
        ,A.ReqTime)) Duration
    FROM Visits A
   ) N ON R.ID = N.ID
SET R.Duration = N.Duration
WHERE Duration IS NULL;

Best Answer

Related Solutions

Mysql – Adding index to large thesql tables

Mysql – #1093 – You can’t specify target table ‘R’ for update in FROM clause

Related Question