Query matching
I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query matching
From: Ed Curtis [mailto:[EMAIL PROTECTED] I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? This is untested, but I imagine you could do the following: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND changelog.orig_id IN ( pages.mls_1, pages.mls_2, pages.mls_3, pages.mls_4, pages.mls_5, pages.mls_6, pages.mls_7, pages.mls_8, pages.mls_9, pages.mls_10, pages.mls_11, pages.mls_12 ); -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query matching
Yes, I think the most straight forward way is to simply put in a series of grouped OR statements. See below. SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND ( changelog.orig_id = pages.mls_1 OR changelog.orig_id = pages.mls_2 OR changelog.orig_id = pages.mls_3 OR changelog.orig_id = pages.mls_4 OR changelog.orig_id = pages.mls_5 OR changelog.orig_id = pages.mls_6 OR changelog.orig_id = pages.mls_7 OR changelog.orig_id = pages.mls_8 OR changelog.orig_id = pages.mls_9 OR changelog.orig_id = pages.mls_10 OR changelog.orig_id = pages.mls_11 OR changelog.orig_id = pages.mls_12 ) John A. McCaskey -Original Message- From: Ed Curtis [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: Query matching I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query matching
Thanks, that seemed the sensible way to me as well. I just didn't know for sure if you could do that in a MySQL query for sure. Thanks, Ed On Fri, 6 Feb 2004, John McCaskey wrote: Yes, I think the most straight forward way is to simply put in a series of grouped OR statements. See below. SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND ( changelog.orig_id = pages.mls_1 OR changelog.orig_id = pages.mls_2 OR changelog.orig_id = pages.mls_3 OR changelog.orig_id = pages.mls_4 OR changelog.orig_id = pages.mls_5 OR changelog.orig_id = pages.mls_6 OR changelog.orig_id = pages.mls_7 OR changelog.orig_id = pages.mls_8 OR changelog.orig_id = pages.mls_9 OR changelog.orig_id = pages.mls_10 OR changelog.orig_id = pages.mls_11 OR changelog.orig_id = pages.mls_12 ) John A. McCaskey -Original Message- From: Ed Curtis [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: Query matching I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Query: matching criteria AND ordering by primary key
I have a table with a state field, and often I want to get only records matching those states, ordered. This simple query Select * from my_table where state='NJ' order by table_id DESC is relatively slow because of the order by clause (where there are a lot of matching records). I've tried everything I can think of to speed this up -- indexing state and ID together, extracting the records (just the table_id's) matching the state into a temporary table and then inner joining it back with the original table, etc. EXPLAIN shows that it has to scan through the entire result set to order it, but in the case where there are lots of matching records (and the records themselves are large, with text blobs), it's very slow. It's fast without the order by. Basically, I want a super-fast way to say Get me the most recently stored x records matching this criteria. Any suggestions on speeding this up? (Every day I look on mysql.com to see if 4.02 is out, because this issue will largely go away when query caching is available, and I don't want to implement that logic in my code now.) TIA, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow Query: matching criteria AND ordering by primary key
At 23:20 -0400 7/2/02, Tac wrote: I have a table with a state field, and often I want to get only records matching those states, ordered. This simple query Select * from my_table where state='NJ' order by table_id DESC is relatively slow because of the order by clause (where there are a lot of matching records). I've tried everything I can think of to speed this up -- indexing state and ID together, extracting the records (just the table_id's) matching the state into a temporary table and then inner joining it back with the original table, etc. EXPLAIN shows that it has to scan through the entire result set to order it, but in the case where there are lots of matching records (and the records themselves are large, with text blobs), it's very slow. It's fast without the order by. Basically, I want a super-fast way to say Get me the most recently stored x records matching this criteria. Any suggestions on speeding this up? (Every day I look on mysql.com to see if 4.02 is out, because this issue will largely go away when query caching is available, and I don't want to implement that logic in my code now.) You don't have to wait for 4.0.2. This particular issue is fixed in 4.0.0 (ORDER BY ... DESC can use indexes). TIA, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
