homepage Welcome to WebmasterWorld Guest from 54.198.130.203
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
table index issue
sam222




msg:4349424
 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




msg:4349558
 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




msg:4349658
 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




msg:4349991
 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. (?)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved