Well you can discount Access pretty much straight off for anything but small or low traffic sites, It starts to flake out when there are more than 10 concurrent users.
MySQL is fast and has a small server footprint but lacks SPs (unless you are on Ver5) and needs a seperate user database
MS SQL is a bit slower in some aspects and has a much larger footprint, It does have SPs so you can save some CPU load there. Integrates into the windows user database seamlessly (in theory)
There are lots more pros and cons but that's probably the main points.
For most things MySQL works just fine. Unless you are talking millions and millions of records you should be fine with MySQL. As for access, its cheap but slow if you have to do a large number of queries. Its good for small business applications but I would not recommend using it for any websites if you can avoid it. Security is also an issue here. Don't forget PostGre too
-dk
JeevesBond posted this at 12:31 — 18th April 2005.
In terms of popularity MySQL wins I believe, also it appears to be the most widely supported, in addition there are a variety of useful - more importantly free - tools available to help you configure your DB (phpMyAdmin etc). What do you have available and what tools does your host provide to support your decision?
There are many things to take into account, and each solution fits different purposes. If you give us more information on the choice you have to make, we will be in a better position to help.
Well considering your advies and what I have been thinking about the past week I figure like this.
Access - use when I need to send a script (+ DB) to a client. Easiest to configure
Mysql - The my sites and buisness ventures
I will look into mysql very soon. But I'm not sure how diffrent it will be from access. I'm used to editing the DB on a table and you need a SQL server to run a mysql db?
JeevesBond posted this at 19:40 — 18th April 2005.
MySQL is a SQL server, simply: You send it SQL, it does stuff.
Access is like having a DB front-end and SQL server both running on your computer: Access is the front-end, the Jet Engine (arf arf - I love calling it that) is the SQL server. As you get deeper into the workings of Access you'll find that all Access does is send SQL to Jet and Jet does stuff, hence it's behaviour is different to many other applications - this often manifests itself as appearing to be less user-friendly (although the average user should never go near Access, they don't know enough about DB design/management).
When you get MySQL all you're getting is the backend, it is the 'SQL Server' (unless you download some of their management tools as well), there is a command-line interface included but that's about it. So when you get this you'll presumably be building a nice front-end in your chosen language (PHP, VB, C++ etc).
The differences between Access and MySQL are easily highlighted if you download the MyODBC connector, you can then hook Access up to MySQL. So instead of Access sending SQL to Jet and Jet doing stuff, Access sends SQL to MySQL and it does stuff instead. The advantage of this is that you can create a true client/server interface where the client runs on one machine and the server runs on another machine - something that is not possible using Access alone (it can do client/file server but that's a completely different matter).
The word: 'Stuff' is used in this context to describe any SQL operation, e.g. the client sends "SELECT * FROM `products`;" the server grabs all the records from the products database and throws the result back to the client. Note that all the hard work is done by the server instead of the client
It would appear that the SQL strings I had to learn for access are also used in MySql example of one of them "SELECT volume, number, date, URL, manypages FROM dat ORDER BY date DESC". Is that a valid sting for Mysql ?
Thank you JeevesBond very informative.
Andy is right, SQL is in fact an ANSI/ISO Specification (SQL99) so databases - although their levels of conformance very and there are quirks - should all be able to parse and understand your SQL.
As said, there are quirks I'm afraid and you will occassioanlly find yourself getting infuriated as something that worked in Access doesn't work in MySQL (or vice-versa), in this case head to http://dev.mysql.com and search for the syntax; the documentation is quite good
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.
chrishirst posted this at 19:27 — 17th April 2005.
He has: 379 posts
Joined: Apr 2005
Well you can discount Access pretty much straight off for anything but small or low traffic sites, It starts to flake out when there are more than 10 concurrent users.
MySQL is fast and has a small server footprint but lacks SPs (unless you are on Ver5) and needs a seperate user database
MS SQL is a bit slower in some aspects and has a much larger footprint, It does have SPs so you can save some CPU load there. Integrates into the windows user database seamlessly (in theory)
There are lots more pros and cons but that's probably the main points.
Chris
Indifference will be the downfall of mankind, but who cares?
Venue Capacity Monitoring
Code Samples
dk01 posted this at 19:38 — 17th April 2005.
He has: 516 posts
Joined: Mar 2002
For most things MySQL works just fine. Unless you are talking millions and millions of records you should be fine with MySQL. As for access, its cheap but slow if you have to do a large number of queries. Its good for small business applications but I would not recommend using it for any websites if you can avoid it. Security is also an issue here. Don't forget PostGre too
-dk
JeevesBond posted this at 12:31 — 18th April 2005.
He has: 3,956 posts
Joined: Jun 2002
In terms of popularity MySQL wins I believe, also it appears to be the most widely supported, in addition there are a variety of useful - more importantly free - tools available to help you configure your DB (phpMyAdmin etc). What do you have available and what tools does your host provide to support your decision?
There are many things to take into account, and each solution fits different purposes. If you give us more information on the choice you have to make, we will be in a better position to help.
a Padded Cell our articles site!
bja888 (not verified) posted this at 17:40 — 18th April 2005.
They have: 5,633 posts
Joined: Jan 1970
Well considering your advies and what I have been thinking about the past week I figure like this.
Access - use when I need to send a script (+ DB) to a client. Easiest to configure
Mysql - The my sites and buisness ventures
I will look into mysql very soon. But I'm not sure how diffrent it will be from access. I'm used to editing the DB on a table and you need a SQL server to run a mysql db?
JeevesBond posted this at 19:40 — 18th April 2005.
He has: 3,956 posts
Joined: Jun 2002
MySQL is a SQL server, simply: You send it SQL, it does stuff.
Access is like having a DB front-end and SQL server both running on your computer: Access is the front-end, the Jet Engine (arf arf - I love calling it that) is the SQL server. As you get deeper into the workings of Access you'll find that all Access does is send SQL to Jet and Jet does stuff, hence it's behaviour is different to many other applications - this often manifests itself as appearing to be less user-friendly (although the average user should never go near Access, they don't know enough about DB design/management).
When you get MySQL all you're getting is the backend, it is the 'SQL Server' (unless you download some of their management tools as well), there is a command-line interface included but that's about it. So when you get this you'll presumably be building a nice front-end in your chosen language (PHP, VB, C++ etc).
The differences between Access and MySQL are easily highlighted if you download the MyODBC connector, you can then hook Access up to MySQL. So instead of Access sending SQL to Jet and Jet doing stuff, Access sends SQL to MySQL and it does stuff instead. The advantage of this is that you can create a true client/server interface where the client runs on one machine and the server runs on another machine - something that is not possible using Access alone (it can do client/file server but that's a completely different matter).
The word: 'Stuff' is used in this context to describe any SQL operation, e.g. the client sends "SELECT * FROM `products`;" the server grabs all the records from the products database and throws the result back to the client. Note that all the hard work is done by the server instead of the client
a Padded Cell our articles site!
bja888 (not verified) posted this at 09:50 — 19th April 2005.
They have: 5,633 posts
Joined: Jan 1970
It would appear that the SQL strings I had to learn for access are also used in MySql example of one of them "SELECT volume, number, date, URL, manypages FROM dat ORDER BY date DESC". Is that a valid sting for Mysql ?
Thank you JeevesBond very informative.
andy206uk posted this at 10:48 — 19th April 2005.
He has: 1,758 posts
Joined: Jul 2002
Looks like it would work... I believe SQL language is pretty much standardised across the most database apps
JeevesBond posted this at 11:52 — 19th April 2005.
He has: 3,956 posts
Joined: Jun 2002
Andy is right, SQL is in fact an ANSI/ISO Specification (SQL99) so databases - although their levels of conformance very and there are quirks - should all be able to parse and understand your SQL.
As said, there are quirks I'm afraid and you will occassioanlly find yourself getting infuriated as something that worked in Access doesn't work in MySQL (or vice-versa), in this case head to http://dev.mysql.com and search for the syntax; the documentation is quite good
a Padded Cell our articles site!
dk01 posted this at 15:11 — 19th April 2005.
He has: 516 posts
Joined: Mar 2002
Sounds like MS SQL Server 2005 will be out soon. Should be interesting to see what they add/change since SQL Server 2000.
-dk
JeevesBond posted this at 12:19 — 20th April 2005.
He has: 3,956 posts
Joined: Jun 2002
Bah, *grumble* Microsoft *grumble*
StudioWorks posted this at 23:23 — 20th April 2005.
They have: 33 posts
Joined: Sep 2004
MySql.. never used anything else
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.