Choice of database

[Anon]

Hi guys,

I am trying to figure what is the best environment for an application we are developing but am having a hard time. the whole problem is that if its a UNIX environment, we have more or less two choices -- Oracle or MySQL... Oracle is too big of an overhead and will kill the future owners with the hostin gprice tag. MySQL on the other hand is not good enough for the application, which entails lots of FREETEXT search in about 100,000 to 300,000 records. SQL server is somewhere in the middle and this is why, albeit our unwillingness, it seems we have to use the MS platform, not for anything else but for its database as MS SQL server gives better performance than MySQL but is much less expensive than Oracle in terms of hosting.

noetheless, I would be very interested in getting some feedback as to the availability of a database that works on UNIX and fits in the middle (has triggers, stored procs, foreign keys, etc..) and has some way of indexing the words in large chunks ot text for faster searches (you can do that in both MS SQL server and Oracle). does mysql have freetext search indexing?

and another question to all you gurus -- what is the biggest MySQL DB that you have deployed abd it still hasn't gone bust... cause I am inclined to use MySQL for this project but I am afraid it will go bust if I try a freetext search on 50k records.

Thanks for your input. Please feel free to write to my e-mail if you feel more comfortable, but you can all be sure that your input will be invaluable to me.

Greetings,

Vassil

[clapton]

There is an alternative for you to choose
try PostgreSql (it's has the stored procedure , i class it between Oracle & Mysql , there is also DB2 but it's expensive)

My biggest database :
30 Tables x 800 records (postgresql)

perhapes another one will help u more

good luck

          Clapton.

[[deleted]]

1st, the fact that MS-SQL runs on windows does not mean that your entire app has to be windows based. There is nothing stopping you from using a un*x webserver with a windows database server.

Second, there is PostgreSQL which runs on un*x and windows, supports just about everything you want, and is completely free.

As for the biggest MySQL db, that's up to how big the largest possible filesize on your OS is.

Oh, and MySQL is crappy, but it will handle the freetext search on a mere 50k rows just fine, provided your system has enough resources. But that is a must for any datatabase.

MySQL's biggest problem is the lack of foreign-keys, stored procedures and transactions.

[Anon]

MySQL 4.1 might have the features you want..

From my research, I noticed 4.1 (to be released by years end supposedly) will have stored procedures, nested queries, full-text searches, etc .. check out the following for additional information:

http://www.eweek.com/article/0,3658,s%253D701%2526a%253D15968,00.asp

http://www.mysql.com/doc/T/O/TODO_MySQL_4.0.html

John

[Anon]

I know that I can have a mixed environment but that is ...errr, not the best combination as not a single host is willing to allow for such combinations. at least I don't know of one, but would appreciate it if you give me alead. this mixed UNIX/WIn environment will also require a JDBC/ODBC bridge (if written in UNIX, the application will be with Java servlets; this is not negotiable unfortunately🙁) which ultimately affects performance

Can you tell me more about PostGre SQL? I asked a friend of mine what he thinks of it and he said that it is about the same as MySQL with a few additions (the ones you mentioned) but with less people that use it and more bugs... two very different opinions🙂)

now, another thing. we just finished some tests and MySQL could not handle gracefully a Search with several LIKEs in a 20,000 record DB...so it seems good ole MySQL can't handle the load...🙁

p.s. thanks for the input

[[deleted]]

Two cool quotes:
"Even if the bureau needed more Oracle-like features, such as advanced transaction processing, another open-source database such as the University of California at Berkeley's PostgreSQL would be considered first, Taylor said."

MySQL still have a long way to go...

and

"The downside [of mysql] is that it doesn't have all the features Oracle has, but 99 percent of people don't need them," said Zawodny, in Sunnyvale, Calif."

giggle 99% of people don't need them. No, 99% of people never saw them (because MySQL doesn't have them) and thus they don't know how to use them. Once you have them and know how to use them, you start using them and wonder how you ever got by without them...

Still, I am looking forward to MySQL 4.1, maybe it will actually turn out to be something good :-)

[[deleted]]

"errr, not the best combination as not a single host is willing to allow for such combinations"

Then you have shitty hosts :-)

"if written in UNIX, the application will be with Java servlets; this is not negotiable unfortunately🙁)"

Uhm.. ya... java servelets.. what's this doing in a PHP forum then? :-)

Servlets.. yuk.. are they trying to get rid of all performance no matter what? 🙂

"Can you tell me more about PostGre SQL?"
http://www.ca.postgresql.org/features.html

MySQL 'lovers' hate postgreSQL, and postgreSQL 'lovers' hate MySQL.
Both have their use, but PostgreSQL supports much more if the SQL standard. It was the basis for Informix (market leader in corporate databases).
And as for bugs, well that's the default argument against software if you don't have any real arguments. 🙂

"MySQL could not handle gracefully a Search with several LIKEs in a 20,000 record DB"

whoa there, generalization alert!
First of all, how do you define 'gracefull',
and second, what kind of query did you run, on what kind of machine, and onw what kind of data?

LIKE's are not 'gracefull' on any database.
Especially the LIKE '%something%'. That always gives a sequencial scan, even on oracle.

[Anon]

If it were up to us we really would use PHP...🙁 and I came to this forum accidentally but you are providing me quite a lot of guidance

will tell you all the details tomorrow about the Seaarches, machine, etc.. getting late today🙁

[gid__]

I have a medium sized postgres db, 39472 records, I just changed a page to do a LIKE %var% query, and the first query happened in 0.160574 seconds on a p3-600 (slow ide hd). And indexed varchar field got returned in 0.011808.

We currently have a client with probably well over a million records in a mysql db (3.23.x, myISAM tables), and 331 meg on disk. The site runs smooth as silk, although it's not very high traffic yet.
I've done test between the 2, mysql is easily twice as fast for the things I did (even under heavy load with updates, selects with even mutliple table joins, this database that I test against was quite small though, probably around 550 records.

Postgres definitely has more features and is very solid, and hey, you can't beat the price, start using postgres now, and then upgrade to something that costs money later on if need be, that's always been my philosophy.

[Anon]

You might want to look into other databases like Interbase from Borland (now free and has a long proven track record and all the bell and whistles) or Frontbase (free version and paying versions), which has advanced things like clustering etc... yet is rather cheap compared to Oracle or DB2, and also has a PHP module that mirror the one from MySQL (making porting easy and fast)