MySQL data explorer / data analysis tool

Question

I am looking for a tool which will connect to a MySQL database and, given a table name, will allow me to analyze the data in the table.

I would like to be able to (the most important features first):

• count how many non-empty values are in every column
• apply filters (eg. I would like to analyze only rows where creation_date>='2014-01-01')
• analyze relations between data in columns (eg. what is the average age of users in every country)
• draw a histogram of the values in a given column
• perhaps analyze the data in a pivot table

The tool should be able to analyze tables that contain at least a few hundred thousand rows with many columns.

It does not matter to me if it's a web-based tool or a desktop application (Windows).

I am a developer and I know SQL, but people who will be using the tool can only write basic SELECT queries. So the tool should be fairly simple to use.

I already found one tool that seems to meet my requirements. It's dbForge studio (http://www.devart.com/dbforge/mysql/studio/data-reports.html). I tried the evaluation version and it seems to me that it can analyze only 1000 rows at a time (which is of course not enough).

Do you know such a tool?

Answer 1

I found some programs that at least partially answer my needs. Sorry that I did not provide links for all of them, but my reputation allows me to post at most 2 links.

SOFA Statistics

• http://www.sofastatistics.com/home.php
• it can connect directly to MySQL

Quote from the official documentation:

SOFA can be used to:

• make charts e.g. Pie Charts
• produce attractive report tables on your data e.g. gender vs age
• run basic statistical tests e.g. one-way ANOVAs
• and generally increase your understanding of your data.

SOFA is great for initial research and exploratory analysis - or as someone put it rather nicely, “statistical/mathematical doodling”. It doesn't have every statistical test you could possibly need, but for many purposes it has more than enough

sqlDashboards

A tool that can display charts and tables based on database queries.

JMP

A very powerful tool that can can import data from MySQL (and many other databases as well) and analyze it in various ways; it goes far beyond my needs.

Other tools

I found more tools at http://alternativeto.net/software/sofa-statistics/ .

Answer 2

The tool that you are probably looking for is the ipython library pandas you can query your database from within iPython using pandas and do a lot of analysis and charting with it.

• Both iPython and Pandas are free, (both Libre and FOSS).
• Pandas can handle very large data sets
• You can wrap your functionality, results and instructions into an iPython notebook to make it simple to use for your users. An example is here and here.
• The combination, sometimes with other libraries, can work with various databases so you are not limited to MySQL - if you move to another DB there is minimal work to do.

Answer 3

Database .NET

is an innovative, powerful and intuitive multiple database management tool With it you can browse objects, design tables, edit rows, export data and run queries with a consistent interface.

SQL Power Architect

The SQL Power Architect Community Edition is a cross-platform, open-source data modeling tool with a GUI and an embeddable API.

MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

SQL Workbench

SQL Workbench is a free, DBMS-independent, cross-platform SQL query tool. It is written in Java and should run on any operating system that provides a Java Runtime Environment.