0

Okay, mysql indexing. Is indexing nothing more than having a unique ID for each row that will be used in the WHERE clause?

When indexing a table does the process add any information to the table? For instance, another column or value somewhere.

Does indexing happen on the fly when retrieving values or are values placed into the table much like an insert or update function?

Any more information to clearly explain mysql indexing would be appreciated. And please dont just place a link to the mysql documentation, it is confusing and it is always better to get a personal response from a professional.

Lastly, why is indexing different from telling mysql to look for values between two values. For Example: WHERE create_time >= 'AweekAgo'

I'm asking because one of my tables is 220,000+ rows and it takes more than a minute to return values with a very simple mysql select statement and I'm hoping indexing will speed this up.

Thanks in advanced.

knittledan
  • 754
  • 2
  • 9
  • 23
  • 1
    Yes, indexing will make your query much much faster. Read the doc : http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Denys Séguret Jun 21 '12 at 17:44
  • Really... you could't provide you own thoughts about this. You think I would be writing here if I completely understood that doc. stackoverflow exists not to redirect people to something they could easily google, rather, for people to come and ask a question as if talking to a professional. Thanks for not reading my question. – knittledan Jun 21 '12 at 17:51

3 Answers3

2

You were down voted because you didn't make effort to read or search for what you are asking for. A simple search in google could have shown you the benefits and drawbacks of Database Index. Here is a related question on StackOverflow. I am sure there are numerous questions like that.

To simplify the jargons, it would be easier to locate books in a library if you arrange the in shelves numbered according to their area of specialization. You can easily tell somebody to go to a specific location and pick the book - that is what index does

Another example: imagine an alphabetically ordered admission list. If your name start with Z, you will just skip A to Y and get to Z - faster? If otherwise, you will have to search and search and may not even find it if you didn't look carefully

Community
  • 1
  • 1
codingbiz
  • 26,179
  • 8
  • 59
  • 96
  • Some of my questions are easily searchable on google but others not so much as just try it to find out so I came here to clarify. Thanks for a little more clarification – knittledan Jun 21 '12 at 18:00
1

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

You can create an index like this way :

CREATE INDEX index_name
ON table_name ( column1, column2,...);

You might be working on a more complex database, so it's good to remember a few simple rules.

  • Indexes slow down inserts and updates, so you want to use them carefully on columns that are FREQUENTLY updated.

  • Indexes speed up where clauses and order by.

For further detail, you can read :

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

http://www.tutorialspoint.com/mysql/mysql-indexes.htm

Nishu Tayal
  • 20,106
  • 8
  • 49
  • 101
0

There are a lot of indexing, for example a hash, a trie, a spatial index. It depends on the value. Most likely it's a hash and a binary search tree. Nothing really fancy because most likely the fancy thing is expensive.

Micromega
  • 12,486
  • 7
  • 35
  • 72