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