Slashdot Mirror

Here is a Database Journal article from 2010 listing some useful MySQL GUI development tools that may provide some leads -- http://www.databasejournal.com...

It sounds like someone missed an ON clause in a join statement and got a cartesian. The most interesting way to join things is the correlated subquery. It's very powerful when implementing weird business logic.

You don't have to hire me (or anyone) to do those things. If you're a one man shop, and perfectly comfortable doing your installs yourself, no one "should" get paid-you've every right to do it yourself. On the other hand, if you're a large corporation, chances are someone's going to get hired to do installation, maintenance, and customization.

Exactly.

Case in point, one of my good friends just left us (Rackspace) to found a company called Riptano that sells support for Cassandra (there's a story in Database Journal here).

They are committed (John and Matt) to building bits on top of Cassandra for support / management / etc, but also contributing in a big way to the project its self and not forking it.

I don't have the stones to strike out on my own like that, but MAJOR props to them - this is what the F/OSS community needs more of. It takes people with an abundance of passion about a technology to make something like this a reality. Plus, who knows, they may really turn this into a big thing if Cassandra takes off and people want nosql solutions.

(plug: http://www.riptano.com/)

The solution to this is to use a table-valued function instead of a scalar and then CROSS APPLY it to the rest of your data set:

http://www.databasejournal.com/features/mssql/article.php/3845381/T-SQL-Best-Practices--Dont-Use-Scalar-Value-Functions-in-Column-List-or-WHERE-Clauses.htm

Or, as stated, just use a view. Scalar UDFs are good for setting SQL variables based on today's date or a customer ID one-time; not much else. Indeed, a shame.

Since you're using Oracle, there some ways to prevent access to other parts of the schema and prevent run-on queries from going nuts.

Create a user for them, specifically for these ad-hoc queries. This user should have no disk quotas on any tablespace, since they're not writing any data. This user should only have the 'Create session' privilege.

Second, if your customer data is all in big_honkin_schema, create some views for them. Create the views in another schema and grant them select only.

Third, if you're REALLY worried about them "touching your precious tables"[1] then create them materialized views instead. Might have to adjust disk quotas of course.

You can also set up user profiles to limit resource consumption ( number of sessions, maximum CPU time, et. al. ).

[1] Since when did Gollum become a DBA? ;-)

What have you been smoking ? Lots and lots of people have complained about MySQL for a long time,
e.g.: http://sql-info.de/mysql/gotchas.html

Here is someone who likes mysql trying to explain why automagically inserting default values for
columns that are supposed to have the constraint NOT NULL is a good idea versus the insert failing:
http://www.databasejournal.com/features/mysql/arti cle.php/3519116

The only thing mysql has going for it is that PHP integrates so nicely with it and for many web apps
thats great but for anything else there are far better choices.

I would inform you that you need some means of doing the query that supports a cursor

Try to avoid using SQL Server cursors whenever possible. Of course, maybe using MS SQL is a design error ;) Regardless of what SQL server I would use, as your client I'm sure you'd make a pretty penny helping me optimize my connection pool to work out how many seconds I should hold cursors open before I decide that the user has surfed away from my website and won't be needing the query anymore. We might even discover that 90% of my users don't even press "next page" and that I'm establishing all these cursors for nothing, when all I really needed was the first 10 results, in whatever language it takes to get them.

Wow. A quick Google search says you are in fact correct.

http://www.databasejournal.com/features/mysql/arti cle.php/2248101

http://www.databasejournal.com/features/mssql/arti cle.php/2204421
http://www.perfectxml.com/articles/xml/openxml.asp
http://www.microsoft.com/technet/technetmag/issues /2005/05/SQLXML/default.aspx

Also, the default transaction mode is autocommit, automatic commiting after each statement. So if you don't either start a transaction or set autocommit=0 you're going to see automatic per-statement commits.

See Transactions in MySQL and transaction isolation levels for more.

It's been this way since before MySQL 4.0, though using "type" instead of "engine" in versions 4.0 and below.

Also, the default transaction mode is autocommit, automatic commiting after each statement. So if you don't either start a transaction or set autocommit=0 you're going to see automatic per-statement commits.

See Transactions in MySQL and transaction isolation levels for more.

It's been this way since before MySQL 4.0, though using "type" instead of "engine" in versions 4.0 and below.

So you do have constant inserts, but you are basically just using the database as a cache. In that case it really wouldn't matter at all if you had to go back to last night's backup, as material would just be cached again as queries came in. So MySQL would look like a good fit there.

It's difficult to find up-to-date comparisons as the latest versions of both (SQL Server 2005 and MySQL 5) are quite new, but here's an example comparing MySQL 4.1 with SQL Server 2000. MySQL 5 is meant to be a lot better than 4; SQL Server 2005 is also better but it is more of an incremental thing over 2000 compared to the advances with MySQL.

You should also consider SQL Server Express, which is free. Just bear in mind the limitations: 1 CPU, 1gb RAM, 4gb database size (data files, not including logs.) Other than this I believe it has the performance of the full version (e.g. it would be as fast as the full version running on a 1 CPU machine with 1gb RAM.)

To be honest if you are planning on just a single machine which is going to share the web server, application server and database the Express edition will likely be more than enough. The 1 CPU/1gb RAM limitation is what SQL Server is limited to use; if you have 2 CPUs/2gb RAM the rest of the system will still be using these.

