how to insert/update/delete on columns with index?

I had to in开发者_如何学JAVAdex some of my cells in multiple tables to improve speed.

Now how i should change my insert/update/delete etc queries to make sure indexing works?

What exactly i should do and what happens if i just don't do anything special?

Should i just automate re indexing of columns every hour ?

---

The MySQL RDBMS will handle the indexing for you. DELETEs, INSERTs, and UPDATEs will modify the index without any action from you. Re-indexing them should not ever become necessary.

---

Database will keep your indexes up to date.

You don't need to change your queries. RDBMS will determine when to use index.

To make sure RDBMS uses indexes in your SELECT/UPDATE queries use EXPLAIN (For UPDATE queries, just put your WHERE clause to SELECT for EXPLAIN)

---

MY test code: Differents 42 sec and 2 min .

mysql> UPDATE  big_table SET id = INSERT(id,1,5,'');
Query OK, 2835999 rows affected (42.69 sec)
Rows matched: 2835999  Changed: 2835999  Warnings: 0

mysql> ALTER TABLE big_table ADD INDEX id(id);
Query OK, 2835999 rows affected (5.75 sec)
Records: 2835999  Duplicates: 0  Warnings: 0

mysql> UPDATE  big_table SET id = INSERT(id,1,4,'');
Query OK, 2835999 rows affected, 2856 warnings (2 min 1.49 sec)
Rows matched: 2835999  Changed: 2835999  Warnings: 2856