sql

Fetching the most recent entry from a log-table

Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this: log area (char) lognotice (char or text) logtime (timestamp when the event was logged). Fetching Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated - the Query to fetch the data could typically look like this:

Mysql: display row count for all tables in a database

When playing the role of the DBA, it’s often useful to get a quick listing of how many rows each table in a database contains. The syntax for this is pretty simple in Mysql: SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '***database name***'; Replace database name with the actual database name in the SQL above. Notice that when using innodb tables, it’s only a rough estimate.

Substring magic with mysql

Mysql is a wonderful database, and while many use it, most people only scratch the surface of what the database can do. One of the practical functions available is the substring_index function, and an imaginary mailing list example is a nice way to show how to use it. Let imagine we have a mailinglist in a table named “mailinglist” and it has a (char) column with the email addresses subscribed to the list.

Should you use sql specific statements?

It seems there are two camps when it comes to SQL and how to do database optimizations - the “generic camp” and “the specialist camp”. While I don’t consider myself an extremist, I am absolutely in the specialist camp and this little post is an explanation of why. SQL is a generic database langauge . There are a few different standards in use (the language has progressed over time), but the core of the SQL language is pretty much the standard in most databases.

Mysql: Change account password

Through the mysql client: update mysql.user set password=password('NEW_PASSWORD') where user='USERNAME' and host='HOSTNAME'; flush privileges; Through the command line: mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.