MySQL Performance – Select from JSON Encoded Data

MySQLperformancequery-performance

I have saved the details of the product in JSON format.
I am wondering how to select from a list of values from a "JSON_ENCODED DATA"

For Example
I want to select TV with screen_type as LED or LCD.

And the value is saved as

{"screen_size":"22 - 24", "screen_type":"LED", "smart_tv":"no", "3d":"no", "resolution":"HD Ready", "hdmi":"1", "usb":"1"}

It is possible to do with UNION query like:

SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LED"%'  
UNION  
SELECT * FROM `nw_object_detail` WHERE `details` LIKE '%"screen_type":"LCD"%'

But the query will be too lengthy if there are lot of choices.
Is there are any solution in a simpler way.

Best Answer

I got a solution:

Single check of value in JSON array

SELECT * FROM `nw_object_detail` 
WHERE ((`details` like '%"screen_type":"%LED%"%'))

One value from multiple possibilities

SELECT * FROM `nw_object_detail`
WHERE ((`details` like '%"screen_type":[%"%LCD%"%]%')

PHP Solution

if(count($options)>1): 
    $condition = '(';
    $count = 1;
    foreach( $options as $option ): 
        $condition .= '(`details` like \'%"'.$key.'":[%"%'.$option.'%"%]%\')';
        if(count($options)!=$count): 
            $condition .= ' OR ';
        endif;
    endforeach;
$condition .= ')';

Related Solutions

MySQL slow select from a view

There are a few things going on here:

your CREATE VIEW statement contains a SUM() function. Even though your ALGORITHM is specified as MERGE, it is really going to be TEMPTABLE as discussed in the documentation here:

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs: Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
So we're using temporary tables. I suspect you're not seeing 'blocking' due to this benefit of TEMPTABLE algorithm:

A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.
However, the problem with the TEMPTABLE algorithm is that they do not have/use indexes [src]:

View processing is not optimized:

It is not possible to create an index on a view.

Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

So now we need to know the underlying structure of your table as to why the direct query blocks:

If it's not InnoDB, consider switching to use row-level locks instead of table-locks of MyISAM
If there's no index on (number, bet_amount) consider creating one.

If the above points have already been done, and you still for some reason have blocking on a query directly against the salestable table, then you can institute a poor-man's materialized view by creating triggers that modify a temporary table with each number and a running total.

MySQL – Tools and Methods to Investigate Slow Query

Why the query is slow:

explain's output shows the following:

The query will scan ALL rows from first table, 78536406, and for each of these rows, it is will scan an average of 15 rows from the second table. In total, (78536406*15) rows will be read. This is by itself is an indicator of slowness.

Taking into consideration the size of the tables, there might be some memory swap, which cause slowness as well (Reading from disk to RAM, repeatedly.)

How to improve this query:

One possible way would be to create a table out of the sub-query, and use INNER JOIN between the first table, and the table from the previous step.

CREATE TABLE tmp_ve as select min(VendorEventID) as VendorEventID from Vendor_Events where ProdID in (598446938, 598446984, 598447024, 598447054, 598447311, 598447523, 598447764, 598447778, 598448000, 598448048) and vendorid = 12 group by ProdID;
-- No need to add indexes to this table, as it would contain only 10 rows.
SELECT Vendor_Events.* FROM Vendor_Events INNER JOIN tmp_ve USING (VendorEventID);

I didn't try it, but if you explain this second query, it will start by the tmp_ve table, then for each value, it will use the index on Vendor_Events to fetch the corresponding row

HTH

Best Answer

Related Solutions

MySQL slow select from a view

MySQL – Tools and Methods to Investigate Slow Query

Related Question