Mysql Index for concat select

For Below Query

SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG
    FROM entry_log
    WHERE TRIM(LEADING 0 FROM card_no)='2948'

I've index on

date
card_no
date,last_entry
date,last_entry,card_no

My Explain Shows

id  select_type     table       type    possible_keys   key             key_len     ref     rows    Extra   
1   SIMPLE          entry_log   index   NULL            date_last_card  158         NULL    103766  Using where; Using index

My Explain Extended shows

id  select_type     table           type    possible_keys   key             key_len     ref     rows    filtered    Extra   
1     SIMPLE        entry_log       index   NULL            date_last_card  158         NULL    103766  100.00      Using where; Using index

I want to know which index can i remove/use, Am i in right path, How should i improve execution time for above query?

CREATE TABLE `entry_log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `card_no` varchar(50) NOT NULL,
 `date` date NOT NULL,
 `first_entry` time NOT NULL,
 `last_entry` time NOT NULL,
 `all_entry` text NOT NULL,
 `entry_time` datetime NOT NULL,
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9308 DEFAULT CHARSET=latin1

Cleanse your data before storing it. Otherwise, INDEXes may be useless.

I this particular case, the TRIM function is hiding card_no, making the INDEX on card_no useless.

This SELECT would run a lot faster because of the index:

SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG
    FROM entry_log
    WHERE card_no = '2948'

OK, you don't like the Cleanse advice. And your card_no is always an 8-digit number. In that case, this would allow the use of the index:

WHERE card_no = RIGHT(CONCAT('00000000', '2948'), 8)

Please provide SHOW CREATE TABLE so we won't have to guess any more.

Edit

If a card_no is just a number, but with some extra zeros in front, it could be stored more compactly as an INT, BIGINT, or DECIMAL(...). Then do the LPAD to prefix it with the needed zeros when fetching it.

SMALLINT (2 bytes) can handle 4 digits, as with your example.

INT (4 bytes) can handle 9 digits.

BIGINT (8 bytes) can handle 18 digits.

DECIMAL(32,0) (15 bytes) can handle 32 digits. A smaller number could be used if you never need a full 32.

You would need to pick whatever size will handle the largest card_no you can have.

VARCHAR(50) takes 2 + N bytes, where N is the number of digits. If card_no varies a lot is length, this might still be the best bet.

Edit2

If you have a lot of rows for a card_no, it will run a lot faster if you do these:

• Combine date (date) and last_entry (time) into a single column of DATETIME datatype. I'll call the column last_log.

• SELECT MAX(last_log) AS LAST_LOG

• INDEX(card, last_log)

Rationale: The INDEX can do essentially all the work of the MAX -- this will run a lot faster.

(Note: This comment about last_log is independent of the discussion on leading zeros for card_no. Be sure to formulate the WHERE clause as WHERE card_no = ... in order to make use of the index.)

Edit 3

In the newest versions of MySQL 8.0 and MariaDB 10, you can index "virtual" columns.

StackExchange Database Administrators Q#99202, answer score: 3