Poll: Is MySQL a good choice for a web app?

What kind of data storage?

  • MySQL

    Votes: 38 80.9%

  • Postgres

    Votes: 2 4.3%

  • SQLite

    Votes: 0 0.0%

  • Flat files, baby!

    Votes: 1 2.1%

  • Other (MS SQL, Oracle, etc.)

    Votes: 3 6.4%

  • None, I don't like data, it burnssssss

    Votes: 3 6.4%
  • Total voters
    47
Status
Not open for further replies.
Supergeek

Supergeek

The Uberest Nerd
May 19, 2007
1,039
12
0
Denver
Cliff Notes: Should I use SQLite, MySQL, Postgres, etc?

Long version:

I recently developed an app for someone, and I had to make a decision about how to store my application's data.

I had seen references to SQLite before, so I decided, what the hell, why not use this project as an excuse to learn SQLite, and then I don't have to worry about supporting the creation of a MySQL database, etc.

What I've learned from this project is that I don't really like SQLite, but it's a good choice for someone who doesn't want to run MySQL or Postgres.

That said, if you were to buy a web application from someone, and it needed to store data, what would you prefer?
 


Everything I've worked in has used MySQL. I don't know Postgresql; I don't even know anything that rhymes. So I'd be using MySQL, although some small projects have used flat files. I've also worked in mssql. I actually prefer MySQL to mssql.

I'd like a layer of abstraction though, so it'd be possible to swap it out for whatever the client needed (without grepping for mysql_query, etc).
 
Depends on what you need and what your comfortable with...

MySQL is the defacto choice for most developers because its the most common database system out there. I have yet to see a *nix based host not provide MySQL. For most situations, MySQL is fine - unless you have a specific reason or functionality requirement not served by MySQL then it should do you fine.

Postgres on the other hand has a smaller following but comes with features that you see more from heavy duty enterprise level database systems.

There is a good article comparing MySQL & Postgres at http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
 
LazyD said:
MySQL is the defacto choice for most developers because its the most common database system out there. I have yet to see a *nix based host not provide MySQL. For most situations, MySQL is fine - unless you have a specific reason or functionality requirement not served by MySQL then it should do you fine.http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
Click to expand...
That's a great point. I think most hosting companies do offer MySQL databases, unless they're running Windows of some sort. I think from this point forward I'll concentrate on MySQL development since that's what I'm familiar with, and that's what I like.

I'd like to make an abstracted front end as suggested, that could use any database type (Select your database type from the menu...), but I don't know how much more work that would be. Using an OO layer would certainly go a long way towards accomplishing that, but I'm really not an OO programmer.

Thanks for your input.
 
Very theoretically, though.

The smaller following of PostgreSQL also means bugs are fixed slower.

At my work, we use both, but PostgreSQL is one of those "legacy" things that is used because it was used.

Both Post and My-SQL are shouldering heavy loads, so no complaints from that side.

Use whatever fits you best, but you'll have an easier time with MySQL - easier because it is so common.

::emp::
 
Depending on how OO you are planning to get with this project, or the general requirements of the database layer, you may consider something like Doctrine or Propel - They are all around ORM/Activerecord setups that abstract your DB layer. Im more familiar with Doctrine then propel, but Doctrine gives you functionality for table relationships, a query building api, validation, a command line script to generate model classes and migrations (versions of table structures), behaviors such as Soft Delete, Versioning, etc. Doctrine extends PDO in most, if not all cases.

http://www.doctrine-project.org/

If your not looking for something that heavy, consider using PDO. PDO is the built-in PHP database abstraction layer that still uses raw SQL but gives you a better handle over thing including prepared statements, sanitization, etc. It has support for a number of different database types including MySQL & Postgres. The full list of drivers can be found on PHP's website... PHP: PDO Drivers - Manual
 
In my opinion, unless you're working with something like cold fusion, active server pages, or other brand specific (Microsoft, novell, macromedia, etc) server sided scripting language, then MySQL is the most versatile choice. If I were primarily an ASP coder, I would probably find it more worthwhile using M$ SQL Server, instead of an ODBC wrapper around MySQL. And of course there are those days when choosing between a relational database or a flat file can be a toss up depending on project needs.

Also as mentioned earlier, like PHP, MySQL is offered/supported on nearly 98% of all hosting providers.
 
MySQL is great choice simply because of it's prevalence in cheap hosting. If you're coding this under .NET or something else Windows-centric then I'd definitely lean towards MS-SQL.
 
sqllite
sqllite is a great app for small applications where you need the smallest footprint possible. Generally you'll find it behind lots of 2.0 apps that require significant datastores (browsers, chat programs etc) that don't require you to install a full RDBMS. However, it only provides a small subset of datatypes or storage classes and this can impact performance on larger systems. I use sqllite for portable desktops apps etc.

mysql
mysql is great as a database for your bigger applications where there are large amounts of data to work with, you have no budget and you want a solution that just works (and keeps getting better). The only thing to remember with mysql is that is is cool. Cool as is it will breaks the rules and it well tell you, but not care enough to actually do something about it. This is great database when you control it. It assumes the schema defines the rules and you know who to write a decent sql statement. Beyond that it doesn't care. I use mysql for most of my web based applications and desktop apps. Business logic should not be written into the statements. Its a database and its about data storage and retrieval only.

mssql
This is a awesome RDBMS where you have money and nothing to spend it on. Its quick, powerful and with a decent IDE you pretty much don't have to do anything other then match the pretty colors. Rules are strictly defined and lots of validation checks occur prior to runtime meaning its a great place to build you business logic into (if you're that way inclined).
This db should only be used for commercial applications where there is someone other then you to install, configure, secure, license & maintain it.

oracle
You would use oracle when someone higher up then you makes the decision for the RDBMS the company will be embracing. This is the worst of worst. It hates sql and instead you need to use pl/sql for the more trickier stuff. Its footprint is massive, it causes ozone (and possibly cancer) due to all the power it consumes, it costs like a squillion dollars a license and helicopter pad to be installed at its location for when the oracle guy comes to fix it. This is a great database to use when you are a CEO of company who might get asked 'what database do you use?'


As for other db mentioned i won't even bother. so to recap
sqllite - great for small apps which require a data store and are deployable.
mysql - great for larger apps where where you aren't using a MS IDE or language and you don't need a lot more the storing and retreiving.
mssql - great for larger apps where you have a MS IDE, bucks to spend you want to the software to manage the data also.
oracle - ...
 
So basically:

- MySQL if you have a serious site

- SQLite if you have an app and need something light and portable

- Other shit if you can't avoid it (big corporation swinging its budget dick around and/or requiring "enterprise level" support)
 
I haven't used MySQL much and SQLite not at all - but the latest version of MS-SQL Express version is *free* and has no concurrent users limit. The database is limited in size to 4GB (which is big enough for me). You also get the management GUI which makes creating tables/views etc very fast.

If you are running *nix then it is a different story - but on Windows MSSQL is hard to beat.
 
I think its fair to say at this point, for a web-based app in a *nix environment that MySQL is what you want.

SQLite seems to find its place with things like Adobe AIR apps, etc.
 
Hi,

if u want to create large website go to MSSQL or ORACLE ------ I have an bad experience with MySQL and SQLite -------- for large data, this will give problem.
 
Supergeek said:
So basically:

- MySQL if you have a serious site

- SQLite if you have an app and need something light and portable

- Other shit if you can't avoid it (big corporation swinging its budget dick around and/or requiring "enterprise level" support)
Click to expand...

Correct
 
Status
Not open for further replies.
Top Bottom