One of the new features of MySQL Workbench 6.0 is Table Data Search. The main purpose of this was to ease data searching through the whole instance. Previously, we needed to use some tricks to get the query to run over all schemas that we’ve got on the server. Now it’s easy to find the searched term with much less hassle. This functionality is easy to use and provides searching through all columns and even all types. However, we can’t forget that due to the nature of this tool we must take some precautions to not overload your server.

To use this functionality we pick it up from the Database menu called “Search Table Data…” or just click the icon on the main toolbar (scr 1). The third option is to select Search Table Data.. from context menu when you right click on the schema list on some schema.
After that you will see the new screen (scr 2) with a few options which you must provide to get started working with it.

As you can see, the interface is very simple, but I’ll still try to explain some of the options. The first thing is the Search for Text input, where you just put the phrase that you’d like to find. The type of the phrase depends on the select box that is located below this input (scr 3). That select box has three different options of search type which are:

Search using =
Search using LIKE
Search using REGEXP

The first one Search using =, is the simplest one, it just matches fields using the = operator.
Second option is little more powerful, it allows you to search using the database LIKE  operator where you can provide wild cards like % (match any character any number of times) or _ (match any char, a single time).
The third option allows you to use regular expressions.

Next to the selection box, you’ll see two inputs described as Max. matches per table, and Max. total matches. The first one is responsible for limiting search occurrence through one table. The second one will limit the whole search, so when there will be over 10000 (initial value) entries, search will stop. Last option is the check box named Search columns of all types. Initially searching is done through only text fields, when this option is enabled, then all columns will be used and will be cast to the char data type. This check box have a great impact over whole server performance use it with caution!

Now that you know how each option relates to the searching, I’ll try to step through a sample search and describe the results. I assume that you’ve got sakila database, cause I’ll make a sample using that database.  

1. open the Search table data…
2. enter text mary into the Search for Text field
3. check if the Search using option is set to the equal sign
4. check if the Search columns of all types check box is unchecked
5. select the schema (or column), that you’d like to be searched for the matching phrase in the schema selector
6. press the Start Search button.

As in the screen shot (scr 4), your result should be the same. You can click on the arrow in the result list to expand details of the row. The columns are as follows:

Schema – name of the database that holds the columns that matched the criteria
Table – name of the table
Key – primary key value assigned to the data that match criteria
Column – column name that holds the matched data
Data – the top row will contain information, how many times the phrase were matched in the details it will contains the matched data

There is also context menu for the result set, it’s available when you right click on the result set. The menu allows you to copy query that where used to find the matching rows. you can also copy query that will match the rows against primary key, and the last option allows to copy the key values.

And that’s all, you’ve done your first search using the new Search Table Data option. Please remember that using this feature have very big impact on general server performance because you’re generally doing full table scans. Stay with us to get more information cool information.