Perfomance tuning - best column types for datetime and IPs

2 posts / 0 new
Last post
mikikg
mikikg's picture
Offline
Last seen: 1 month 3 days ago
Joined: Jul 24 2014
Junior Boarder

Posts: 3

Aleksandar Markovic
Perfomance tuning - best column types for datetime and IPs

Hi, I'm evaluating InfiniDB for my analytical system which is based on imported Apache logs.

 

As I have parsed Apache logs, among other fields I have datetime (access time) and host (IP) fields in table.

 

What is the best choice for column types for those two fields?

 

Does changing field type from "datetime" to integer will make some improvements regarding performance?

Similar, does changing from varchar to integer for IP address will gain some improvements?

 

My current table definition is like this:

 

                    CREATE TABLE IF NOT EXISTS my_log_table (
                      `log_id` int(11) DEFAULT NULL,  ##related to processed log file, not PK
                      `host` varchar(50) NOT NULL,
                      `remote_logname` varchar(50) DEFAULT NULL,
                      `remote_user` varchar(50) DEFAULT NULL,
                      `request_time` datetime NOT NULL,
                      `request_method` varchar(50) NOT NULL,
                      `request_uri` varchar(3000) NOT NULL,
                      `http_status` int(11) NOT NULL,
                      `referer` varchar(3000) DEFAULT NULL,
                      `user_agent` varchar(500) DEFAULT NULL,
                      `virtual_host` varchar(100) NOT NULL,
                      `request_bytes_in` int(11) NOT NULL,
                      `content_bytes_out` int(11) NOT NULL,
                      `header_bytes_out` int(11) NOT NULL,
                      `country_name` varchar(75) DEFAULT NULL,
                      `country_iso` varchar(2) DEFAULT NULL,
                      `region_name` varchar(75) DEFAULT NULL,
                      `region_iso` varchar(3) DEFAULT NULL,
                      `city_name` varchar(75) DEFAULT NULL
                    ) ENGINE=InfiniDB DEFAULT CHARSET=utf8;

 

Is there any additional advice regarding this structure which can influence performance?

 

Thx in advance.

radams
radams's picture
Offline
Last seen: 7 hours 50 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Perfomance tuning - best column types for datetime and IPs

 

 

InfiniDB uses a token-dictionary strategy to manage strings longer than 8-bytes fixed length, and 7 bytes variable length.
Performance would be best when using non-dictioanry column types (datetime,INT,etc....)
 
 
Thanks,
 
Robert