MySQL Forums
Forum List  »  Newbie

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


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

Written By
Index not working as I expect
June 01, 2011 12:44PM

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.