The one to watch is the 4gb max database size; you would want to be a good bit below this (half?) to allow for expansion - and your application sounds like something that would potentially produce a very large amount of data to store (although maybe not as I presume you need to flush stuff out regularly to keep the info fresh.) Also, the 4gb is per database; you can have as many as you like.

You'll also be more familiar with SQL Server administration though, so staying with that might be easier than trying to migrate to MySQL now. What I would probably do myself would be to stick with SQL Server (if the free Express edition is enough) for _this_ move (you will have other issues with the move rather than trying to complicate it) and then when you have expanded to the point where you need a seperate database machine (which is very much to be recommended anyway) look into MySQL on that then. You can set up MySQL on that new machine independently while your app runs happily on the other, and only switch over when you are sure everything is working OK. MySQL (v5+, which many would think the first version that is a real DBMS) will have been around for a bit longer then as well which can only be an advantage.

To be honest performance is likely to depend a lot more on your database design, good SQL (e.g. thinking in sets, reducing queries per ASP page), proper use of indexes, appropriate caching, etc. You can speed up a badly implemented database by several orders of magnitude looking at these things, whereas either actual engine will be in the same ballpark. I'd say either DBMS will do in that regard.

I see, you're a SQL purist. Not that there's anything wrong with that. It's just that I come at things from a bit of a different perspective.

I'm not an SQL purist, I'm a data integrity purist. Did you read the "Gotchas" site I linked to? MySQL silently inserted an empty value for a NOT NULL field. There are an alarming number of cases where data is truncated silently, zeros are entered silently, and NULL fields leads to weird assumptions on MySQL's part--all silently. I like my databases to let me know when they're making assumptions or doing anything automatically so I can respond to it in code.

Here's another article--MySQL Oddities.

I do have one follow up question for you. You mention that you don't like having to get the PK after an insert into an auto_increment field, but I still didn't see any reason why this system doesn't work. Do you have an example where it's necessary to get the PK before the insert? Or is this just a matter of preference?

Not only do I like to be able to associate user-uploaded files with content they may be editing but haven't yet published and submitted to the database, but MySQL's auto_increment leads to situations where 0 equals 1. MySQL just ignores what you give it sometimes without warning and does its own thing. I can't trust that, not for what I do.

Now, sure, I could walk around it all and conform my code to compensate, but I think it's the job of the database to be protecting its integrity, not me.

I see, you're a SQL purist. Not that there's anything wrong with that. It's just that I come at things from a bit of a different perspective.

I'm not an SQL purist, I'm a data integrity purist. Did you read the "Gotchas" site I linked to? MySQL silently inserted an empty value for a NOT NULL field. There are an alarming number of cases where data is truncated silently, zeros are entered silently, and NULL fields leads to weird assumptions on MySQL's part--all silently. I like my databases to let me know when they're making assumptions or doing anything automatically so I can respond to it in code.

Here's another article--MySQL Oddities.

I do have one follow up question for you. You mention that you don't like having to get the PK after an insert into an auto_increment field, but I still didn't see any reason why this system doesn't work. Do you have an example where it's necessary to get the PK before the insert? Or is this just a matter of preference?

Not only do I like to be able to associate user-uploaded files with content they may be editing but haven't yet published and submitted to the database, but MySQL's auto_increment leads to situations where 0 equals 1. MySQL just ignores what you give it sometimes without warning and does its own thing. I can't trust that, not for what I do.

Now, sure, I could walk around it all and conform my code to compensate, but I think it's the job of the database to be protecting its integrity, not me.

Maybe you just need to simma down now!

Fuck you, I don't have to waste my time on this shit so stop acting like a dick. If you are "genuinely curious about the technical differences between the two DBs" then great, start from reading these articles:

PostgreSQL vs MySQL: Which is better? by Ian Gilfillan
MySQL Gotchas by Ian Barwick
MySQL and PostgreSQL Wikipedia articles

Things you should know before you start reading the above articles, to fully understand what they talk about, and indeed before starting to use relational databases, to avoid common mistakes like confusing objects with tuples et cetera:

Relational model, transaction processing,
ACID, atomicity, consistency, isolation, durability,
relational algebra, predicate calculus, set theory.

Those Wikipedia articles are a very good start if you really want to know what databases are all about. First of all you have to understand that RDBMS is not an object store. This is the most common mistake. What you get from a database are not objects, but tuples. They don't have an identity and they are not real things that exist, but an information about your data. See this thread for wonderful explanation.

You must have some minimum knowledge about the set theory, predicate calculus and relational algebra to understand it, but once you do, you will have a much better understanding about relational databases and your data, including the importance of ACID features. It is really worth to invest a little time now ro learn the theory and save a lot of time in the future thanks to better understanding those concepts which can be confusing at first, for they don't map into the standard OOP model and standard data models very well. Good luck.

I am looking forward to hear from you if reading those articles was helpful. This is the standard introductory material that I recommend to my students, and I'd like to hear an opinion of someone who is not scared that some criticism will cause him troubles. By the way, sorry for my English.

PostgreSQL vs MySQL: Which is better?

And here is a 3-point summary for the impatient:

MySQL versus Postgresql (summary)

• Database Journal has an article comparing SQL Server 2000 and Access 2000, but the feature list shouldn't have changed much. The charts that you are interested in are at the bottom right before the conclusion.
• Here's a speed comparison between MySQL and Access.
• Here's a great comparison of many SQL technologies including Access.