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,
       cli,message,c.udh,c.transactionid,
       c.callback_url
    FROM  contentq_par c
    JOIN  vendor v ON c.keyvendor= v.keyvendor
    WHERE  c.STATUS=0
      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


Subject
Views
Written By
Posted
Re: timestamp, datetime as default value as current_timestamp [PARTITION]
1627
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.