Skip navigation links

MySQL Forums :: General :: Time Manipulation In Query


Advanced Search

Re: Time Manipulation In Query
Posted by: Rick James ()
Date: October 30, 2011 12:28PM

My directions were assuming that you are using the "mysql commandline". Here is an example:

mysql> SHOW TABLE STATUS LIKE 'ts' \G
*************************** 1. row ***************************
           Name: ts
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 29
 Avg_row_length: 564
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2009-03-19 19:00:09
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.30 sec)

It is not obvious why your attempt at SHOW TABLE STATUS failed.

> innodb_buffer_pool_size 8388608
That is really low for InnoDB. However, you have a tiny machine (only 1GB). Still, it might be good to raise that to 100M.

Some tips on shrinking the size of the table, which will improve performance:

You seem to know about SMALLINT (2 bytes) /MEDIUMINT (3 bytes), yet did not apply it here?
> `driverID` int(5) NOT NULL,
INT always takes 4 bytes; the "(5)" is irrelevant.

Latitude/Longitude --
DOUBLE (8 bytes) -- precise enough to distinguish one side of a hair from the other
FLOAT (4 bytes) -- precise to 6 feet / 2 meters

More details:
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

I see 3 other tables. Please provide the SHOW CREATE TABLE for them. And try again to get the SHOW TABLE STATUS for all for tables. If you are running from one of the UIs, try providing the command inside the 'enter SQL' window.

Options: ReplyQuote


Subject Written By Posted
Time Manipulation In Query newbie Shai 10/23/2011 03:13PM
Re: Time Manipulation In Query Rick James 10/24/2011 10:26AM
Re: Time Manipulation In Query newbie Shai 10/25/2011 09:22AM
Re: Time Manipulation In Query Rick James 10/25/2011 05:26PM
Re: Time Manipulation In Query newbie Shai 10/25/2011 11:26PM
Re: Time Manipulation In Query Rick James 10/26/2011 09:31PM
Re: Time Manipulation In Query newbie Shai 10/26/2011 08:24PM
Re: Time Manipulation In Query newbie Shai 10/28/2011 11:27PM
Re: Time Manipulation In Query Rick James 10/30/2011 12:28PM
Re: Time Manipulation In Query newbie Shai 11/01/2011 07:35PM
Re: Time Manipulation In Query Rick James 11/21/2011 11:50PM
Re: Time Manipulation In Query newbie Shai 11/23/2011 11:27AM
Re: Time Manipulation In Query Rick James 11/24/2011 11:39AM
Re: Time Manipulation In Query newbie Shai 11/24/2011 12:11PM
Re: Time Manipulation In Query Rick James 11/25/2011 12:24PM
Re: Time Manipulation In Query newbie Shai 11/26/2011 01:54AM
Re: Time Manipulation In Query Rick James 11/26/2011 10:44PM
Re: Time Manipulation In Query newbie Shai 11/27/2011 04:15AM
Re: Time Manipulation In Query Rick James 11/28/2011 11:09AM
Re: Time Manipulation In Query newbie Shai 11/28/2011 07:52PM
Re: Time Manipulation In Query Rick James 11/29/2011 09:02PM
Re: Time Manipulation In Query newbie Shai 11/30/2011 07:29AM
Re: Time Manipulation In Query newbie Shai 11/20/2011 11:46PM


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.