6

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.

Pinser
  • 163
  • 1
  • 1
  • 5

2 Answers2

12

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.

Vérace
  • 30,923
  • 9
  • 73
  • 85
6

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.

jynus
  • 15,057
  • 2
  • 37
  • 46