After you create a table, you can create an index on one or more columns in the table to speed up the execution of SQL statements on the table. Well-designed indexes can reduce physical or logical I/O operations.
Tables in OceanBase Database are clustered index tables. If you do not specify a primary key for a table, the system automatically generates a hidden primary key for the table.
The advantages, disadvantages, and considerations of indexes are as follows:
Advantages:
With indexes, the data query speed is increased.
With indexes, optimization hiders can be used during queries to improve the system performance.
Disadvantages:
Index creation and maintenance are time-consuming, and the amount of time consumed increases as the data volume grows.
When you add, delete, or modify data in tables, you must also dynamically maintain indexes, which slows down data maintenance.
Considerations:
Create indexes on fields that are frequently used for queries, but do not create too many indexes on tables that are frequently updated.
Do not use indexes for tables with a small data volume. This is because the time consumed in querying all data may be shorter than that in traversing the indexes. In this case, indexes may not be useful.
Do not create indexes when the modification performance is far greater than the query performance.