database type: mySQL, MS SQL and Access

I guess those are most used db types in CF, right? I need to know which one of them is most stable and faster - mySQL?

Also, please tell me if communication with all of them is in the same manner - trought DSN's and CFQUERY and if cfquery is with the same syntax for all of them, for example, will

<cfquery name="qry" datasource="dsn_name">
SELECT * FROM products WHERE product LIKE '#letter#%'
</cfquery>

work with all those db types if they have the same attributes (never worked with MSSQL, but I am sute that mySQL and Access has columns etc.)

If somebody have time to explain this detailed with some code examples (if necessary regarding differences), that would be great.

kiteless and other good people, thank you in advance

Given the same server hardware and competent admins, it goes:

Performance (fastest to slowest):
Oracle
SQL Server
MySQL
Access


Cost (low to high):
MySQL (free)
Access ($200)
SQL Server ($7000)
Oracle ($50,000+)

MySQL is a great DB and it blows away Access. It's only main problem (a big one in my opinion) is it's total lack of a GUI interface. There is an open source effort called PHPMyAdmin to let people manage MySQL but it pales in comparison to SQL Server's Management Console or TOAD (an Oracle Tool) and even Access. The flip side is that other than this, MySQL is fast and stable and FREE. It is not quite as fast or scalable as the "huge" databases like Oracle and SQL Server but for most people it is plenty for what they need. We use Oracle becuase we have HUGE databases.

Thanks, I was expectiong to hear you

By your suggestion, I opened account at CrystalTech and purchuased their Intermediate Plan and I am pretty satisfied, thanx. They support mySQL and Access db (maybe even MSSQL, not sure), and if costs are so high as you mentioned, I don't think I'll ever find webost which supports Oracle and MSSQL db's, or at least, won't be able to pay their hosting account fees

Now, I built my site with Acess backend and I thought mySQL would be better solution - can you tell me how much in % is he faster/stable than Access, at least approximate value. What I also want to know regarding my code is would I need to change anything at my pages except the DSN ofcourse if I'd like to change database backend from Access -> mySQL? Of course, table structure and data will be transfered into appropriate mySQL db.

And what about if I (will ever) store data in Oracle or MSSQL, would it be the same?

The answer is, it depends. If you are only doing basic SQL then any database should handle it in about the same way. But there are many database-specific elements as well. Things like triggers, stored procedures, user-defined functions, transactions, and proprietary commands. For example, Oracle has PL/SQL. SQL Server has T-SQL. These are not compatible.

MySQL is much faster and more stable than Access. Access isn't even a true RDBMS, it is a file-based database. That said, if you are not expecting tons of queries or huge databases, Access may work just fine for you. It sure is easier to deal with than MySQL.

And you wouldn't even necessarily need to change your DSN to switch databases...just delete that DSN and recreate the same DSN name pointing to the new database.

Of course, if you do need to change your DSN, it will be very easy, just change 1 variable. You are OF COURSE using variables for all of your database info such as DSN Name, RIGHT? ;-) (If you are not, you should be)

my access db has 2500 records and I guess there will be maximum 10 queries/sec, not more, is it able to handle it?

Access isn't strictly a RDBMS, btw ... (kiteless beat me to that point )

There are plenty of MySQL GUIs available - 2 at least for free download right from the MySQL.com website, plus many more can be found googling.