SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Speed of MySQL Question

    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.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  2. #2
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  4. #4
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks for the input

    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.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ).


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •