CODEWITHSUNDEEP
mysqldatabasejoinindexingquery-performance
UgoL
UgoL

Reputation: 919

Optimize speed of Mysql JOIN query

I have 2 tables called T1 made of 1.6mln of rows and T2 made of 4.6mln of rows with with one-to-many relationship.

The CREATE STMT of T1 is:

CREATE TABLE `T1` (
  `field_1` text,
  `field_2` text,
  `field_3` decimal(10,6) DEFAULT NULL,
  `field_4` decimal(10,6) DEFAULT NULL,
  `field_4` decimal(10,6) DEFAULT NULL,
  `field_5` text,
  `field_6` text,
  `field_7` text,
  `field_8` double DEFAULT NULL,
  `field_9` text,
  `field_10` text,
  `field_11` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The CREATE STMT of T2 is:

CREATE TABLE `T2` (
  `field_1` int(11) DEFAULT NULL,
  `field_2` text,
  `field_3` text,
  `field_4` text,
  `field_5` text,
  `field_6` text,
  `field_7` text,
  `field_8` text,
  `field_9` text,
  `field_10` text,
  `field_11` text,
  `field_12` text,
  `field_13` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I don't have set any kind of indexes or any particular constraints for now, but the T1.field_1 should be my ideal key and can be joined with T2.field_2 field.

If I decide to make a JOIN like:

SELECT * FROM T1
JOIN T2
ON T1.field_1=T2.field_2
WHERE T1.=2130100;

The benchmark is really high. This is the EXPLAIN: explain

So I'm just trying to understand what could be some possibile improvements:

Upvotes: 0

Views: 38

Answers (2)

Rick James
Rick James

Reputation: 142560

Since you are using latin1, switch t1.field_1 and t2.field_2 to VARCHAR of no more than 767. Use the shortest value that is not likely to be exceeded. Do likewise for all the other TEXT columns. (If you need >767, stick with TEXT.)

Then add two indexes:

T1:  INDEX(??) -- whatever column you are using in the `WHERE`
T2:  INDEX(field_2)

If the column in T1 is an INT, then 2130100 is OK. But if it is TEXT (or soon to be VARCHAR(..), then quote it: "2130100". The should prevent a surprising and unnecessary table scan of T1.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133410

In you where condition you missed the column name i assume the columns is named your_col

Starting form mysql 5.0.3 varchar can be up 65,535 so you could try using varchar instead of text when possibile

for indexing there are limitation on the size of the index max key length is 767 byte ( assuming 3 bytes for each utf8 character. so about 250 utf8 char )

the column candidate for indexing must respected these limit if this is possible then you could add index on

table t2 colums fiedl_2

and on 

table t1 a composite index on  column (Your_col, field_1)

these are the columns involved in where and ON clause 

  SELECT * FROM T1
  JOIN T2
  ON T1.field_1=T2.field_2
  WHERE T1.Your_col=2130100;

Upvotes: 2

Related Questions