Save typing effort for complex MySQL queries with custom variables or procedures

To save time and because it’s not that smart to repeatedly retype the same things multiple times, you can user custom variables in MySQL like that: SET @store := 1, @name_id := 71, @short_desc_id := 72, @desc_id := 73; SELECT CPE.sku AS sku, CPEVN.value AS name, CPEVSD.value AS short_description, CPEVD.value AS description FROM catalog_product_entity AS […]

Simulate a MySQL query before running it

It is possible to Simulate a MySQL query before running it with the use of  MySQL-Transactions and the build in ROLLBACK-Feature of MySQL: For example: START TRANSACTION; SELECT * FROM nicetable WHERE somthing=1; UPDATE nicetable SET nicefield=’VALUE’ WHERE somthing=1; SELECT * FROM nicetable WHERE somthing=1; #check COMMIT; # or if you want to reset changes ROLLBACK; […]

Find duplicate entries in a MySQL Table

You can use the following query to get all duplicated records of a MySQL table having equal values for the respective field: SELECT fieldname, COUNT(*) FROM tablename GROUP BY fieldname HAVING COUNT(*) > 1 This query will give you a list of all the entries having the same value for fieldname. It also will show […]

Get table or DB size im Megabyte

Sometimes it is really useful to know how large a certain database table has become. With the following SQL statement you get a list of all tables of the currently connected database with their rounded size in MB: SELECT table_name AS “Table”, round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB” FROM information_schema.TABLES […]

Get current user, mysql version and other status information

To get information about the currently connected user and more MySQL status information, you can call the following command if you are already logged in: This will give you an output like that: ————– mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 1044395 Current database: Current user: niceguy@localhost SSL: Not in […]