Pomelo.EntityFrameworkCore.MySql 1.0.1 milestone

[pomelofoundation]

Pomelo.EntityFrameworkCore.MySql 1.0.1 milestone

[caleblloyd]

GVCs work just fine in our library with no changes! I've committed an example model and test in #86

[caleblloyd]

JSON_CONTAINS in MySQL causes a full table scan. If the table is large, this query will be very slow. MySQL's recommended approach is to create "Generated Virtual Columns"

• MySQL Documentation for Generated Virtual Columns
• Good Article on Generated Virtual Columns

I don't know if we should expose JSON_CONTAINS queries through LINQ. People may use them thinking that it's a good idea, when in reality once their table grows they will have very slow queries.

@kagamine what are your thoughts? Should we nix JSON_CONTAINS for 1.1 and instead find a way to let people query Generated Virtual Columns?

[yukozh]

Wow, interesting. It seems the GVC is better.

[dnetguru]

<rant>
I personally think a JSON column type is not a great idea since it has a huge potential for being misused by the developer.

Think about it, any table can be stored in the following table by serializing the object representing the columns in the data column:

CREATE TABLE DataStore (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`data` JSON NULL
)

Now should we do this? Absolutely not! MySQL is not a document store and if the developer thinks they need something like this then a document database is what they need to look for!

I always suggest using the JSON column type as you would a TEXT column type that has CSVs in it OR as you would a BLOB! Never store data that you need to search through or select a subset of in it.

Sure the possibility of indexing JSON columns by extracting their values is good to have for interop and backwards compatibility reasons (e.g when you have some data in JSON format that you need to import and use) but otherwise I'd say skipping GVCs and going through the motions of defining a column for it would be a better idea since it will be faster and will not impose a read/write overhead (based on if the value is GENERATED or STORED).

Also note that GVCs are not an option in the most common use cases of these BLOB types like JSON anyways. Just like the one you have in the README file (the tags list).

[caleblloyd]

@dnetguru the JSON type is very useful. If for no other reason, the JSON is stored in an Optomized Format

I always suggest using the JSON column type as you would a TEXT column

That is a good suggestion. It should never be searched directly, as this will cause a full table scan.

skipping GVCs and going through the motions of defining a column for it would be a better idea since it will be faster and will not impose a read/write overhead

GVCs can be VIRTUAL or STORED.

VIRTUAL indexed columns will write to the index when created/updated, but will not write to storage. Since the JSON format is optimized, generating the value when a row is returned in a result set will be very fast. This also allows to cut down on storage space.

STORED indexed columns will write to the index and storage when created/updated. There's really no difference here - the data is just extracted to the secondary column on the DBMS instead of on the App Server. I'd go so far as to argue that the DBMS will do this faster because it's a highly optimized codepath.

[caleblloyd]

Congratulations to all of the contributors who worked on 1.0.1 and all of the testers who opened issues against 1.0.1! It's a great step forward and provides a very powerful tool to the modern .NET stack. I'm looking forward to using this in my apps and continuing to improve the library in 1.1.0!