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:
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.
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.
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.
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.