table index issue

Forum Moderators: open

Message Too Old, No Replies

table index issue

sam222

10:57 am on Aug 9, 2011 (gmt 0)

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

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
GROUP BY field
ORDER BY fc_od, f_od

rocknbil

4:32 pm on Aug 9, 2011 (gmt 0)

Did you try removing the group by? Doesn't seem necessary on an un-joined select. Maybe there are seven rows that match?

sam222

8:09 pm on Aug 9, 2011 (gmt 0)

Thanks for the reply.

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

rocknbil

4:48 pm on Aug 10, 2011 (gmt 0)

What is the data type of s1,2,3,4? If it's an integer data type (which it should be) I see no other explanation other than there's 24 rows of matching data. (?)