SQL is the de-facto language for communicating with [[Database]] servers. It's a way of describing operations on regularly structured data (usually, in Database Tables). ## Building a database Normalization probably the best method for making a re-usable, resilient database structure. ## Operations ### Counting When you count, you can use `COUNT(*)` as the fastest way to count in [[MySQL]]. MySQL uses the smallest, non-null, secondary index. It does not like to count or iterate over the primary index, as that's the cluster / disk order, so it has to scan linearly. Secondary Keys are small on disk since you're only storing data for that index. `COUNT(*)` lets the engine pick. Further investigation required for behavior in [[Postgres]] ## Learned Lessons 1. Always limit queries. Unless the point of the query is to get everything, always limit queries to a reasonable number beyond the maximum expected. Fetching blog posts? Only grab the latest 10. Listing out downloads in a table? Grab 100, they'll never hit that... right? > [!tip] Any limit will be hit eventually. It creates a conversation with the client about usage and codifies requirements ## Column Types ### Text (Char) A static string up to 65535 bytes. Trailing spaces > [!warning] This is allocated all at once and generally not useful for variable datasets (see [[#Char and VarChar]]) ### CHAR and VARCHAR CHAR and VARCHAR are [[Database#Row Embedded|Row Embedded]] string column types on default sizes. They can store up to 65535 bytes, minus other data in the column. > [!warning] CHAR undergoes trimming of whitespace on insert, while VARCHAR does not. If a medium or long variant of this type is used, they are stored in their own datapage and require an [[Pointer#Indirection|indirection]] to access. --- # References https://www.youtube.com/watch?v=H6juZ8c_Nu8 - Counting in MySQL https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html#data-types-storage-reqs-strings