Would it help to add index to BIGINT column in MySQL?

by

If you have a very large table, then searching against values that aren’t indexed can be extremely slow. In MySQL terms this kind of query ends up being a “table scan” which is a way of saying it must test against each row in the table sequentially. This is obviously not the best way to do it.

Adding an index will help with read speeds, but the price you pay is slightly slower write speeds. There’s always a trade-off when making an optimization, but in your case the reduction in read time would be immense while the increase in write time would be marginal.

Keep in mind that adding an index to a large table can take a considerable amount of time so do test this against production data before applying it to your production system. The table will likely be locked for the duration of the ALTER TABLE statement.

As always, use EXPLAIN on your queries to determine their execution strategy. In your case it’d be something like:

EXPLAIN SELECT * FROM table1 WHERE my_big_number=19287319283784

More Related Contents:

  1. DOs and DONTs for Indexes [closed]
  2. MySQL performance: multiple tables vs. index on single table and partitions
  3. Higher cardinality column first in an index when involving a range?
  4. Does Order of Fields of Multi-Column Index in MySQL Matter
  5. MySQL load data infile – acceleration?
  6. Mysql performance on 6 million row table
  7. With MySQL, how can I generate a column containing the record index in a table?
  8. MYSQL OR vs IN performance
  9. How to Perform an UPSERT so that I can use both new and old values in update part
  10. JDBC batch insert performance
  11. SQL Performance UNION vs OR
  12. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  13. MySQL vs MongoDB 1000 reads
  14. What is cardinality in MySQL?
  15. MySQL varchar index length
  16. What’s the difference between using INDEX vs KEY in MySQL?
  17. What is better in MYSQL count(*) or count(1)?
  18. Mysql Improve Search Performance with wildcards (%%)
  19. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  20. How to disable index in innodb
  21. MySQL: Inner join vs Where [duplicate]
  22. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  23. MySQL: Many tables or many databases?
  24. MySQL 5.0 indexes – Unique vs Non Unique
  25. sql group by versus distinct
  26. MySQL distinction between e and é (e acute) – UNIQUE index
  27. Two single-column indexes vs one two-column index in MySQL?
  28. MySQL – why not index every field?
  29. Disadvantages of quoting integers in a Mysql query?
  30. Optimizing MySQL for ALTER TABLE of InnoDB

Leave a Comment