INDEX Help pleaseeee

MySQL & MariaDB Other MySQL® Questions
1

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:

2

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??

3

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]

Hope this helps!

4

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.