Geek Answers Handbook

MySQL is a slow query with joining, although EXPLAIN shows a good plan

I have the following scenario: in a MySQL database, I have 2 MyISAM tables, one of which contains 4.2 million rows and the other contains 320 million rows. Below is the table layout:

Table 1 (rows 4.2M)

F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY
f2 varchar(40)
f3 varchar(40)
f4 varchar(40)
f5 varchar(40)
f6 smallint(6)
f7 smallint(6)
f8 varchar(40)
f9 varchar(40)
f10 smallint(6)
f11 varchar(10)
f12 tinyint(4)
f13 smallint(6)
f14 text

Table 2 (rows 320M)

F1 INTEGER UNSIGNED NOT NULL PRIMARY KEY
f2 INTEGER UNSIGNED NOT NULL

Table 2 is in a different database, but I am using a stored procedure that queries two tables. The relationship between the two tables is that for Table1.F1 it can be up to approx. 100 rows in table2.F1 (foreign key) that match, and the value for Table2.f2 will be returned for these mapped keys. I have index IX1 (f2 (15), f3 (10)) in table 1 and index IX2 (F1, f2) and IX3 (f2) in table 2

I execute the following queries:

SELECT g.F1
FROM DB1.Table1 g 
INNER JOIN DB2.Table2 gp ON g.F1 = gp.F1 
WHERE (gp.f2 = 452677825) AND
(g.f2 = 'A string value') LIMIT 0,56

(< 1s), , g.F2, , 11, 30 . , . EXPLAIN SELECT.

1, 'SIMPLE', 'g', 'ref', 'PRIMARY,IX1', 'IX1', '17', 'const', 901, 'Using where'
1, 'SIMPLE', 'gp', 'ref', 'IX3,IX2', 'IX2', '8', 'DB1.g.F1,const', 1, 'Using index'

. 2000, , . , 99,9% " ". - , , ?

,

+5
3

, :

, g.F2 - . MySQL ( ), , . . (. )

, g gp (, gp?) where, . (. )

, , , , ram ( swap - ), explain, explain .

+1

my.cnf, , , key_buffer_size, MyISAM .MYI, (, ls -lh/var/lib/mysql/dbname/*.MYI), , . MySQL 25% , .

0

, Table1.F1 . 100 2.F1

In order to clarify whether there is a link between Table1.F1and Table2.F1one-to-one or one-to-many? For me, this statement implies "one to many", but from the scheme, each of the fields is primary (that is, unique) keys.

In any case, I suspect that the uniform is g.f2(15)uneven and that when statistical outliers are affected, productivity deteriorates accordingly.

Make the results

SELECT f2(15) AS f2_15, COUNT(*) AS cnt
FROM Table1
GROUP BY f2(15) 
ORDER BY cnt DESC

show some significant emissions?

0
source

More articles:


All Articles