0

I have asked questions about what is best approach to create a search system without libraries such as lucene, sphinx, solr, etc.

People keep on telling me things like you can create an index, that I can emulate indexing inside mysql using dedicated table for index, that searching through many tables is not a good idea. much better to have an index. index can be a single structured database table or external engine like sphinx or lucene which I won't use.

Please explain me what actually does indexing mean? I just know that I should assign PRIMARY KEY for ID and make it auto increment, PRIMARY KEY will make it UNIQUE so ID numbers won't be same. Are these index-es that people telling me directly associated with INDEX next to PRIMARY KEY or what?

I could never find an explanation anywhere in internet, so I'd be very thankful if somebody helps me out.

Davit
  • 1,394
  • 5
  • 21
  • 47
  • Keywords for research: full-text search, stemming and morphology – Valera Leontyev Dec 14 '12 at 21:03
  • Is it okay if I make a search based on full-text search? Is it somehow maybe a little bit close to modern approach? thanks – Davit Dec 14 '12 at 21:05
  • You don't have much of a good hand to ask questions about that here on site lately, don't you? Haven't you taken the feedback from [What is modern way of creating a php based mysql search?](http://stackoverflow.com/questions/13885872/what-is-modern-way-of-creating-a-php-based-mysql-search) into account? And you ask a lot here, I suggest you start getting your questions answered in the MySQL manual that covers these basic topics. If you've got a specific question, ask that then. – hakre Dec 14 '12 at 21:07
  • [What is an index?](http://stackoverflow.com/q/2599735/367456); [What is a fulltext index and when should I use it? \[closed\]](http://stackoverflow.com/q/3645746/367456) – hakre Dec 14 '12 at 21:12
  • thanks. I understood fulltext indexes but now i'm thinking of making a final decision to make my website search using fulltext indexes or any other better way... but, thank you again, it helped me – Davit Dec 14 '12 at 21:19

1 Answers1

0

MySQL is really just a very complex and powerful string parsing engine at the end of the day. All of the data you send it is stored as a string-like value in a file. MySQL is built in C and that is what manages the processing to do all of the queries that you run.

An index is a list of the data in a table of yours mapped in a different manner than the mapping of a primary ID. The basic goal of an index is to allow the MySQL engine you are using to search your table much more quickly. This is because in practice an index is able to rule out huge sections of your table where it is known the records your looking for do not exist. A Primary Key is an index of the id value, but you can create indexes on arbitrary columns as followed:

CREATE INDEX user_id_index ON meta (user_id);

A table can have multiple indexes on it for different things and that is okay. Often you will find that you are referencing a table by different values and maybe you will want to create an index for each of those. It is important to index wisely as it is possible that indexing the wrong columns will slow your queries down. You will have to read this entire link to gain a good understanding of what is going on, but remember an index is basically a file that allows lookups via a column in one of your tables to happen more quickly:

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

usumoio
  • 3,500
  • 6
  • 31
  • 57