3

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
2
  • What type is the last_entry and card_no? Varchar? Apr 30 '15 at 1:22
  • last_entry is time and card_no is varchar(50)
    – farness
    Apr 30 '15 at 8:29
3

Assuming that card_no and log_entry have VARCHAR or CHAR type, I would first add an index on (card_no, date, last_entry):

ALTER TABLE entry_log
  ADD INDEX card_no__date__last_entry__ix
    (card_no, date, last_entry) ;

and then use this query:

SELECT CONCAT(date, ' ', last_entry) AS LAST_LOG 
FROM entry_log 
WHERE card_no = LPAD('2948', 32, '0')
ORDER BY date DESC, last_entry DESC
LIMIT 1 ;

The values in the IN list should include all lengths up to the maximum allowed by the type. The example above is for VARCHAR(8).

Update: since it was clarified that the card_no column is always 32 characters and left padded with zeroes, the query condition is simplified from:
WHERE card_no IN ('2948', '02948', '002948', ..., '000...0002948') to
WHERE card_no = RIGHT(CONCAT('000..00', '2948'), 32) or simply to
WHERE card_no = LPAD('2948', 32, '0')

9
  • I always need to check max last entry for one card
    – farness
    Apr 30 '15 at 8:21
  • So, what do the values look like? Is it '000....002948' - always 50 characters stored? Or could it be less? Apr 30 '15 at 9:08
  • Yes always 32 characters like '000....001234' and '00000...000003'
    – farness
    Apr 30 '15 at 12:09
  • using Ricks advice if i use SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE card_no = RIGHT(CONCAT('00000000000000000000000000000000', '2475'), 32) Then it seems using card index... should i go with that?
    – farness
    Apr 30 '15 at 12:22
  • Did you try my answer (adding the index and using my query)? Apr 30 '15 at 12:23
3

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.

4
  • I suppose you wanted to write RIGHT(..., 8) in the last line. Apr 30 '15 at 1:32
  • Oops -- yes....
    – Rick James
    Apr 30 '15 at 1:53
  • actually card_no is a 32 byte character and it could be 1 to 10000 so what should be RIGHT(CONCAT('?', '*'),?) value?
    – farness
    Apr 30 '15 at 8:23
  • Please see the update
    – farness
    Apr 30 '15 at 8:38

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.