MySQL Forums :: Newbie :: Index not working as I expect


Advanced Search

Index not working as I expect
Posted by: Olek Marianski ()
Date: June 01, 2011 12:44PM

Hi,

my problem is with indexing table. Let's say I have a table:

id(int PK AI),c1(varchar 100),c2(varchar 100),c3(varchar 100),c4(date),c5(text)

I put an index on c1,c2,c3,c4.

I query database: SELECT * FROM table WHERE c1='c1' AND c2='c2' AND c3='c3' ORDER BY c4 DESC LIMIT 0,30;

I expect it to use my index and with over 600k records give me results in less than seconds. However it uses index, when i 'explain' i get key_len = 990 and rows = 30.000.

Why mysql hava to analize like 30k rows if I put the index and I expect to get and analyze only 30 rows? Where am I wrong? I would be grateful for any suggestions.

Config if needed: based on my.medium.cnf, run on 512MB RAM, 1 GHz VPS.

Options: ReplyQuote


Subject Written By Posted
Index not working as I expect Olek Marianski 06/01/2011 12:44PM
Re: Index not working as I expect Olek Marianski 06/01/2011 02:25PM
Re: Index not working as I expect Rick James 06/02/2011 07:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.