Hello Daevid,
On 6/11/2013 7:17 PM, Daevid Vincent wrote:
-----Original Message-----
From: shawn green [mailto:shawn.l.gr...@oracle.com]
Sent: Tuesday, June 11, 2013 2:16 PM
To: mysql@lists.mysql.com
Subject: Re: How do I select all rows of table that have some rows in
another table (AND, not OR)
Hello Daevid,
... snip ...
Shawn, thank you for taking the time to reply. I wasn't expecting the
solution to be so much "work" with multiple statements like that. I was
thinking it could be done in one (or two, as in split out a portion of it in
PHP and re-insert it to the original SQL to avoid a JOIN or something). Part
of the issue is that we use PHP to generate the $sql string by appending
bits and pieces depending on the search criteria thereby keeping the 'path'
through the SQL statement simple and relatively linear. To implement this
would require significant re-writing and/or special cases where we could
introduce errors or omissions in the future. The frustrating part is that
the REGEXP query we use now only takes about 2 seconds on my DEV VM (same
database as PROD), however when the RDBMS is loaded it then takes up to 30
seconds so in theory it's not even that inefficient given the # rows. We do
use memcached for the results, but since there are so many combinations a
user could choose, our hit ratio is not so great and therefore the cache
isn't doing us much good and this is why the RDBMS can get loaded up easily.
How can an "OR" be so simple using IN() but "AND" be so overly complex?
Seems that mysql should have another function for ALL() that works just like
IN() to handle this kind of scenario.
As I said, we could have used a single command but you would have not
been able to review the 'best' match scenario only those rows that were
'complete' matches.
And, those three commands can easily be encapsulated within a stored
procedure. Pass in two strings (one listing the values to find, one
listing the values to reject) and use PREPARED STATEMENTS within the
procedure to build the IN() lists within the first and second commands.
The value in the 'HAVING' clause in the last command (unless you use
the other option of reviewing the list of 'closest' matches) can be set
to the number of items in the "list of things to find" parameter to your
procedure.
I was demonstrating a principle you could use and not necessarily giving
you a full solution. Some tweaking may be required.
Also, by encapsulating what I wrote within a stored procedure, this
changes my 3 statements to a single call that you can easily configure
from your PHP application.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
