Announcement Module
No announcement yet.

INDEX Help pleaseeee

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • INDEX Help pleaseeee

    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:

      Hope this helps!


      • #4

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



        • #5
          Table scans aren't necessarily bad. Your query is asking to do an operation on every row in EDPG_PupilGrouped with no exceptions. In general, the most efficient way to examine every row is to do a table scan. You could do something awful like try to create a covering index (one that includes all the columns you're selecting out of EDPG_PupilGrouped), but that would only change the plan to use an index scan, which might only be marginally better, depending on the number of columns in your table.