It,s simple.
Look at cardinality on last two rows of statement 'show index from e2s'.
You have same cardinality on co_id and mod_time both.
My solution stands on the fact that mod_time will have much higher
selectivity in real data than co_id. (IMO)
So, I suppose that you have wrong testing data.
Filip
Thank you for answering.
I tried your suggestion and it's got fast, but still slow and
could not get rid of "Using temporary; Using filesort".
I don't understand why this simple query is so slow...
------------------------------------------------------------------------------------------------------------
mysql> ALTER TABLE employees2skills ADD INDEX(co_id, mod_time);
Query OK, 1000000 rows affected (24.75 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> analyze table employees2skills;
+-----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| test.employees2skills | analyze | status | OK |
+-----------------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> show index from employees2skills;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null |
Index_type | Comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employees2skills | 0 | PRIMARY | 1 | emp_id
| A | 1000638 | NULL | NULL | | BTREE |
|
| employees2skills | 0 | PRIMARY | 2 | skill_id
| A | 1000638 | NULL | NULL | | BTREE |
|
| employees2skills | 1 | skill_id | 1 | skill_id
| A | 18 | NULL | NULL | | BTREE |
|
| employees2skills | 1 | mod_time | 1 | mod_time
| A | 18 | NULL | NULL | | BTREE |
|
| employees2skills | 1 | co_id | 1 | co_id
| A | 18 | NULL | NULL | | BTREE |
|
| employees2skills | 1 | co_id | 2 | mod_time
| A | 18 | NULL | NULL | | BTREE |
|
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
mysql> 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 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+-----------+-------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql> 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 (1.70 sec)
On 3/3/07, Filip Krejci <[EMAIL PROTECTED]> wrote:
Hi,
if your selectivity is very low, try to use multiple index on e2s(co_id,
mod_time) and force this index in query.
Filip
>
> employees2skills:
> INSERT INTO employees2skills SELECT id, 1, 2, NOW() FROM employees;
> # Yes, 1000000 have same skill_id, co_id, and even mod_time.
> # selectivity is extreamly low
>
--
Filip Krejci <[EMAIL PROTECTED]>
Vini, vidi, Linux!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Filip Krejci <[EMAIL PROTECTED]>
Microsoft gives you Windows... Linux gives you the whole house.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
