Member Avatar for janjini.10

Is it required to use index when the MySQL table has only 100 rows? Justify your answer.

  • 3 Contributors
  • 2 Replies
  • 229 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by cereal

Dani AI

Generated

Short answer: No — not required for a 100‑row MySQL table in most cases. A full table scan over a few hundred rows is extremely cheap, so an index usually won’t improve performance enough to justify the extra complexity and write overhead unless the queries or workload make it necessary.

Context and when an index still helps (ties to existing replies)

  • is right that indexes speed queries when columns are used frequently in filters/joins.
  • ’s point about the optimizer deciding whether to use an index is important: indexing alone doesn’t force better plans — testing does. For very small tables, the optimizer will often prefer a sequential scan unless a query is highly selective or the table is joined many times.

Practical checklist

  • Add an index when a column is repeatedly used in WHERE, JOIN, ORDER BY or when a query returns a tiny fraction of rows.
  • Avoid indexes on low‑cardinality fields (boolean/status) unless combined in a composite index with selective columns.
  • If using InnoDB, define a sensible primary key (InnoDB stores rows clustered by PK, which affects lookup cost).
  • Measure: compare query latency before/after adding the index, and verify the execution plan (use the optimizer’s plan tool) rather than assuming.

Quick commands (examples)

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);
SHOW INDEX FROM users;
DROP INDEX idx_users_email ON users;
ANALYZE TABLE users;

Final recommendation
For ’s case: don’t add indexes just because of habit — only add them if query patterns or expected growth justify it. If the table may grow or is read‑heavy with selective filters or frequent joins, add the appropriate index and re‑measure. Otherwise, keep it simple.

Recommended Answers

All 2 Replies

Member Avatar for RomelynCastillo

Hi,Index is used to speed up query.If particular column is used often in queries and table has lot of data.Then it is recommended to create index for that column of the table. It will speed up the query.

Member Avatar for cereal

In addition, check the documentation: http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

Especially:

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

Anyway it's the query optimizer that will decide if the query will use the available indexes or not, you can force it to use a specific index or to ignore them all, read these links for more information:

So, at end, test your queries, use the explain select ... and see when you get better results.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.