MySQL Forums
Forum List  »  Newbie

Re: optimising a create table as select
Posted by: Rick James
Date: May 15, 2014 11:35PM

As long as you are rebuilding the table, you should improve the datatypes...

INT is always 4 bytes;
MEDIUMINT is 3 bytes;
SMALLINT is 2 bytes;
TINYINT is 1 byte;
FLOAT is 4 bytes;

time` int(11) DEFAULT NULL, --> see the TIME datatype (assuming it really is a time not a datetime); 3 bytes.

`rush_hour_am` int(11) DEFAULT '0',
`status` int(11) DEFAULT '0', --> TINYINT UNSIGNED?

In general, make int-like fields UNSIGNED.

`deceleration_1/2/3` int(11) DEFAULT '0', -- perhaps it should be in another table? ("arrays" are not implemented.)


`latitude` decimal(15,10) DEFAULT NULL, -- gross overkill; takes about 8 bytes; more discussion here:

> I agree >> Joining FROM t1 > t2 WHERE = t2.trip_id is going to be the killer just to get a date <<
It would be much less of a killer if you used a compound index something like
(trip_id, date)
`created_at` datetime NOT NULL, -- DATETIME is 8 bytes; TIMESTAMP (same resolution) is 4 bytes.

`highest_speed` float DEFAULT '0', --> SMALLINT UNSIGNED?

Please show us the query with the real table/column names; then we can reassess the situation.

Options: ReplyQuote

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.