MySQL Forums
Forum List  »  Partitioning

Re: timestamp, datetime as default value as current_timestamp [PARTITION]
Posted by: Rick James
Date: September 04, 2012 08:05PM

SELECT  c.idcontent,c.msisdn,c.keyvendor,
    FROM  contentq_par c
    JOIN  vendor v ON c.keyvendor= v.keyvendor
      AND  mtallowed > mtused
      AND  v.STATUS='active'
      AND  hourperiod= HOUR(CURTIME())
      AND  DATE(recordtime)=CURDATE()
      AND  c.keyvendor='d6bb95d3b4343rwws21a2c47d5f'
    LIMIT  1
Please qualify each field with a table alias.

> AND DATE(v.recordtime)=CURDATE()
Cannot use an index on record time because it is hidden in a function.
AND v.recordtime >= CURDATE()
AND v.recordtime < CURDATE() + INTERVAL 1 DAY
would have the same effect, and be able to use
KEY `NewIndex1` (`keyvendor`,`hourperiod`,`recordtime`)

If hourperiod and recordtime are supposed to represent parts of the same time, it is much better to have a single DATETIME or TIMESTAMP.

You could save 16 bytes (per row) by changing keyvendor to BINARY(16) and storing UNHEX(...) into it. You would need to do it in all tables with that column.

'abc' in VARCHAR(250) takes 4-5 bytes, depending on whether there is 1- or 2-byte overhead.

> `status` varchar(16) default 'active',
Consider an ENUM. (1 byte)

> `msisdn` varchar(16) DEFAULT NULL,
Maybe DECIMAL(12,0)? (6 bytes, I think)

Options: ReplyQuote

Written By
Re: timestamp, datetime as default value as current_timestamp [PARTITION]
September 04, 2012 08:05PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.