Database Storage allocation/Choosing Right Column Types mysql

Here integer type along with minimum maximum value length and storage in byte.

Type	Storage	Minimum Value	Maximum Value
	(Bytes)	(Signed/Unsigned)	(Signed/Unsigned)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

In contrast to CHAR (0 to 65,535),  VARCHAR (length 0 to 65,535) values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	'    '	4 bytes	''	1 byte
'ab'	'ab  '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

varchar = 1 byte which is fix + number of characters( 0 to 255) byte
varch = 1+4 = 5 byte (abcd is 4 character+1 = 5 byte)
varchar = 2 byte which is fix + number of characters(256 to 65535) byte

For more text type :

TEXT	String length + 2 bytes	A string with a maximum length of 65,535 characters
MEDIUMTEXT	String length + 3 bytes	A string with a maximum length of 16,777,215 characters
LONGTEXT	String length + 4 bytes	A string with a maximum length of 4,294,967,295 characters

For other field/column type

FLOAT[Length, Decimals]	4 bytes	A small number with a floating decimal point
DOUBLE[Length, Decimals]	8 bytes	A large number with a floating decimal point
DECIMAL[Length, Decimals]	Length + 1 or 2 bytes	A DOUBLE stored as a string, allowing for a fixed decimal point
DATE	3 bytes	In the format of YYYY-MM-DD
DATETIME	8 bytes	In the format of YYYY-MM-DD HH:MM:SS
TIMESTAMP	4 bytes	In the format of YYYYMMDDHHMMSS; acceptable range starts in 1970 and ends in the year 2038
TIME	3 bytes	In the format of HH:MM:SS

Fix MySQL InnoDB tablespace exists (corrupt tablespace issue)

Mainly this tablespace error happen when file missing from mysql data folder.
You'll find an orphaned tablename.ibd file without it's normal companion tablename.frm file. If you move that .ibd file to a safe temporary location (just to be safe) that should fix the problem.

Example inside  mysql/data/database_name  folder you'll find this kind of list.

table1.frm
table1.idb
table2.frm
table2.ibd
table3.idb <- problem table, no table3.frm
table4.frm
table4.idb

Here is the solution steps:

1. backup your database first.
2. stop mysql engine service
3. remove the database directory manually from inside mysql/data/database_name.
4. start mysql engine
5. create new database with any name different from your corrupted database/ or same name as you want.
6. Import the backed up database now. 

After restoring your backup and your table will be working fine.

Relative path Vs Absolute path in javascript

The path with reference to root directory is called absolute. The path with reference to current directory is called relative.

Relative Path

 Relative Path <img src="kitten.png"/>   

Absolute Path

 Absolute in all respects: <img src="http://www.foo.com/images/kitten.png">

Example of absolute paths:

• D:\documents\something.doc
• E:\music\good_music.mp3

<script>

 var full_path = location.protocol + "//" + window.location.host
</script>

// output   www.esteemcorporation.com

For this page if you inspect the window.location object you will see hash: host: stackoverflow.com hostname: stackoverflow.com href: http://stackoverflow.com/questions/8401879/get-absolute-path-in-javascript pathname: /questions/8401879/get-absolute-path-in-javascript port: protocol: http: search:

MySQL LIMIT on DELETE statement

Suppose you want to delete some old data from a table but need to keep latest data to light weight your database. In that case there are some very simple way to do.

Here one of them. If there is id unique primary column with auto increment, in that table we can delete old id like if you want to delete first 10000 rows  with following command.

"DELETE FROM table_name WHERE id >=10000";

*The conditions in the optional WHERE clause identify which rows to delete. Without WHERE clause, all rows will be deleted.

If the DELETE statement includes an ORDER BY clause, rows are deleted in the order specified by the clause. This is useful primarily in conjunction with LIMIT. For example, the following statement finds rows matching the WHERE clause, sorts them by timestamp_column, and deletes the first (oldest) one:

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

But it's not possible to limit rang like SELECT statement LIMIT 1,500