Need help in table index

I have a table having around 2 million records i have tried to put proper index but still returning 7 rows when i explain query and an expert advice will be highly appreciated, here is the explain query result:

id select_type table type possible_keys key key_len ref rows Extra:
1 SIMPLE table ref p_id,KF,All_Fields All_Fields 24 const,const,const,const,const,const 7 Using where; Using temporary; Using filesort

explain
SELECT *
FROM table
WHERE cat =16
AND s1 =112
AND s2 =0
AND s3 =0
AND s4 =0
AND p_id =102
GROUP BY field
ORDER BY fc_od, f_od

There is a composite index on cat, s1, s2, s3, s4, p_id named as All_Fields

what is the purpose of the GROUP BY clause?

Group by is used to bring distict records.

that doesn’t make sense, because you’re using the dreaded, evil “select star”

therefore the columns returned include the primary key, and therefore all the rows are distinct

your table does have a primary key, doesn’t it?

This table is the child of Field_table storing the records against field from Field_table and I need all fields of this table that’s why using select star here and because I need to get the distinct records by field column and couldn’t find any other way instead to use GROUP BY clause.
Yes there is no primary key.

sorry, yes there is a primary key ‘Id’ just for auto increment.

Right so with the SELECT * you are selecting the ID field along with the others. Thus every row is DISTINCT.

GROUP BY clause compresses all of the same type of rows into a group row. So if you have a table of cars and a column for colour, a GROUP BY colour would group all the red cars into one row, all the green into another so you could count them.

A GROUP BY clause isn’t used to get DISTINCT rows.

And you can’t merely put a DISTINCT clause on your query instead because you have included your primary key column which means all rows are distinct.

You have also not laid out what criteria will keep one row and dispose of another row that is similar. For instance you may want the highest building of each type and discard all others in each group.

my sincere advice: rethink your concept of “distinct records”

Great help much appreciated!

I have restructured and removed GROUP BY clause and now my query is:

explain
SELECT *
FROM table
WHERE cat =16
AND s1 =112
AND s2 =0
AND s3 =0
AND s4 =0
AND p_id =102
ORDER BY fc_od, f_od

and this time got 24 rows and here are the results:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table ref p_id,KF,All_Fields All_Fields 24 const,const,const,const,const,const 24 Using where; Using filesort

Any help to optimize query to get rows=1 against explain?

huh? what does this mean?

I mean I have removed GROUP BY now and if I do explain query it is still not coming up with required results::

explain
SELECT *
FROM table
WHERE cat =16
AND s1 =112
AND s2 =0
AND s3 =0
AND s4 =0
AND p_id =102
ORDER BY fc_od, f_od

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table ref p_id,KF,All_Fields All_Fields 24 const,const,const,const,const,const 24 Using where; Using filesort

Rows are 24 and not 1.

it looks to me like your table contains 24 rows that meet those criteria in your WHERE clause

No according to my where clause criteria table returns 29 rows.

umm…

i think your EXPLAIN is fine