MySQL Forums
Forum List  »  Newbie

Re: Need help on indexing
Posted by: Rick James
Date: April 25, 2010 08:32AM

Have you ever used a dictionary? Or some sort of directory? Without an "index", suppose you are looking for a specific word or name. You would have to start on page 1, look at every entry on each page. This would be quite tedious if the word/name started with Z. Well, fortunately, dictionaries and directories are sorted. You can jump around and pretty quickly find the right page, then the right item within the page. That is an example of where the data is in sorted order. (This happens for InnoDB's PRIMARY KEY. MyISAM is different, but let's not get into details.)

Now, let's supposed you want to find all the Jakes in the directory. Now the order of the directory is useless (assuming it was ordered by _last_ name). You are back to scanning the entire list. So, let's add an Index on firstname. This will be a separate list of firstname and lastname, sorted by firstname. That way, it is pretty easy to find all the Jakes, or even all the Jake Laus.

Yes, a table with 2M rows that does not have an Index is going to be terribly slow.

CREATE TABLE rel
   user VARCHAR(40) NOT NULL,
   fruit VARCHAR(22) NOT NULL,
   PRIMARY KEY(user, fruit),
   INDEX(fruit, user)
);
Finding all the fruits for a given user will be very efficient because of the PRIMARY KEY. Finding all the eaters of a given fruit is very efficient because of the other index.

A PRIMARY KEY is an INDEX. It also constrains UNIQUEness, which seems 'right' for your application. And, since tables, "should" have a PRIMARY KEY, this is a reasonable candidate. (If you get to a billion rows, we can discuss the merits of using INTs instead of VARCHARs.)

Just as in the firstname, lastname example, (user, fruit) and (fruit, user) are _not_ redundant, and each is useful. Also, INDEX(user) plus INDEX(fruit) would not be as useful.

Similarly your user table should have an index (recommend PRIMARY KEY) in the user. And the fruit table, on the name of the fruit. (Is there anything else in the fruit table?)

With the new index, the query may run 20K times as fast because it will need to look only at the 100 rows for the given user, not all 2M rows. (It probably won't be that much faster, but the speedup will be significant.)

Options: ReplyQuote


Subject
Written By
Posted
April 24, 2010 03:27AM
Re: Need help on indexing
April 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.