Welcome to WebmasterWorld Guest from 54.196.153.46

Forum Moderators: open

Message Too Old, No Replies

table index issue

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

Junior Member

5+ Year Member

joined:July 28, 2011
posts: 88
votes: 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
4:32 pm on Aug 9, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Did you try removing the group by? Doesn't seem necessary on an un-joined select. Maybe there are seven rows that match?
8:09 pm on Aug 9, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:July 28, 2011
posts: 88
votes: 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
4:48 pm on Aug 10, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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. (?)
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members