5 Minute Tips: MySQL Commands

Welcome to your learning coffee break, today on MySQL commands! I like working on the MySQL console. It’s available everywhere, and keyboard trumps mouse as soon as you know the right commands and shortcuts. But there’s more to it than just entering queries. So, today I’ll share a few tips to improve your productivity with the MySQL console:

You want to quickly execute something on the shell without leaving the console? Use the system command:
```
system rm -rf var/cache/*
```
Viewing a record with many columns is confusing? You can show the result vertically if you finish your query with \G instead of ;
```
SELECT * FROM sales_flat_order WHERE entity_id=42\G
```
With the pager command you can specify any shell command that receives query output. pager without parameters, or nopager sets it back to default STDOUT.
Examples:
- Almost always useful: scroll through big results and search within them:
```
pager less
```
- Get a checksum of the result. Useful to compare results for equality.
```
pager md5sum
```
- Filter results by regular expression “PATTERN”. Useful if you want so search for content over several columns:
```
pager grep PATTERN
```

Combining vertical results with “pager grep” is also useful if you want to view columns that match a certain pattern:

I'm enjoying my newly aquired MySQL command knowledge 😊 pic.twitter.com/yAjowSXzT0

— Fabian Schmengler (@fschmengler) July 28, 2016

Besides these console commands I’ll share some general-purpose DDL queries that I use all the time:

Looking for a table but can’t remember the exact name? Show all tables with “index” in their name:
```
SHOW TABLES LIKE '%index%';
```
And how did the columns look like? Show the column definitions for the catalog_product_index_price table:
```
DESCRIBE catalog_product_index_price;
```
Need more info about the table? Constraints, engine, charset?
```
SHOW CREATE TABLE catalog_product_index_price;
```

And don’t forget that in Magento you can enter the console directly with