Hi list,
I have 4 simple table.
---------------------------------------------------
CREATE TABLE employees(
id INT NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE = InnoDB;
CREATE TABLE skills(
id INT NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE = InnoDB;
CREATE TABLE companies(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE = InnoDB;
CREATE TABLE employees2skills(
emp_id INT NOT NULL,
skill_id INT NOT NULL,
co_id INT NOT NULL,
mod_time TIMESTAMP NOT NULL,
PRIMARY KEY(emp_id, skill_id),
FOREIGN KEY(emp_id)
REFERENCES employees(id),
FOREIGN KEY(skill_id)
REFERENCES skills(id),
FOREIGN KEY(co_id)
REFERENCES companies(id)
) ENGINE = InnoDB;
ALTER TABLE employees2skills ADD INDEX(mod_time);
---------------------------------------------------
employees and employees2skills table are
big table containing 1000000 rows, others are small, just 10 rows or so.
When I join employees and employees2skills, order by query is very fast,
but when I join employees2skills and small companies table,
the query gets very slow.
See below.
---------------------------------------------------
SELECT * FROM employees e INNER JOIN employees2skills e2s ON e.id = e2s.emp_id
ORDER BY e2s.mod_time limit 5;
+-----+------------+--------+----------+-------+---------------------+
| id | name | emp_id | skill_id | co_id | mod_time |
+-----+------------+--------+----------+-------+---------------------+
| 183 | fffmudmykn | 183 | 1 | 2 | 2007-03-03 17:59:35 |
| 184 | qg{keoohdr | 184 | 1 | 2 | 2007-03-03 17:59:35 |
| 185 | qoowibsgum | 185 | 1 | 2 | 2007-03-03 17:59:35 |
| 186 | hxfqnduzt{ | 186 | 1 | 2 | 2007-03-03 17:59:35 |
| 187 | kyyvuzyqqo | 187 | 1 | 2 | 2007-03-03 17:59:35 |
+-----+------------+--------+----------+-------+---------------------+
5 rows in set (0.00 sec)
SELECT * FROM employees2skills e2s INNER JOIN companies c ON c.id =
e2s.co_id ORDER BY e2s.mod_time limit 5;
+--------+----------+-------+---------------------+----+-------+
| emp_id | skill_id | co_id | mod_time | id | name |
+--------+----------+-------+---------------------+----+-------+
| 183 | 1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
| 184 | 1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
| 185 | 1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
| 186 | 1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
| 187 | 1 | 2 | 2007-03-03 17:59:35 | 2 | Apple |
+--------+----------+-------+---------------------+----+-------+
5 rows in set (4.03 sec)
EXPLAIN SELECT * FROM employees2skills e2s INNER JOIN companies c ON
c.id = e2s.co_id ORDER BY e2s.mod_time limit 5;
+----+-------------+-------+-------+---------------+-------+---------+-----------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+-----------+-------+----------------------------------------------+
| 1 | SIMPLE | c | index | PRIMARY | name | 62 |
NULL | 7 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | e2s | ref | co_id | co_id | 4 |
test.c.id | 55591 | |
+----+-------------+-------+-------+---------------+-------+---------+-----------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
---------------------------------------------------
Probably I'm doing something wrong, but I couldn't figure it out.
Could you please tell me the reason why the above query is slow,
and the solution to get this faster?
Any comments, suggestions and pointers would be greatly appreciated.
Thank you in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
