SQL Server vs MySQL – Filtering Statement Explained

MySQLsql serversql-server-2016

My company is in the midst of an IT overhaul and considering a shift away from MS SQL Server (2016 Standard) to a MySQL database (version 5.6.10). I have used MS SQL Server for years, so I am apprehensive to make this move. Basically – our IT team is under the impression that MySQL is free and comes with only a few syntax changes.

I have spent the day reading up on the differences and honestly finding that they seem to be very similar. If I am going to make a case for MS SQL Server – then now is the time to do it. One key detail – we are an analytical company and our SQL queries are often complex – so I need to make sure that our RDBMS of the future is up to par.

I have encountered the following statement multiple times (about filtering) and I need a translation. My interpretation of this is that it is not possible to use a single query to join tables from differing databases. However I have seen youtube videos where people do just that in MySQL. Please help me understand this difference and whether it is important or not.

MySQL allow users to filter out tables, rows, and users in a number of ways. But it requires users to filter out the tables, rows, or users by individual databases. While filtering the data, the developers have to filter database tables individually by running multiple queries. On the other hand, SQL Server enables developers to take advantage of row-based filtering. The row-based filtering option filters data on a database by database way. Also, the filtered data is stored in a separate distribution database. Hence, it becomes easier for programmers to filter multiple rows without considering the number of databases.

– A Comparison between MySQL vs. MS SQL Server

Greatly appreciate your help with deciphering the quoted statement as well as any Pro/Cons you see for this move. Thanks!

Best Answer

"Very few syntax changes" -- Excuse my while I laugh! SQL Server has a lot of features that MySQL does not have. Any use of such is not just a syntax change, but possibly a query rewrite.

TOP 10 --> LIMIT 10 -- but only approximately.

ROW_NUMBER --> does not exist

SEQUENCE --> AUTO_INCREMENT -- but only approximately

dbname.dbo.tablename --> dbname.tablename

Which version of MySQL? MySQL 8.0 has a good chance of having some of the fancier features being used in SQL Server. MariaDB 10.4 is likely to come even closer to SQL Server, in that it handles some of the incompatibilities that MySQL does not cover.

JOINing across databases on the same instance of MySQL has never been a problem:

SELECT a.this, b.that
    FROM db1.tblx AS a
    JOIN db2.tbly AS b
    WHERE a.foo = 1
      AND b.blat < 5;

JOINing across two separate instances (of MySQL or MariaDB) on the same server, or separate instances on separate servers is a problem. See MariaDB's "Connect". The quote you provided leaves the term "database" ambiguous. (Or it was pulled out of context.) I hope that I have made it clear with the addition of the term "instance".

As for the rest of that link. It is aimed at CEOs, not us grunts who will have to make the transition. Too much 'hand-waving' and fluff; no meat.

I have not figured out what it means by "row-based filtering". Maybe it is a snazzy feature in SQL Server. But maybe it is not being used by your code.

Start a new Question and post in it "how do I change this query to MySQL" together with a non-trivial, but typical query. You may see some of the 4 incompatibilities I mentioned above, or there may be other things waiting to trip up the conversion.

Related Solutions

Sql-server – Indexing a PK GUID in SQL Server 2012

The primary problems with GUIDs, especially non-sequential ones, are:

Size of the key (16 bytes vs. 4 bytes for an INT): This means you're storing 4 times the amount of data in your key along with that additional space for any indexes if this is your clustered index.
Index fragmentation: It is virtually impossible to keep a non-sequential GUID column defragmented because of the completely random nature of the key values.

So what does this mean to your situation? It comes down to your design. If your system is simply about writes and you have no concern about data retrieval, then the approach outlined by Thomas K is accurate. However, you have to keep in mind that by pursuing this strategy, you're creating many potential issues for reading that data and storing it. As Jon Seigel points out, you will also be occupying more space and essentially having memory bloat.

The primary question around GUIDs is how necessary they are. Developers like them because they ensure global uniqueness, but it's a rare occasion that this kind of uniqueness is necessary. But consider that if your maximum number of values is less than 2,147,483,647 (the maximum value of a 4 byte signed integer), then you're probably not using the appropriate data type for your key. Even by using BIGINT (8 bytes), your max value is 9,223,372,036,854,775,807. This is typically enough for any non-global database (and many global ones) if you need some auto-incrementing value for a unique key.

Finally, as far as using a heap versus a clustered index, if you are purely writing data a heap would be most efficient because you are minimizing overhead for inserts. However, heaps in SQL Server are extremely inefficient for data retrieval. My experience has been that a clustered index is always desirable if you have the opportunity to declare one. I have seen the addition of a clustered index to a table (4 billion+ records) improve overall select performance by a factor of 6.

Additional information:

Mysql – Why isn’t this index helping the InnoDB MySQL query

This is just a guess, as I do not have all info, but you probably would be better by doing:

EXPLAIN SELECT STRAIGHT_JOIN
    *
FROM
    tusers PARTITION (p362) tu
    JOIN users PARTITION (p362) u
      ON u.group_id=tu.group_id 
      AND tu.email_address=u.email
      AND tu.group_id = 362 
WHERE
    tu.application_id=253555;

Note the STRAIGHT_JOIN, that may not be needed -if it is needed, then I may have assumed wrongly- and the tu.group_id comparison (that, again, shouldn't be needed).

Then using the following keys:

(tu.application_id, tu.group_id, tu.email_address)
(u.group_id, u.email)

However, if the number of records to be returned is 2.5M, as your cardinality suggests, then do not expect this to be fast... this is a pure IO math.

There are many other things that clicks me as problems, but I cannot say for sure without access.

Those could be even more effective if you didn't do a SELECT *.

Another thing is that varchar(255) is usually a bad idea.

Best Answer

Related Solutions

Sql-server – Indexing a PK GUID in SQL Server 2012

Mysql – Why isn’t this index helping the InnoDB MySQL query

Related Question