# INDEX Help pleaseeee **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [March 18, 2009, 7:29am UTC](https://forums.percona.com/t/index-help-pleaseeee/1120 "2009-03-18T07:29:54Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![floodster](https://avatars.discourse-cdn.com/v4/letter/f/67e7ee/32.png) [@floodster](https://forums.percona.com/u/floodster)\ **Post date:** [March 18, 2009, 7:29am UTC](https://forums.percona.com/t/index-help-pleaseeee/1120/1 "2009-03-18T07:29:54Z")
Hi, My first post here so be gentle confused: I have a query which joins to a tmp table BUT before I even go there I ran the EXPLAIN on the query that creates the tmp table & found it isn’t using any indexes?? The code is CREATE TEMPORARY TABLE tmpSELECT EDPG\_SchAttYearCode as School\_Year, EDPG\_SchAttTermCode as School\_Term,EDPG\_L0Code as Ethnicity, EDPG\_YearCode as Year\_Code, EDPG\_Gender as Gender, COUNT(DISTINCT(EDPG\_UPN)) AS Total\_Pupils FROM EDPG\_PupilGrouped LEFT JOIN Lookup\_PcodeGeo ON EDPG\_Postcode = LKPC\_Postcode WHERE LKPC\_District=“Sandwell” GROUP By School\_Year, School\_Term, Ethnicity, Year\_Code , Gender; I have an index (EDPG\_Idx1) on the table edpg\_pupilgrouped using the columns in this order; EDPG\_SchAttYearCode (Cardinality:1)EDPG\_SchAttTermCode (Cardinality:5)EDPG\_L0Code as Ethnicity (Cardinality:2)EDPG\_YearCode as Year\_Code (Cardinality:17)EDPG\_Gender as Gender (Cardinality:2) When I run the EXPLAIN it returns id select\_type table type possible\_keys key key\_len ref rows Extra1 SIMPLE EDPG\_PupilGrouped ALL EDPG\_Postcode NULL NULL NULL 243425 Using filesort1 SIMPLE Lookup\_PcodeGeo eq\_ref PRIMARY,LKPC\_District,LKPC\_Postcode PRIMARY 8 development.EDPG\_PupilGrouped.EDPG\_Postcode 1 Using where Can anybody help me or explain how indexes work I’m pulling my hair out mad: ---
**Author:** ![floodster](https://avatars.discourse-cdn.com/v4/letter/f/67e7ee/32.png) [@floodster](https://forums.percona.com/u/floodster)\ **Post date:** [March 18, 2009, 9:03am UTC](https://forums.percona.com/t/index-help-pleaseeee/1120/2 "2009-03-18T09:03:55Z")
I’ve just read somewhere that if your using a SUM or COUNT in a SELECT statment then Mysql ignores any indexes, is this true?? ---
**Author:** ![dsuehrin](https://avatars.discourse-cdn.com/v4/letter/d/a587f6/32.png) [@dsuehrin](https://forums.percona.com/u/dsuehrin)\ **Post date:** [March 18, 2009, 9:10am UTC](https://forums.percona.com/t/index-help-pleaseeee/1120/3 "2009-03-18T09:10:59Z")
Indexes are used to quickly find the rows you are looking for, based on the fields in your WHERE clause. For example, if you had the query SELECT \* FROM table1 WHERE id = 3 and you had an index on the id field, the database could use that index to find where the rows are in the table. If you did not have an index on id, the database would have to look through every record in the table to find the matches. In your query, the field you are searching on in your WHERE clause is “LKPC\_District”, so this would be the field you would want to look into putting an index on. You may also want to take a look at this page in the documentation for a more detailed explanation: [URL][http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html[/URL]](http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html%5B/URL%5D) Hope this helps! ---
**Author:** ![floodster](https://avatars.discourse-cdn.com/v4/letter/f/67e7ee/32.png) [@floodster](https://forums.percona.com/u/floodster)\ **Post date:** [March 19, 2009, 4:10am UTC](https://forums.percona.com/t/index-help-pleaseeee/1120/4 "2009-03-19T04:10:32Z")
dsuehrin thanks for the info. Where my query GROUPS BY is on a column from a joined table. I do have an index on the field LKPC\_District & the EXPLAIN shows me that it is using this but on my original table “EDPG\_PupilGrouped” it seems to be doing a full table scan everytime?? John.