Re: Precedence in WHERE clauses.
Hi Michael,
Le 18/03/2014 20:28, Michael Dykman a écrit :
Also, as you currently have it, the expression DATE_SUB(NOW(),
INTERVAL 24 is going to be executed once for every single candidate
row. I would suggest you temporarily memoize that like so:
select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate @yesterday);
Indeed ! it can be a significant performance improvement ;).
Thanks for this .
Regards,
Christophe.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi Morgan, Harald, and all, Thanks for the answers. While reading your answers, I realize one more time this problem does not seem so obvious ... That's why I asked ;) . Beyond the specific case I mentioned, my goal is also to get a general discussion about theorical behaviour of MySQL while handling WHERE clauses. I don't have access to the application for now, but will try EXPLAIN as soon as I can . Regards, Christophe. Le 19/03/2014 17:12, Morgan Tocker a écrit : Hi Harald, you can call me Harald My apologies! the question was is there any performance impact and (DWProcessed / Lowest cardinality and indexed) so *yes* it does matter, i have seen MySQL more than once not using any index because a unlucky order of where clauses up to copy a 16 GB table in a table file instead using a existing primary key If you have experienced that the order of the where clause has changed which execution plans are possible, I suggest taking a look at optimizer trace: http://dev.mysql.com/doc/internals/en/optimizer-tracing.html This is more detailed than EXPLAIN and will show indexes being considered, and the reasons that they are excluded. The 1=1 example I gave will also show as eliminated in optimizer trace. - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Precedence in WHERE clauses.
Hi Christophe,
On 20.03.14 13:18, Christophe wrote:
Hi Michael,
Le 18/03/2014 20:28, Michael Dykman a écrit :
Also, as you currently have it, the expression DATE_SUB(NOW(),
INTERVAL 24 is going to be executed once for every single candidate
row. I would suggest you temporarily memoize that like so:
select into DATE_SUB(NOW(), INTERVAL 24 HOUR) INTO @yesterday ;
SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
('PENDING', 'ACCEPTED') AND SubscribeDate @yesterday);
Indeed ! it can be a significant performance improvement ;).
Thanks for this .
Actually, the optimizer is often capable of detecting constant expressions and
evaluating them only once, regardless of how many rows that are accessed in the
query.
Run EXPLAIN EXTENDED for the query, followed by SHOW WARNINGS.
If you see cache(your expression) in the output, it is a sign that the
optimizer detected that your expression is constant, so it will be evaluated
once and cached for subsequent uses.
Thanks,
Roy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Decode Json in MySQL query
Hello,
I would like to know if there is a way to decode the json string stored in
one of the fields as text without using triggers or stored procedures.
What I want to do is is within the query, I would like to get one row per
element within the json string.
For example: the json string is as follow:
[
{
name : Abc,
age : 20
},
{
name : Xyz,
age : 18
}
]
and after query, I want result as:
NameAge
Abc 20
Xyz 18
Would this be possible, I greatly appreciate any help regarding this
matter.
Many Thanks,
Sukhjinder
Re: Decode Json in MySQL query
Hi, you probably want to perform this conversion on your client. There are
JSON parser libraries available for Java, PHP and the like. Cheers, Karr
On Mar 20, 2014, at 11:35 AM, Sukhjinder K. Narula narula...@gmail.com wrote:
Hello,
I would like to know if there is a way to decode the json string stored in
one of the fields as text without using triggers or stored procedures.
What I want to do is is within the query, I would like to get one row per
element within the json string.
For example: the json string is as follow:
[
{
name : Abc,
age : 20
},
{
name : Xyz,
age : 18
}
]
and after query, I want result as:
NameAge
Abc 20
Xyz 18
Would this be possible, I greatly appreciate any help regarding this
matter.
Many Thanks,
Sukhjinder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Decode Json in MySQL query
Hi,
http://blog.ulf-wendel.de/2013/mysql-5-7-sql-functions-for-json-udf/
This is not the exact solution for you query, but might help you better if
you add the libraries.
*thanks,*
*-- *Kishore Kumar Vaishnav
On Thu, Mar 20, 2014 at 11:35 AM, Sukhjinder K. Narula
narula...@gmail.comwrote:
Hello,
I would like to know if there is a way to decode the json string stored in
one of the fields as text without using triggers or stored procedures.
What I want to do is is within the query, I would like to get one row per
element within the json string.
For example: the json string is as follow:
[
{
name : Abc,
age : 20
},
{
name : Xyz,
age : 18
}
]
and after query, I want result as:
NameAge
Abc 20
Xyz 18
Would this be possible, I greatly appreciate any help regarding this
matter.
Many Thanks,
Sukhjinder
Re: Decode Json in MySQL query
Short answer, no. There is nothing in MySQL to facilitate this. In
general, storing structured data as a blob (JSON, CSV, XML-fragment,
etc..) is an anti-pattern in a relational environment. There are
NoSQL solutions that provide the facility: Mongo comes to mind; there
are some others, I am sure.
On Thu, Mar 20, 2014 at 2:59 PM, Karr Abgarian a...@apple.com wrote:
Hi, you probably want to perform this conversion on your client. There are
JSON parser libraries available for Java, PHP and the like. Cheers, Karr
On Mar 20, 2014, at 11:35 AM, Sukhjinder K. Narula narula...@gmail.com
wrote:
Hello,
I would like to know if there is a way to decode the json string stored in
one of the fields as text without using triggers or stored procedures.
What I want to do is is within the query, I would like to get one row per
element within the json string.
For example: the json string is as follow:
[
{
name : Abc,
age : 20
},
{
name : Xyz,
age : 18
}
]
and after query, I want result as:
NameAge
Abc 20
Xyz 18
Would this be possible, I greatly appreciate any help regarding this
matter.
Many Thanks,
Sukhjinder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
