Helpful MySQL Queries

Put queries here that you feel are helpful. Be sure to indicate what the query is doing <grin>.

Note: As a general rule DO NOT use a query on a customers system unless you are absolutely sure that you're not going to get a huge return. If a query has many results to return or has a lot of data to sift through it could bring the server to it's knees. Remember: A customer system is not a test system. Here's an article on how to kill a process in MySQL if you manage to mess it up Sometimes it is helpful to use a COUNT statement on your select statement first to see if you're going to have a lot of data to sift through. Also, the LIMIT (MySQL) or TOP (MSSQL) statement can be helpful. However, the SQL server will still have to find all the values that fit your query, it won't go to the limit or top value and then stop.

MySQL

Select the minimum and maximum value in a table

select min(f_delivered), max(f_delivered) from t_message;

Select list of users who have had mail archived

select f_first, f_last, f_mailbox from t_abook where f_uid in (select f_uuid from t_uuid_mapping) order by abook_id \g

Select list of users who have never had mail archived

select f_first, f_last, f_mailbox from t_abook where f_uid not in (select f_uuid from t_uuid_mapping) order by abook_id \g

Find Hashes of specific date range from when the attachment was archived

SELECT hash FROM t_document WHERE (date BETWEEN *1433200981 AND *1439076181) ORDER BY date DESC;

If you know the exact timestamp

SELECT hash FROM t_document WHERE date = xxxxxxxxxx;

Re-create the admin user in a Retain system where the user tables are blank

Use the SQL file found here to fix this issue.

WARNING: Only do this if the tables are blank, otherwise you could break something.

Note: The dates are timestamps in Unix time (or POSIX/Epoch, if you prefer)

Helpful MySQL Queries

Contents