Re: timestamp, datetime as default value as current_timestamp [PARTITION]
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.