subquery for where in
Hi All, I am trying to find the top 5 ee_entry id's, and then return all rows matching any of the 5 ee_entry id's. I think I am close, but the following sql is currently only returning rows matching the first ee_entry in the group_concat. If I run the subquery separately and manually create the sql replacing the subquery with it's result, I get the intended result (all rows matching any of the 5 ee_entry). SELECT dt,resource,resource_title,ee_entry,ee_author,ee_cat,ee_comment FROM mint_visit WHERE ee_entry IN ( SELECT GROUP_CONCAT( ee_entry ) FROM ( SELECT ee_entry FROM mint_visit WHERE ee_entry 0 GROUP BY ee_entry ORDER BY COUNT( * ) DESC LIMIT 5 ) AS t1 ) ORDER BY ee_entry DESC , dt ASC I also tried: SELECT dt, resource, resource_title, ee_entry, ee_author, ee_cat, ee_comment FROM mint_visit WHERE ee_entry IN ( SELECT ee_entry FROM mint_visit WHERE ee_entry 0 GROUP BY ee_entry ORDER BY COUNT( * ) DESC LIMIT 5 ) ORDER BY ee_entry DESC , dt ASC But I received an error: #1235 - This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' Thanks for your help, Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: IN vs. OR on performance
2009/3/29 Oscar ro4...@gmail.com: Hi all- I want to know what the difference between IN and OR is under the hood. select * from dummy_table where id in (2, 3, 4, 5, 6, 7); select * from dummy_table where id=2 or id=3 or id=4 or id=5 or id=6 or id=7; I've have thought once the query is compiled, they are the same. What might cause a difference in performance is doing id 2 and id = 7. Test it on a large dataset and let us know :) -- Blog: http://pookey.co.uk/blog Follow me on twitter: http://twitter.com/ipchristian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Search based where claused and stored proc
An approach that has worked for me in the past and may work for you. Declare as many parms as you can possibly need and then code the statement like this: sql SELECT * FROM someWhere WHERE @parm1 = 123 OR @parm1 IS NULL AND @parm2 = 345 OR @parm2 IS NULL -- etc. This has the desired effect and eliminates the need for a temp table or actual table (inno or otherwise). The trick here is to pass the maximum number of parms, even if they are Nulls. HTH, Arthur
RE: IN vs. OR on performance
basically the same criteria as IN vs EXISTS from http://decipherinfosys.wordpress.com/2007/01/30/in-vs-exists/ select select column list from TABLE_A where col1 in (Select col2 from TABLE_B) VS Select select column list from TABLE_A where exists (select 1 from Table_B where Table_B.col2 = Table_A.col1) where should one use an IN vs the EXISTS clause? EXISTS works better when: If the result of the sub-query “Select col2 from TABLE_B” is huge (rows in table_b rows in table_a) AND main table TABLE_A is a relatively small set (table_a rows table_b rows) AND executing “select 1 from Table_B where Table_B.col2 = Table_A.col1″ is very fast because of proper index on Table_B.col2, then an exists clause will be better since the optimizer can do a FTS on main table Table_A and then use the index to do the probe/seek operations for Table_B. (essentially a giant Nested Loop) IN works better when: If the result of the sub-query (table_b) is small, then the IN clause is much faster. (the results from the subquery is so small it can be inlined into the main query) http://decipherinfosys.wordpress.com/2007/01/30/in-vs-exists/ HTH Martin __ Disclaimer and confidentiality note This message is confidential and may be privileged. If you are not the intended recipient, we kindly ask you to please inform the sender. Any unauthorised dissemination or copying hereof is prohibited. This message serves for information purposes only and shall not have any legally binding effect. Given that e-mails can easily be subject to manipulation, we can not accept any liability for the content provided. Date: Sun, 29 Mar 2009 09:13:10 + Subject: Re: IN vs. OR on performance From: poo...@pookey.co.uk To: ro4...@gmail.com CC: mysql@lists.mysql.com 2009/3/29 Oscar ro4...@gmail.com: Hi all- I want to know what the difference between IN and OR is under the hood. select * from dummy_table where id in (2, 3, 4, 5, 6, 7); select * from dummy_table where id=2 or id=3 or id=4 or id=5 or id=6 or id=7; I've have thought once the query is compiled, they are the same. What might cause a difference in performance is doing id 2 and id = 7. Test it on a large dataset and let us know :) -- Blog: http://pookey.co.uk/blog Follow me on twitter: http://twitter.com/ipchristian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail® is up to 70% faster. Now good news travels really fast. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_70faster_032009
Re: IN vs. OR on performance
An explain of the two statements yields the same plan, anybody knows if they are actually translated in the same plan? Claudio Ian P. Christian wrote: 2009/3/29 Oscar ro4...@gmail.com: Hi all- I want to know what the difference between IN and OR is under the hood. select * from dummy_table where id in (2, 3, 4, 5, 6, 7); select * from dummy_table where id=2 or id=3 or id=4 or id=5 or id=6 or id=7; I've have thought once the query is compiled, they are the same. What might cause a difference in performance is doing id 2 and id = 7. Test it on a large dataset and let us know :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Need a Brief Overview - SSL Connections
Hi Seth, I implemented SSL successfully just a couple of weeks ago on 5.1.30, and I too found some aspects confusing. Here's my answers from my own experience so please forgive me if they're inaccurate. 1) On the server side, I believe ssl-ca, ssl-cert and ssl-key are all required to establish the server's identity. On the client side, I believe a user can still login without encryption/SSL unless REQUIRE SSL is set on their account. From what I can tell, a client can also login using SSL with just ssl-ca (the server's certification authority certificate.) To verify that the client is who they say they are, then you set REQUIRE X509 on their account, and the client has to connect using ssl-ca, ssl-cert and ssl-key with a valid certificate and private key. 2) I also don't understand all concepts of SSL - I'm used to just providing a certificate request to a trusted partner (e.g. Verisign) and for them to send me back my certificate. However I presume that the trusted CA certificates (e.g. Verisign, Thawte etc) are present on all browsers/e-mail clients and that's why we don't need to worry about it for these types of applications. I believe that MySQL is allowing you to effectively issue your own certificates, and as such the client needs the CA certificate to verify the server's issuing authority (yourself.) The first step the MySQL manual takes you through is creating a private key and a certificate which will become your CA certificate (and is the file needed for ssl-ca/ssl-capath.) This is a different private key and certificate to both the server's and the client's (unless your server/client is the same box, then they can share the same certificate/key but will be different from the CA cert/key.) 3) I followed the instructions in the manual to the letter and I had no issues whatsoever. In my client (Navicat) I do get an SSL connection error - invalid parameter if one of the certificates are missing, but that may be coming from Navicat not the MySQL library. It'll be interesting to see what happens in some situations, for example, what happens when the client certificate or the CA certificate expires? Interestingly I can also still connect using SSL when the CA certificate is invalid (ie. non-existent or a private key file instead of a certificate file.) Hope this helps some! I do agree it wasn't the clearest of things to get my head around. Andy Seth Willits wrote: Howdy, I've read through all of the MySQL docs on SSL and I just need a brief overview of a few things to understand some things that aren't crystal clear to me since I'm not very familiar with SSL. 1) Which SSL options are *required*? It seems that only ssl-key is _always_ required for the client to connect to the server. If REQUIRE X509 is set, then ssl-cert is required as well in order to authenticate who the actual client is, right? 2) The options I don't understand are ssl-ca/ssl-capath. Why would the client specify a certificate authority? Is this the authority (or authorities) that's used to authenticate the server's certificate? Is there a platform default for this value? I'm not used to having to specify a list of authorities for other programs to validate certificates (such as with email). 3) How does I know if the server/client authentication (validating the certificate against given authorities) failed? Do I just get a vague SSL connection error back from MySQL and that's it? I think that's mostly it. Thanks, -- Seth Willits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
