Speed of MySQL Question

[westmich]

I know MySQL is supposed to be extremely fast when it comes to running Select statements. That, that was one of the goals built into it from the start as a database for the Web environment.

Does that mean that its not so fast for running Inserts, Updates, and Deletes? Is there a comparison?

Just looking into building an application that I would prefer to platform independent with its own database rather then create modules for MS SQL Server, Oracle, etc... The application would make heavy use of Inserts and Updates.

[Aes]

I don't know the physical limitations of MySQL; I would imagine it is limited only upon the server on which it is running. Select queries are the fastest I believe, however, basically anything you do in MySQL is going to be done in a very decent amount of time, even if there are heavy amounts of inserts, updates, deletes, etc. Look at SitePoint forums, or any other vBulletin powered forums with 9,000+ members for that matter.

[DR_LaRRY_PEpPeR]

SPF are mostly reading from the DB (relative to the writing). there isn't that much writing going on.

westmich, i hear that MySQL is slower on writes that other DBs. don't know by how much. it's plenty fast when there's only one write going at a time. so it depends if your application is going to be doing a lot of writes at the same time. one of the best ways to kill MySQL and kill it fast is to do a lot of concurrent writes on the same table. it will really slow down since it doesn't have row level locking. the other way (to kill it) is to have a lot of simultaneous connections.

[Marshall]

In general, enterprise databases such as Oracle and DB2 tend to be faster than their free counterparts because they handle their own memory management instead of relying on the underlying operating system. They also scale better for SMP (Symmetric Multiple Processor) systems and come with extensive data replication and transaction handling functionality, something which most free DBMS's lack.

Here are articles comparing various DBMS's:
http://www.phpbuilder.com/columns/ti...05.php3?page=1
http://www.dbmsmag.com/9611d52.html
http://www.dbmsmag.com/9611d56.html

The first article compares MySQL and PostgreSQL. The last two articles compare Oracle, Sybase, Informix, MS SQL Server, DB2 and CA-OpeningRes.

- Marshall

[westmich]

I am looking to build something along the lines of 'real-time' traffic analysis/personalization similar to what the big e-commerce sites do. It would probably involve at least one or two inserts/updates everytime you click a link.

My personal experience is more with MS SQL Server, I know its pretty fast. I just finished part of a project where users 'build' their own content. The page runs 16 Inserts through MS SQL Server/Cold Fusion and takes about 80 milliseconds, virtually instant. Whereas, I've noticed submitting posts here may take a few seconds. That's not a big deal, but my application would be doining inserts/updates on every single page.

I don't mind staying with MS SQL Server, but the possible clients maybe hosting this application on their own servers so I was looking to make it a cross-platform as possible.

[MattR]

The only major problem I've noticed with MySQL comes when you have many, many people getting exclusive table locks on updates, deletes (and I think inserts as well). Because MySQL locks the entire table everything else is blocked (all other updates, selects, deletes, etc.) until it finishes. You can get quite a backlog when you have 800 users trying to do the same thing.

InnoDB is a step forward but there are still significant bugs (check out LiveJournal.com and read their woes).

In my opinion, if you have the need (e.g. traffic) then go ahead with a better RDBMS. If not, don't waste your time/money porting it to a commercial DB. You can always try PostGRES etc. (or Sybase ASE 11 ).