1

I have the following table to store log messages:

CREATE TABLE `log` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `timestamp` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `logger` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `level` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 `message` varchar(4000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `thread` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
 `file` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `line` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `machine` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10321 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

There will be lots of SELECTs with a WHERE machine = 'xyz'

Would making this column an INDEX improve performance on that?

6
  • 1
    Yes, it would...
    – Philᵀᴹ
    Jan 7 '16 at 10:26
  • k thx, if that is so easy, then why not make every column an INDEX?
    – oleole
    Jan 7 '16 at 10:33
  • @oleole That's a different question. But will you also have a lot of Selects with where column = 'xyz-something', for every column of the table? Jan 7 '16 at 10:36
  • 2
    @oleole then you can create an index over those two columns (combined). In short - indexes make those selects faster but they make all inserts and updates a bit slower and take some memory and disk space, so having index which is not used is just a waste. And with multicolumn indexes the actual possible combinations are too many, so you only create those you need.
    – jkavalik
    Jan 7 '16 at 12:07
  • 1
    You probably have WHERE machine = 'xyz' ORDER BY timestamp? If so, then this would be even better: INDEX(machine, timestamp). And, if you have a WHERE machine = 'xyz', but not that ORDER BY, the index is still useful. More on indexing.
    – Rick James
    Jan 7 '16 at 23:26
0

Answer is yes, indexing varchar data field increases the performance but it depends on the query you performed against that particular table. Lets consider the following two entities,

Card

Card Id (PK)  
Card Type  
Card Name

Registration

Reg Id (PK)   
Card Id (FK)   
Number of Registration

Lets consider a query, what are the total number of registration by card type?

Here, the card type is not frequently updated, it is either Credit or Debit Card. Therefore index on the card type data field is a good choice. Card type contains the values Credit card and Debit Card only.

If your non-key data field have such data and you need to perform the query in such a way mentioned above, you can create a non-unique key index for performance.

I hope this answer will help you. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.