Basics MySQL Questions & Answer
- BLOB is used for storing binary data while Text is used to store large string.
- BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values while TEXT values are treated as nonbinary strings (character strings). They have a character set, and values are sorted and compared based on the collation of the character set.
- BLOB data can take a long time to retrieve, relative to how quick data from a TEXT field can be retrieved. So, use only what you need.
Query caching is one of the prominent features in MySQL and a vital part of query optimization. It is important to know how it works as it has the potential to cause significant performance improvements - or a slowdown - of your workload. The MySQL query cache is a global one shared among the sessions.
Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.
A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB –
They all differ only in the maximum length of the values they can hold.
A TEXT is a case-insensitive BLOB. The four TEXT types
They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.
The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.
mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.
mysql_fetch_object() – Returns a result row as object from database.
Non-Standard string types:
- CONCAT(A, B) – Concatenates two string values to create a single string output. Often used to combine two or more fields into one single field.
- FORMAT(X, D) – Formats the number X to D significant digits.
- CURRDATE(), CURRTIME() – Returns the current date or time.
- NOW() – Returns the current date and time as one value.
- MONTH(), DAY(), YEAR(), WEEK(), WEEKDAY() – Extracts the given data from a date value.
- HOUR(), MINUTE(), SECOND() – Extracts the given data from a time value.
- DATEDIFF(A, B) – Determines the difference between two dates and it is commonly used to calculate age
- SUBTIMES(A, B) – Determines the difference between two times.
- FROMDAYS(INT) – Converts an integer number of days into a date value.
MySQL Query for getting size of database in a schema
SELECT table_schema `your_database_name`, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
GROUP BY table_schema;
MySQL Query for getting list of tables in a database with size.
table_name 'Table Name',
data_length + index_length 'Size in Bytes',
ROUND(((data_length + index_length) / 1024 / 1024), 2) 'Size in MiB'
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;