1

I have the original table of 4 columns, described as follows:

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| FieldID  | varchar(10) | NO   | MUL | NULL    |       |
| PaperID  | varchar(10) | NO   |     | NULL    |       |
| RefID    | varchar(10) | NO   |     | NULL    |       |
| FieldID2 | varchar(10) | NO   | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

I want to run a query with COUNT(*) and GROUP BY :

select FieldID, FieldID2, count(*) from nFPRF75_1 GROUP BY FieldID, FieldID2

I've created indexes on both column FieldID and column FieldID2, however, they seem to be ineffective. I have also tried OPTIMIZE table_name and created redundant indexes on these two columns (as is indicated by other optimization questions), unfortunately it didn't work out either.

Here is what I get from EXPLAIN:

 | id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
 +----+-------------+-----------+------+---------------+------+---------+------+----------+---------------------------------+
 |  1 | SIMPLE      | nFPRF75_1 | ALL  | NULL          | NULL | NULL    | NULL | 90412507 | Using temporary; Using filesort |

I wonder if there's anyway that I can use indexes in this query, or any other way to optimize it. Now it's of very low efficiency since there's lots of lines.

Thanks a lot for the help!

lurker
  • 56,987
  • 9
  • 69
  • 103
Irene W.
  • 679
  • 1
  • 6
  • 15
  • 1
    you may refer this question http://stackoverflow.com/questions/3915574/mysql-optimization-group-by-multiple-keys – amit gupta Aug 17 '15 at 20:20

2 Answers2

2

You should create a multi-column index of (FieldID, FieldID2).

Rob Bailey
  • 1,747
  • 15
  • 18
  • thanks it works!!! i wonder why single-column index wouldn't work in this situation. (excuse me I'm new to sql... ) – Irene W. Aug 17 '15 at 20:48
  • Single-column indexes don't work well in this case because there remains too much work to do at query time. Even if you have an average of only 100 FieldID2's for every FieldID, in order to count each unique combination of FieldID/FieldID2 your database must still look over every unique FieldID2 in order to count them (not just the 100). So even if you have an index on each column separately, it's still too much work to do. If you have a multi-column index, then at insertion time all the grouping is already done, leaving only the "count" execution on the ~100 instances. Hope that helps! – Rob Bailey Aug 17 '15 at 21:10
  • I see, that makes sense! thanks very much for the explanation! – Irene W. Aug 17 '15 at 21:34
0

Create an index of FieldID, FieldID2 if you are grouping by them. That must improve the speed.

Also, I recommend you change count(*) to count('myIntColumn') which improve the speed too.

  • Varchar fields that are only 10 characters in length would not be much of a performance hit. – Rob Bailey Aug 17 '15 at 20:28
  • @RobBailey If she has 90412507 rows, I think It would –  Aug 17 '15 at 20:33
  • sorry I'm a newbee in mysql. can I use int field when the original data of FieldID contain characters other than numbers? something like '173F24A5' ? – Irene W. Aug 17 '15 at 20:39
  • @IreneW. Do what Rob Bailey said, It is a good idea if you are only performing that query and grouping those fields. Remove all indexes you have on the table and then select both column (fieldId, fieldId2) and create an index –  Aug 17 '15 at 20:47
  • @AlbertoFernández yes I've tried that and it works! thanks for the advice for the change of count(*) too! – Irene W. Aug 17 '15 at 20:53