https://bugzilla.wikimedia.org/show_bug.cgi?id=29287
--- Comment #3 from Reedy <[email protected]> 2011-06-06 18:51:08 UTC --- Adding mysql> CREATE INDEX repo_path ON mw_code_paths (cp_repo_id, cp_path); Query OK, 1177840 rows affected (10 min 54.57 sec) Records: 1177840 Duplicates: 0 Warnings: 0 gives mysql> describe select distinct cp_path from mw_code_paths where cp_repo_id = 1 AND cp_path LIKE '/trunk/%'; +----+-------------+---------------+------+-------------------+---------+---------+-------+--------+-------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+-------------------+---------+---------+-------+--------+-------------------------------------------+ | 1 | SIMPLE | mw_code_paths | ref | PRIMARY,repo_path | PRIMARY | 4 | const | 635909 | Using where; Using index; Using temporary | +----+-------------+---------------+------+-------------------+---------+---------+-------+--------+-------------------------------------------+ 1 row in set (0.00 sec) rather than mysql> describe select distinct cp_path from mw_code_paths where cp_repo_id = 1 AND cp_path LIKE '/trunk/%'; +----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------------------------------------------+ | 1 | SIMPLE | mw_code_paths | ref | PRIMARY | PRIMARY | 4 | const | 657813 | Using where; Using index; Using temporary | +----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------------------------------------------+ 1 row in set (0.00 sec) It took 10 minutes to add the index, but seems possibly worthwhile, as we're saving 24,000 rows.. mysql> drop index repo_path on mw_code_paths; Query OK, 1177840 rows affected (1 min 28.05 sec) Records: 1177840 Duplicates: 0 Warnings: 0 Unless there's a better index we can have? Either way it gives using temporary, which isn't nice... -- Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email ------- You are receiving this mail because: ------- You are the assignee for the bug. You are on the CC list for the bug. _______________________________________________ Wikibugs-l mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
