I agree that my testing data is wrong, but what I was wondering is why the query gets slow only when using join, although without join it gives me lightning answer.
BTW, you gave me a big hint! I didn't know about 'cardinality', so searched on the web with the word 'cardinality', which leads me to http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html This page says "MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using ANALYZE TABLE. In the few cases that ANALYZE TABLE doesn't produce values good enough for your particular tables, you can use FORCE INDEX with your queries to force the use of a particular index, or set the max_seeks_for_key system variable to ensure that MySQL prefers index lookups over table scans. " So I added "FORCE INDEX" to my query like this. SELECT * FROM employees2skills e2s FORCE INDEX (mod_time ) 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 | e2s | index | NULL | mod_time | 4 | NULL | 1000638 | | | 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | test.e2s.co_id | 1 | | +----+-------------+-------+--------+---------------+----------+---------+----------------+---------+-------+ 2 rows in set (0.00 sec) it's stunning... So thanks to you, I can solve my problem, thanks you! but I'm not quite sure what's going on. Could you give me some hint please? On 3/3/07, Filip Krejci <[EMAIL PROTECTED]> wrote:
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]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
