I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I have no luck of running these kind of queries, so I am manually calculating this data and storing in tables.

To be more specific of my application, I need to show charts/graphs on iPhone. So a users usage of words per language per day/week/month. I dont need realtime but the rows for one month of usage for a user is approx 5000.

I want to understand if it is possible for mysql to have warehouse scehemas and execute such queries without performance issues. What could be my options.

  • Postgres?
  • MySQL Enterprise?
  • Cubes

I really cant pay for enterprise solutions, I am ready to write code for this kind of processing in my application (which I have) but want to migrate to correct way.

I guess the most ideal case for me would be that without using any tools I can create fact and dimensions in my current db and then shift my api to run these olap queries. From what I have gathered this is not possible for mysql. Postgres is showing some promise but still reading.

  • What sort of volumes are you talking about? And what front end tools are you using? Is it ad hoc analysis? Drill down reports? Or do you need to build this into an application? Also how volatile is the data and how often do you need to update your results? – Sir Swears-a-lot Sep 3 '14 at 9:05
  • You should do some reading about OLAP, star schemas, etc. In general you want to export your data from your OLTP system into an OLAP DB for analysis, usually transforming it in the process using automated ETL tools. Yep, acronym soup. – Craig Ringer Sep 3 '14 at 9:35
  • I updated my question! Data change is hourly. Consider a addictive word finding game. So when a user records his used words we have to reflects his/her stats as "+1 German Words have been found by you!" – JehandadK Sep 3 '14 at 9:37
  • 1
    I'm not aware of anything that'll automatically maintain fact tables in stock PostgreSQL or MySQL, no. You can build it with triggers or use ETL tools. – Craig Ringer Sep 3 '14 at 9:52
  • 2
    You can do it in MySQL - but as @jynus (and I) have pointed out, MySQL is not optimal for your use case. You can construct a data warehouse using any RDBMS, but some are designed with OLTP workloads in mind (MySQL). PostgreSQL is more suited to analytical DW type workloads. – Vérace Sep 3 '14 at 13:15

You can certainly use MySQL or PostgreSQL for this requirement using Python as your database access language. I've never used Python cubes so I can't speak to that.

I would recommend that you use PostgreSQL - it has windowing functions and CTEs (common table expressions). It also supports CHECK CONSTRAINTs and a full range of set operators.

  • MySQL is reasonably good for read heavy OLTP database loads but little else.

  • PostgreSQL is superior for analytical work (DW - data warehousing, or OLAP) while at the same time being an excellent all-rounder in the OLTP arena.

  • Can u point out wether I would need any tools to this in Postgres? – JehandadK Sep 4 '14 at 13:48

This is a very broad answer, but that is because the question is very broad, too.

MySQL has never been focused on OLAP, for one particular reason, its main engine, InnoDB, and MySQL cluster (NDB) are optimised for OLTP loads. Doing analytical queries is usually slow because it involves reading lots of rows.

That does not mean that you could not do OLAP on MySQL, there are some people suggesting options for doing that in MySQL with database structure changes. There are also pluggable specialised engines and connectors that may or may not be helpful depending on your expected queries, like InfiniDB or Impala.

Having said that, you can search for alternative database engines that may be suitable for your needs.

  • 1
    Can u point out wether I would need any tools to this in Postgres? – JehandadK Sep 3 '14 at 14:24
  • 2
    I'm no PostgreSQL expert, so I can't tell about inner workings, (that is why I only mentioned MySQL) -maybe someones else can help you with that. But at the very least, the concept of using a star system for a Relational OLAP would apply equally (check these slides: wiki.postgresql.org/images/3/38/… ). The difference between using Postgres or needing a specialised tool depends on expectations. It's true that there are some extra programming features that are unavailable or very poorly implemented in MySQL that may be useful in this case. – jynus Sep 4 '14 at 0:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.