MySQL Forums :: Newbie :: Need help on indexing


Advanced Search

Need help on indexing
Posted by: jake lau ()
Date: April 24, 2010 03:27AM

Hi there, I'm kinda new to MySQL, but I do know the basics like select/update, inserting/deleting and the where condition etc.

I'm actually looking into indexing now, because right now I'm having my first go at a 20k user database. What i have closely resembles the following example:

20k user each may like to eat any number of 100 different kinds of fruits.

On my first try, what I did was I created a non-normalised database, where I store all the fruits a person likes to eat as an imploded array in a single field in the user_profile table, after which i realised that answering questions like "who likes to eat apples?" is awkward and hard to query.

I was advised to work with a normalised database, split up the fruits into another table, so now I have a second table called the user_fruit table. This table has 2 fields only, user_id and fruit_id.

Now if i have 20k users and 100 possible fruits to choose from, it is logically, though remotely, possible that I have 2 million records in the user_fruit table. I was told I need to explore into indexing.

Been reading up on materials on indexing but I must say I am quite confused.. I am using phpMyAdmin and it seems I can assign any field to be an index, but it defaults to BTREE type, I don't think it allows any other type.. i have some questions as follows:

0) This is the most basic question, what exactly is an index? I know this question is very generic, but its really the whole thing that I don't understand.. I know it makes database look ups fast, I know you can assign a field to be an index.. I'll try to break down into a couple of questions below.


1) Making an index is just simply defining a field to be an index? Do I have to do anything else?
2) For my field I can only define it to be a BTREE index, and from what I've read its binary searching? In that case do I have to sort that column to ascending/descending order first?
3) What if I need to search varchar fields?

To be honest I don't know where to start asking.. If anyone can explain ideally with an example and a query statement etc, I'd be truely grateful!

Greatly appreciate your time, and would like to thank you in advance!

Jake

Options: ReplyQuote


Subject Written By Posted
Need help on indexing jake lau 04/24/2010 03:27AM
Re: Need help on indexing Rick James 04/25/2010 08:32AM


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.