what is the best/fastest DB in order to work with PHP and Linux platform ?

1) the PHP is installed on Linux server
2) the DB can be installed on the Linux server or on dedicated windows server.

we were thinking of using Mysql or MS sql.........

we will have a lot of records and very intense DB use.....
we are using now the mysql but we are thinking of moving to better one...

what do you think....

    You need to take into account a couple things here:
    1) What's you budget?
    2) How much power do you need from a DB? ie: Do you need the ability to do more complex queries? Use stored procedures? Triggers? Or are you just using very basic queries?

    Making it compatible to run across all platforms should rule out MS SQL, which has to be on a Windows server. Reverse to that, Postgres is very hard to get running on Windows(in my experience), but can be done. If you're doing just simple queries and won't be doing anything to intensive, stick with MySQL(it's popular for it's speed and ease of use). If you need something beefy, and have some budget, you should start looking into Oracle.

      lets say that I am looking for professional environment so budget is not the main issue....
      because PHP is very Mysql oriented I would like to know how stable is the main stream
      oracle/ms sql solutions with PHP.....

      and what should I choose oracle ? ms sql ? or stick to mysql
      I want to make the decision now in the beginning.

      thank

        Well, the enviroment we use is very load intensive(read 1000-2000 transactions per second), and houses a very large database somewhere in the upper terabytes. We use a clustered Oracle solution with a real time failover. Not to say you need all this, but it has been very good to us so far, with only one outage in the last 5 years. Of course, we have very good DBA's though. As far as PHP is concerned, I pull through about 30,000 hits/day on a site running reports and apps through this same database, and see no speed difference between pulling this data from here, MySQL or Postgres. The support built into PHP for OCI8 transactions is just as good, if not better, than MySQL support, once you learn the syntax. Now, I'm not a DBA, and have no real experience with MSSQL, so I couldn't tell you one way or another what is better. This is just my experience. There's a couple guys on here that are DBA's(or at least have the knowledge of), and they would probably be the best ones to explain the benefits of each one.

          Are you batching one big process that reads 10,000,000 rows and massages the data then spits it out, or do you need to support 5,000 concurrent users running an ERP application?

          Those are two wildly different things, and the database you might choose for one might not be the best choice for the other.

          For massive parallel load, there are only two databases I would recommend under PHP right now, and that is Oracle and Postgresql.

          The .net and .com tlds (top level domains) are run on Oracle, and it works quite nicely. The smaller, but still quite large and busy .org and smaller still .info tlds are run on Postgresql.

          No other databases are used in large tlds except those two, and for good reason. Those are heavy paraellel environments, something neither MSSQL nor MySQL excel at.

          On the other hand, if you want a report server to hold moderate amounts of data for a few people to access at a time, MySQL is a dandy database, and works quite well.

          Asking which database is fastest is like asking which is faster, a Pinzgauer or a Rail Dragster.

          If you're climbing mountains, one excels, if you've got a 1/4 mile of asphalt to cover, the other is a good choice. Neither is faster in the other's terrain of choice.

            the awswer to your question is 😛ostgresql

              Originally posted by amrigo
              the awswer to your question is 😛ostgresql

              As much as I love postgresql, and trust me, I do, for most new database users or for those on windows, I recommend firebirdSQL. Much smaller footprint, no maintenance required, and it's got most all the features you'd think of having.

                6 days later

                Originally posted by amrigo
                the awswer to your question is 😛ostgresql

                ^ whot he said 🙂

                  there is a nice admin-frontend to Postgresql: pgadmin
                  and a designer: pgdesigner, and even a web based frontend: phppgadmin

                    Hallo,

                    I'm a little bit surprised about the low ranking of MySql in this thread. I thought that prof. users like Mysql a little bit more.

                    My good point is that the knowledge in mysql is usable for the most hosts which are available on the net.

                    A bad point is that some features are not available in the current version (4.0).

                      Actually, Olaf, the worst things about MySQL, to me, are:

                      It scales poorly. Simple tests show a rather non-linear increase in response time as the number of writers increases. This means that if you have to wait 2 seconds for it to write a record for one person, instead of it taking 4 seconds to write the same amount of data for 2 people, it will take 6 or 8 seconds. God help you when you get to 20 or 30 writers. This problem exists with both myisam tables and innodb. It is the primary reason MySQL AB has NEVER published benchmarks that do things in parallel. It is the reason Postgresql is used by OSDL to tune the linux kernel, it does scale well.

                      It has poor SQL compliance. It has issues like using ` to quote identifiers instead of ", ignoring -- as a comment unless it has a space after it, allowing you to drop a table with dependent tables under it. It allows the following incorrect syntax:

                      select a,b from table group by a

                      and many more poorly thought out choices.

                      You can insert the number 4039487384765638465 into an int4 and you get 2147483647 as the number, no error, no fuss, no muss.

                      You can insert the number 1 into an enum, and it will take it, even if your enum was defined as being 'a','b','c', because 1 corresponds to one of those fields as an int.

                      The list of ways MySQL can screw up your data goes on and on.

                      Finally, MySQL GPLd the connect libs in 4.x, so now either all your code is GPL, or you buy a commercial license. They try to sugar coat this and say that if you're writing free software you shouldn't mind, and if you're writing commercial software you owe something back to the community, but what they're really doing is picking your license for you.

                      Wanna write a BSD package against MySQL and distribute it? you can't, unless you use the commercial version of MySQL.

                      The reason it's so widely available is that in the past, it was the easiest database to set up and administer in a shared environment (back when postgresql had VERY course security setup, and required a bit more hand holding / dba work than it does today.) Plus in the past the connect libs were LGPLd, so you didn't have to worry about the viral nature of the licensing biting you.

                      Just because it's the most popular doesn't make it the best, and vice versa. There's things I like MySQL for (mostly read content management systems are a good fit) but for serious database work where your data's integrity counts, look at the other two open source choices.

                      Speed is important, but it's not the most important thing. just ask the folks who sailed the Atlantic a long time ago in a fast ship called the Titanic.

                        If you're gonna do Postgresql, I recommend a couple of things:

                        1: Get Data and Databases: Concepts in Practice by Joe Celko. Joe Celko is one man who can get a free pizza from me anytime he feels like stopping by. Plus, his picture on the back looks just like Anton Lavey.

                        2: Join the mailing lists pgsql-general, pgsql-sql and pgsql-admin. Postgresql has the best signal to noise ratio on its lists, and the most helpful people populating them

                        3: Read the docs that come with it, specifically the admin guide, the users guide and the tutorial. Especially the part in the admin guide about regular maintenance. There isn't that much to do, but it's an absolute necessity to do things like vacuum and analyze the database.

                        4: Go to OSCON and meet some of the core hackers. They're great guys, every one of them, and a hoot to party / hand out with.

                          The Church of Satan?

                            #postgresql on irc.freenode.org is also really, really good.

                              Write a Reply...