MySQL Forums
Forum List  »  Partitioning

Re: timestamp, datetime as default value as current_timestamp [PARTITION]
Posted by: Mannoj Kumar
Date: September 03, 2012 11:26PM

Here are the below requisites that you have asked for.

mysql> show keys from contentq_par;
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| contentq_par | 0 | PRIMARY | 1 | idcontent | A | 73270447 | NULL | NULL | | BTREE | | |
| contentq_par | 0 | PRIMARY | 2 | recorddate | A | 73270447 | NULL | NULL | | BTREE | | |
| contentq_par | 1 | PriorityIndex | 1 | status | A | 67 | NULL | NULL | YES | BTREE | | |
| contentq_par | 1 | PriorityIndex | 2 | priority | A | 87123 | NULL | NULL | YES | BTREE | | |
| contentq_par | 1 | PriorityIndex | 3 | keyvendor | A | 87123 | NULL | NULL | YES | BTREE | | |
| contentq_par | 1 | NewIndex1 | 1 | keyvendor | A | 67 | NULL | NULL | YES | BTREE | | |
| contentq_par | 1 | NewIndex1 | 2 | status | A | 67 | NULL | NULL | YES | BTREE | | |
| contentq_par | 1 | idx_contentq_msisdn | 1 | msisdn | A | 36635223 | NULL | NULL | YES | BTREE | | |
+--------------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (1.43 sec)

mysql> show table status;
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------
| contentq_par | InnoDB | 10 | Compact | 73703385 | 336 | 24767479808 | 0 | 15338913792 | 39845888 | 72063009 | NULL | NULL | NULL | latin1_swedish_ci | NULL | partitioned | |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------


mysql> select * from contentq_par limit 12;
+-----------+----------------------------------+--------------+------+------------------------+------+----------+---------------------+--------------+---------------+--------+
| idcontent | keyvendor | msisdn | cli | message | udh | priority | recorddate | callback_url | transactionid | status |
+-----------+----------------------------------+--------------+------+------------------------+------+----------+---------------------+--------------+---------------
| 1 | 7d1ca848aae060214342eae3356ae | 9733333213 | 9388 | No service available | NULL | 40280 | 2012-01-25 09:49:58 | NULL | NULL | 1 |
| 2 | 7d1ca83443436084963fda2eae3356ae | 97155856428 | 9388 | No service available | NULL | 45331 | 2012-01-25 09:49:58 | NULL | NULL | 1 |
| 3 | 7d1ca848aae06084943433356ae | 971576329059 | 9518 | Message from 3rd party | NULL | 40194 | 2012-01-25 09:55:58 | NULL | NULL | 1 |


Ofcourse I will be revising on TEXT datatypes with application team whether to keep them or not or change to varchar if required. And I should be worried for data storage only and not for tables performance reg text as they are not rendered by any selects or search keyword type. On a contrary If I set varchar (250) on charset latin1 when you add a value say 'abc' characters does it means that the data allocated for it is 251bytes or 4bytes? And which ever the answer would it be the same for other datatypes?

<Below is the select on contentq_par against vendor and constants works fine for now before partitioning.>

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

<Updates often to contentq_par>

UPDATE contentq_par SET status=1 WHERE idcontent=" . $row ['idcontent'] . " AND status=0 LIMIT 1

<Regular inserts to contentq_par happens as usual row by row 8TPS>

CREATE TABLE `vendor` (
`idrow` int(10) unsigned NOT NULL auto_increment,
`keyvendor` varchar(32) NOT NULL,
`hourperiod` int(10) NOT NULL,
`mtallowed` int(10) default '0',
`mtused` int(10) default '0',
`recordtime` timestamp NULL default CURRENT_TIMESTAMP,
`status` varchar(16) default 'active',
PRIMARY KEY (`idrow`),
KEY `NewIndex1` (`keyvendor`,`hourperiod`,`recordtime`),
KEY `NewIndex2` (`keyvendor`,`hourperiod`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1



mysql> show keys from vendor;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| vendor | 0 | PRIMARY | 1 | idrow | A | 32402 | NULL | NULL | | BTREE | |
| vendor | 1 | NewIndex1 | 1 | keyvendor | A | 5 | NULL | NULL | | BTREE | |
| vendor | 1 | NewIndex1 | 2 | hourperiod | A | 420 | NULL | NULL | | BTREE | |
| vendor | 1 | NewIndex1 | 3 | recordtime | A | 32402 | NULL | NULL | YES | BTREE | |
| vendor | 1 | NewIndex2 | 1 | keyvendor | A | 5 | NULL | NULL | | BTREE | |
| vendor | 1 | NewIndex2 | 2 | hourperiod | A | 405 | NULL | NULL | | BTREE | |
| vendor | 1 | NewIndex2 | 3 | status | A | 810 | NULL | NULL | YES | BTREE | |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)



Edited 1 time(s). Last edit at 09/05/2012 12:41AM by Mannoj Kumar.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: timestamp, datetime as default value as current_timestamp [PARTITION]
1382
September 03, 2012 11:26PM


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.