current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question

put on hold as too broad by Craig Ringer, RolandoMySQLDBA, Max Vernon, Mark Storey-Smith, Paul White 17 hours ago

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.
    
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? –  Peter yesterday
    
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 yesterday
    
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!" –  DeBuGGeR yesterday
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 yesterday
1  
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 yesterday

2 Answers 2

up vote 3 down vote accepted

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 good for read heavy OLTP database loads. PostgreSQL is superior for analytical work (DW - data warehousing, or OLAP).

share|improve this answer
    
Can u point out wether I would need any tools to this in Postgres? –  DeBuGGeR 7 hours ago
up vote 2 down vote

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.

share|improve this answer
    
Can u point out wether I would need any tools to this in Postgres? –  DeBuGGeR yesterday
1  
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 21 hours ago

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