Advantages of MySQL over Access

Discussion in 'OT Technology' started by Yep, Oct 15, 2005.

  1. Yep

    Yep Knick knack paddy whack, give the old dog a bone

    Joined:
    Jan 22, 2001
    Messages:
    4,603
    Likes Received:
    0
    Location:
    South Jersey
    I'm sure there are quite a few, but I don't work with them very often. In fact this is the first time in a long time that I have coded an app that interacts with a database.

    The database I have is incredibly simple. It only has 2 tables. One table has 10 fields and another has 4 fields. The size is about 300KB after being compacted. The original version used a series of text files, however, I find it to run much quicker locally using an Access database.

    When I finally implement the app, it will be on a Windows 2000 Server possibly in a shared folder... though I think MySQL actually "runs" on the server no?

    While I'll be "sitting on" the server, other users will be connecting remotely at speeds of around 150KB/sec. I've read that when you connect to and run a query on an Access database, the entire file is copied to the remote PC.... True/False?

    If I run a MySQL database is there an actual server that runs and has the data cached and ready to spit out only what the SQL Query requests or sends? I'd rather have the server return 4KB of data, rather than all 300KB of it, if that is feasible.

    I'd appreciate any advice, especially if any of my logic is off, since I never really use this stuff.
     
  2. FagaBeefe

    FagaBeefe I live for my initials

    Joined:
    Sep 10, 2002
    Messages:
    1,508
    Likes Received:
    0
    Use MySQL. It doesn't cost anything and is much, much, much more robust then Access.
     
  3. Yep

    Yep Knick knack paddy whack, give the old dog a bone

    Joined:
    Jan 22, 2001
    Messages:
    4,603
    Likes Received:
    0
    Location:
    South Jersey
    I just installed it, and I like it. As much as I love a command line interface, is there anything I can use that is graphical that will allow me to work with the tables and columns and a graphical format such as Access? I think I saw somewhere that you can connect to it using Access.

    [EDIT} Found a way to link to it with Access but there are certain things I can't change. Anyone know if there is a free GUI editor?
     
    Last edited: Oct 15, 2005
  4. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    phpMyAdmin if you have it on a server with Apache/PHP installed. It's not quite like Access where you can design forms and such, but it's nice for manipulating tables and things.
     
  5. Yep

    Yep Knick knack paddy whack, give the old dog a bone

    Joined:
    Jan 22, 2001
    Messages:
    4,603
    Likes Received:
    0
    Location:
    South Jersey
    I downloaded SQLYog. Works great.
     
  6. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Other than being free, what are the actual benefits for an application like his? I'm not all that familiar with Access's capabilities, and I'd like to hear someone that is elaborate on this a bit.
     
  7. GlassUser

    GlassUser send an email not a pm OT Supporter

    Joined:
    Oct 26, 2003
    Messages:
    265,149
    Likes Received:
    147
    Location:
    Pearland, Texas
    Okay I'll bite.

    For you access is probably better. It does basic database stuff easily. You can interface with it through the ODBC drivers in windows (eg you just need an access file, you don't need access installed). It will start to have issues after about ten concurrent connections, and lacks plenty of advanced stuff (most oft missed are stored procedures), but it doesn't sound like you'll need any of that.

    Sure MySQL is good, and it's free, but so is access and access will take a lot less time and tinkering. I don't think you'll need any of its added benefits.
     
  8. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    So basically for his needs it comes down to Access having an easier interface than MySQL, and the fact that you can interface Access files without the actual database system running. Does Access have transactions?
     
  9. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    Access does not have transactions. MySQL doesn't either (version 5 might, but I don't know).

    Reasons to run MySQL:

    Access usually has a low limit on the number of concurrent users (about 8 or so). MySQL is 100. Use a persistent connection to MySQL and use just one connecting for all your users.

    ODBC is slow. If you can connect to a database by any other means, then do so. If ODBC is all you have, then Access or MySQL won't matter.

    Speed. MySQL wins here everytime.

    Storage. I'm not sure about Access's max storage abilities. MySQL can handle several gigs easily without a problem.

    I don't have any facts, but I'd argue MySQL is more stable under load than Access is.

    Now, if you were talking about MS SQL server versus MySQL, then don't walk, but run to MS SQL. Its a world better than MySQL and a ton better than Access (and you get transactional processing plus a ton of other goodies). Although with MS SQL, you have to shell out some clams versus the price on MySQL.
     
  10. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    MySQL 5 has been released, and definately has transactions. But so did MySQL 4, when you used BDB tables as the storage mechanism ;)

    And... I've had to deal with MS SQL performance issues for months and months. It would sloooooooow down and have to be rebooted. Maybe this is fixed in 2003, but on win2k AS 2000 I have to give SQL Server a thumbs down.

    Personally, I'm looking at MaxDB for the application that I am developing.
     
  11. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Suppose one were looking to pay for a database server. Any thoughts on MS SQL vs. say, Oracle?
     
  12. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    MS SQL is cheaper. Thats it niche.
     
  13. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA

    I don't think you really can answer that question with out what the application is going to do, how big the tables are, how many transactions per second is estimated.

    Based on that information, you may just realize that mysql is a better choice than shelling out tens of thousands of dollars for oracle or mssql.
     
  14. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    MS SQL Server isn't 10s of thousands of dollars, man.
     
  15. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    While you are right not 10's of thousands of dollars. But still very expensive. Standard processor license being $5k. That's quite a bit, especially if you can get it working just fine with Mysql.
     
  16. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Ah, I see. So it basically splits the difference between a decent free DB like MySQL or Postgres and a real commercial-grade DB like Oracle?
     
  17. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    There are other alternatives for the enterprise. MySQL is lightweight. Check out MaxDB.
     
  18. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    I haven't read up on this but what is the difference between MaxDB and Mysql.

    I wouldn't really call Mysql lightweight. It is very strong and very powerful. It's starting to run a lot of big systems.
     
  19. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    MySQL is open about it being lightweight. Thats its thing.

    Anyway, they also support MaxDB.
     
  20. Astro

    Astro Code Monkey

    Joined:
    Mar 18, 2000
    Messages:
    2,047
    Likes Received:
    0
    Location:
    Cleveland Ohio
    I've worked with SQL v7 and 2000. If design your databases right, MS SQL does a pretty nice job. I'd definitey take it any day over MySQL if price wasn't an option. I work with Oracle now. Its definitely on par to MS SQL, although its SQL language is a bit quirky (in v8 at least - 9i and 10g have improvements to the language from what I've read).

    I'd say it boils down to what you need versus what you're willing to pay for. Plus take into account your OS platform. If you're running a Windows shop, go MS SQL. Access was built for home use. If it wasn't then everyone would be running Access istead of MS SQL server.
     
  21. Yep

    Yep Knick knack paddy whack, give the old dog a bone

    Joined:
    Jan 22, 2001
    Messages:
    4,603
    Likes Received:
    0
    Location:
    South Jersey
    For what I'm doing MySQL seems to be more than sufficient. The queries run much quicker than I thought they would, and I'm connecting to the server through a VPN connection right now.
     
  22. FagaBeefe

    FagaBeefe I live for my initials

    Joined:
    Sep 10, 2002
    Messages:
    1,508
    Likes Received:
    0
    I'm glad MySQL is working for you.

    I worked with Oracle, SQL Server, and DB2 before MySQL and I'm sold on it. I've been working with it for about a year now and my database is about 14GB and handles on average 150 queries per second (v 4.1.12). It took awhile to find the ideal configuration, but after reading as much on the subject as possible (both online and books) everything is running great. I also replicate the database (real-time) to 3 other MySQL servers. I only wish I ran it on Linux instead of Windows. The overall performance is better.
     
  23. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    If you like it that much on Windows, you'd be super-impressed with it under Linux I think. I remember having terrible experiences running it under Windows just for little tiny databases.
     
  24. FagaBeefe

    FagaBeefe I live for my initials

    Joined:
    Sep 10, 2002
    Messages:
    1,508
    Likes Received:
    0
    It's on my list for third quarter of 2006. :bigthumb:
     
  25. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    are you kidding me? What are you looking at? A developer version? From a reseller (on the cheaper side) a 1 Enterprise processor license is still almost $12k. I don't know anybody who runs a production MS SQL Database on anything than less than 2 proc system. You're already at $24k. Just a year ago, SQL Server 1 proc licenses were ~$20k. Only reason they are so cheap right now is that 2005 is going to be released within the next couple weeks. Oracle is damn expensive, but MS SQL is not far behind.
    http://www.atomicpark.com/xq/aspx/m...amilyid.6048/buy.software/qx/productlist.html
     

Share This Page