Reputation: 919
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:
So I'm just trying to understand what could be some possibile improvements:
Upvotes: 0
Views: 38
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
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