> overview text generated always as (`json_column` -> '$.overview'), > imdb_rating decimal(5,2) generated always as (`json_column` -> '$.imdb_rating'), > title varchar(255) generated always as (`json_column` -> '$.title'), > imdb_id varchar(255) generated always as (`json_column` -> '$.imdb_id'), > ai_myid int AUTO_INCREMENT primary key, Mysql> create table movies_json_generated ( Then I am going to load the data via insert from our already loaded movies table. Let’s do this! I am going to create a table with generated columns for various columns. In the case of JSON, we can pull values out of our document and store them read-only in a column that can be indexed (we all know indexing is good, right!). Note, you can also use functional indexes, which I will cover later. Generated columns allow you to create columns based on other columns or circumstances on the fly. (These are because of the null values in the rating column (but these are not impacting performance). Before moving on, notice the warnings? Let’s ignore these for one moment. In this case, you can speed up things drastically with a few important and small improvements. A better cache solves it all, right? Well, not really. Yes, you can put a cache in front of these and probably will. Any website you are working on nowadays can not succeed if all your database queries take 0.5-1 second to return. So we solved the issue with not having enough space to sort the dataset, but did you notice the time? Almost 1 second. | Monrad & Rislund: Det store triumftog (Video 2004) | 9.8 | tt0425266 | Mysql> select json_column->'$.title' as title, json_column->'$.imdb_rating' as rating, json_column->'$.imdb_id' as imdb_id from movies_json where json_column->'$.imdb_rating' > 8 order by json_column->'$.imdb_rating' desc limit 10 Mysql> set OK, 0 rows affected (0.00 sec)
0 kommentar(er)
