Re: Inefficient query processing?
On Feb 10, 2008 5:30 PM, Yves Goergen [EMAIL PROTECTED] wrote: My problem is that the sub-select in line 7 (SELECT 1) takes a rather long time. (When I remove it, it's much faster.) This is a known issue with EXISTS/NOT EXISTS subqueries in MySQL, which has some fixes slated for MySQL 6. In the meantime, there are ways to rewrite most of these queries using JOIN/LEFT JOIN. Read some of the articles on http://xaprb.com/ about subqueries for a more detailed explanation and examples of rewrites. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 19:51 CE(S)T, Perrin Harkins wrote: On Feb 10, 2008 5:30 PM, Yves Goergen [EMAIL PROTECTED] wrote: My problem is that the sub-select in line 7 (SELECT 1) takes a rather long time. (When I remove it, it's much faster.) This is a known issue with EXISTS/NOT EXISTS subqueries in MySQL, which has some fixes slated for MySQL 6. In the meantime, there are ways to rewrite most of these queries using JOIN/LEFT JOIN. Read some of the articles on http://xaprb.com/ about subqueries for a more detailed explanation and examples of rewrites. Thank you for the link. Is there some way to get only the headlines and a summary for all entries? Reading through the entire contents by month and finding the misleading captions is hard work for such masses of content. The search function didn't give me the desired results. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query two different databases for differences
I have two different databases on the same 5.0 server that have the same tables and structure. They also have very nearly the same data. For one of the tables I'd like to run a query to find the records that are present in one database but not the other. Is this possible and what would such a query look like? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
Yves Is there some way to get only the headlines ... For brief discussion some examples see 'The [Not] Exists query pattern' at http://www.artfulsoftware.com/infotree/queries.php. PB Yves Goergen wrote: On 11.02.2008 19:51 CE(S)T, Perrin Harkins wrote: On Feb 10, 2008 5:30 PM, Yves Goergen [EMAIL PROTECTED] wrote: My problem is that the sub-select in line 7 (SELECT 1) takes a rather long time. (When I remove it, it's much faster.) This is a known issue with EXISTS/NOT EXISTS subqueries in MySQL, which has some fixes slated for MySQL 6. In the meantime, there are ways to rewrite most of these queries using JOIN/LEFT JOIN. Read some of the articles on http://xaprb.com/ about subqueries for a more detailed explanation and examples of rewrites. Thank you for the link. Is there some way to get only the headlines and a summary for all entries? Reading through the entire contents by month and finding the misleading captions is hard work for such masses of content. The search function didn't give me the desired results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On Feb 11, 2008 4:46 PM, Yves Goergen [EMAIL PROTECTED] wrote: Thank you for the link. Is there some way to get only the headlines and a summary for all entries? Reading through the entire contents by month and finding the misleading captions is hard work for such masses of content. The search function didn't give me the desired results. I use Google: http://www.google.com/search?q=site%3Axaprb.com+%22NOT+EXISTS%22 - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
Yves If user.additionalkeylist and tag.readaccesskeylist are not lists, naming them `...list` misleads distracts. You asked earlier how to fit my preliminary solution into your problem. The answer is to (i) write the query that lists access-denied messages, then (ii) write a simple exclusion join from messages to that derived table. But on (i), how user.additionalkeylist and tag.readaccesskeylist work remains confusing. You appear to say access may come from ... (i) message-message_revision-message_revision_tag.readaccesskeylist, or (ii) message_revision-user.additionalkeylist which implies there are positive values which provide access, but your original query used the condition readaccesskeylist /is not null/ as a test for access /refusal/, which seems to contradict what you now say. PB - Yves Goergen wrote: On 11.02.2008 17:32 CE(S)T, Peter Brawley wrote: 1. user.additionalkeylist and tag.readaccesskeylist are atomic despite their names? Yes, I forgot the types. Everything is scalar, varchar or integer. There are not set or otherwise complex data types. 2. You have reciprocal foreign keys, keylist.key referencing user(userID) and user.additionalkeylist referencing keylist.keylistID? Basically, yes. Although there is a contraint in my application that is not visible in the database structure: I distinguish between personal and virtual keys. Personal keys must not be part of a user's additional keys list. Virtual keys must not have an additional keys list on their own. (And they must not have logon information.) So there cannot be a cyclic reference. This is documented in the source code and will be enforced on the application layer later.
Re: Inefficient query processing?
Yves, Okay. Then the long form. 1. user.additionalkeylist and tag.readaccesskeylist are atomic despite their names? 2. You have reciprocal foreign keys, keylist.key referencing user(userID) and user.additionalkeylist referencing keylist.keylistID? PB Yves Goergen wrote: On 11.02.2008 11:18 CE(S)T, Peter Brawley wrote: Unclear. Okay. Then the long form. My application is a messaging application that supports multiple users, messages with revisions, tags and access control. A user is identified by a UserId which I also call key. (Imagine it like the key you have for your front door.) A user can be assigned additional keys that give him way to additional messages. (Imagine it like the key for your office and the neighour's.) A user is also the author of a message revision. A keylist supports storing multiple keys for another entity. This table is only a product of database normalisation. It is identified by a KeylistId. Tags are predefined, i.e. an author cannot use tags that are not already there. Tags are identified by their TagId. Tags can also be used to restrict access to messages that have them assigned. For this, a tag can have a list of keys that can be used to access the message. If a keylist is assigned (ReadAccessKeylist IS NOT NULL), then any key from the list will do. E.g. you can restrict access on a certain tag to the sales and development departments, locking out research dept. and anonymous guests. If no key list is defined (ReadAccessKeylist IS NULL), then no access restrictions are imposed on that tag. A message contains multiple revisions (can be one, too) that keep the actual data, the message content, subject, timestamp etc. A message has a MessageId, a revision is identified by a MessageId and a RevisionNumber, both starting at 1. A message can have its own individual access keylist that behaves the same was as for tags, but it only applies to the single respective message. A message revision can be assigned tags that are used for classification and access control. These tag associations are stored in the message_revision_tag table. For search purposes, a single message revision is linked to that will be regarded. This is the SearchRevision of a message that is determined by other conditions and stored persistently. This is the relevant part of the database schema: message (MessageId) message_revision (MessageId references message, RevisionNumber, Author references user(UserId), CreatedTime, ...) keylist (KeylistId, Key references user(UserId)) user (UserId, AdditionalKeylist references keylist(KeylistId)) tag (TagId, ReadAccessKeylist references keylist(KeylistId)) message_revision_tag (MessageId, RevisionNumber, TagId references tag) One additional constraint: message_revision_tag (MessageId, RevisionNumber) reference to message_revision The main search query only retrieves MessageIds. It must only return messages that the current session user has access to. This access may come from the messages' ReadAccessKeylist or any of the assigned tags' ReadAccessKeylist. The session user can be granted access for his own personal key (UserId) or one of the additional keys in his keyring (AdditionalKeylist). If there are multiple access lists, the user must pass all of them to get the message. I.e. if a message has an individual keylist and also some tags assigned that restrict access, the user must be in each of those key lists to get access. This is probably the most tricky part to understand. Please tell me if you have questions. The entire SQL query is now: SELECT m.MessageId FROM message m WHERE -- Grant all access to administrators (so that they can alter the access key lists) (:isAdmin OR -- Message has no read access keylist (m.ReadAccessKeylist IS NULL OR EXISTS -- Message's read access keylist contains personal or additional user key (SELECT 1 FROM message m2, keylist m2k WHERE m2k.KeylistId = m2.ReadAccessKeylist AND m2.MessageId = m.MessageId AND (m2k.UserId = :sessionUserId OR m2k.UserId IN (SELECT uk.UserId FROM user u, keylist uk WHERE uk.KeylistId = u.AdditionalKeylist AND u.UserId = :sessionUserId AND NOT EXISTS -- There must not be any tag that denies access (SELECT EXISTS -- Tag's read access keylist contains personal or additional user key (SELECT 1 FROM keylist tk WHERE tk.KeylistId = t.ReadAccessKeylist AND (tk.UserId = :sessionUserId OR tk.UserId IN (SELECT uk.UserId FROM user u, keylist uk WHERE uk.KeylistId = u.AdditionalKeylist AND u.UserId = :sessionUserId))) AS Allowed FROM message_revision_tag mrt JOIN tag t USING (TagId) WHERE mrt.MessageId = m.MessageId AND mrt.RevisionNumber =
MySQL University session on February 14
Hi, this Thursday, Iggy Galarza will give a MySQL University session: http://forge.mysql.com/wiki/Building_MySQL_on_Windows Please register for this session by filling in your name on the session Wiki page. Registering is not required but appreciated. That Wiki page also contains a section to post questions. Please use it! Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. Next MySQL University sessions: February 21: Checking Memory With Valgrind (Stewart Smith) February 28: New Optimizer Features in MySQL 5.2 (Sergey Petrunia) March 6: How to Add a Collation (Alexander Barkov) March 13: Checking Threading and Locing With Helgrind (Stewart Smith) -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query two different databases for differences
James;
This finds common rows.
Eh!? ... HAVING COUNT(*)=1 returns ONLY pairs that are different:
drop table if exists a,b;
create table a(i int,j int,k int);
insert into a values(1,10,100),(2,20,200),(3,30,300);
create table b select * from a;
update b set k=301 where k=300;
select * from a;
+--+--+--+
| i| j| k|
+--+--+--+
|1 | 10 | 100 |
|2 | 20 | 200 |
|3 | 30 | 300 |
+--+--+--+
select * from b;
+--+--+--+
| i| j| k|
+--+--+--+
|1 | 10 | 100 |
|2 | 20 | 200 |
|3 | 30 | 301 |
+--+--+--+
SELECT
MIN(TableName) as TableName, i,j,k
FROM (
SELECT 'Table a' as TableName, a.i, a.j, a.k
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.i, b.j, b.k
FROM b
) AS tmp
GROUP BY i,j,k
HAVING COUNT(*) = 1
ORDER BY i;
+---+--+--+--+
| TableName | i| j| k|
+---+--+--+--+
| Table a |3 | 30 | 300 |
| Table b |3 | 30 | 301 |
+---+--+--+--+
PB
-
James Eaton wrote:
From: Peter Brawley
I'd like to run a query to find the records that
are present in one database but not the other.
See 'Compare data in two tables' at
http://www.artfulsoftware.com/infotree/queries.php.
Thanks. That's a start.
SELECT
MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;
This finds common rows. That doesn't help when the tables have about
20,000 rows and most are the same. How do I invert the query so that
I can find the 'uncommon' rows? Second, the primary key ('id' in the
example) values do not match, so how/where in the query can you
specify how to match rows from the two tables using other columns?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 20:13 CE(S)T, Peter Brawley wrote: If user.additionalkeylist and tag.readaccesskeylist are not lists, naming them `...list` misleads distracts. Well, these fields contain KeylistId values from the keylist table, so I thought naming them *Keylist would be good enough. But on (i), how user.additionalkeylist and tag.readaccesskeylist work remains confusing. You appear to say access may come from ... (i) message-message_revision-message_revision_tag.readaccesskeylist, or (ii) message_revision-user.additionalkeylist which implies there are positive values which provide access, but your original query used the condition readaccesskeylist /is not null/ as a test for access /refusal/, which seems to contradict what you now say. message.ReadAccessKeylist and message.SearchRevision- message_revision_tag.ReadAccessKeylist are a list of keys of which *one* is required to get in. user.AdditionalKeylist is a list of keys that the user possesses and of which *one* can be used to get in. One list contains the keys that can be used, the other two lists contain keys that are allowed. One gives keys, the other accept keys. Imagine it like the user coming along with a keyring, trying to open a door with multiple keyholes. One of his keys must fit in one of the keyholes to get in. And the problem here is that I need to test whether there is not a single tag for a (known) revision of a message that has an associated keylist to which no keys of the session user fits. If there was such a tag, access would be denied. To grant access, there must only be tags that either have ReadAccessKeylist IS NULL or that contain a key to which one of the session user's additional keys fits. I'm still wondering if there's a way to explain all this better with some graphics. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5 on OS X Leopard
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I found this a while back and it seems to work just fine. It uses launchd rather than the old /Library/StartupItems folder http://blog.tomatocheese.com/archives/2007/11/1/migrating_mysql_to_mac_os_x_leopard/ Regards, Grant Limberg [EMAIL PROTECTED] On Feb 11, 2008, at 6:34 PM, Unnsse Khan wrote: Hello there, I am having problems running MySQL 5 on OS X Leopard... Nothing happens when I try to start MySQL in System Preferences. From the command line, this is the error I receive (when trying to start MySQL): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) On the MySQL 5 downloads site, it doesn't have a version for Leopard. Are there any workarounds for getting MySQL 5 running on Leopard? Happy programming, Unnsse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFHsSBWIAfARU4EW68RAkO2AJ4r6qedXM6zwuzVwsSNuoEnbuJbDQCeJdGT XC/14Qt+oiJtidm1u9p63Iw= =TzER -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 17:32 CE(S)T, Peter Brawley wrote: 1. user.additionalkeylist and tag.readaccesskeylist are atomic despite their names? Yes, I forgot the types. Everything is scalar, varchar or integer. There are not set or otherwise complex data types. 2. You have reciprocal foreign keys, keylist.key referencing user(userID) and user.additionalkeylist referencing keylist.keylistID? Basically, yes. Although there is a contraint in my application that is not visible in the database structure: I distinguish between personal and virtual keys. Personal keys must not be part of a user's additional keys list. Virtual keys must not have an additional keys list on their own. (And they must not have logon information.) So there cannot be a cyclic reference. This is documented in the source code and will be enforced on the application layer later. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
view irregularities
I'm running into a difficult to reproduce problem with a view which is similar to the following: CREATE TABLE Common ( COMMON_ID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, COMMON_NAME VARCHAR(50), UNIQUE(COMMON_NAME) ) ENGINE = InnoDB; CREATE TABLE Parent ( PARENT_ID VARCHAR(50) NOT NULL, PARENT_NAME VARCHAR(50) NOT NULL, PARENT_COMMON_ID INT UNSIGNED, PRIMARY KEY (PARENT_ID, PARENT_COMMON_ID), FOREIGN KEY (PARENT_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; CREATE TABLE Child ( CHILD_IDBINARY(20) NOT NULL PRIMARY KEY, PARENT_ID VARCHAR(50) NOT NULL, CHILD_NAME VARCHAR(50), CHILD_COMMON_ID INT UNSIGNED, FOREIGN KEY (PARENT_ID) REFERENCES Parent(PARENT_ID), FOREIGN KEY (CHILD_COMMON_ID) REFERENCES Common(COMMON_ID) ) ENGINE = InnoDB; DROP VIEW IF EXISTS BrokenView; CREATE VIEW BrokenView AS SELECT Child.* FROM Child LEFT JOIN Parent USING(PARENT_ID) WHERE Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID; DROP VIEW IF EXISTS WorkingView; CREATE VIEW WorkingView AS SELECT Child.*, Parent.PARENT_NAME, Parent.PARENT_COMMON_ID FROM Child LEFT JOIN Parent ON (Child.PARENT_ID = Parent.PARENT_ID AND Child.CHILD_COMMON_ID = Parent.PARENT_COMMON_ID); Though the example cited above does not cause the problems that I'm running into, the table structure is similar. Specifically the fact that I'm doing a WHERE ... in the BrokenView vs WorkingView is seemingly the difference between getting rows returned and not. After my program inserts data into all three tables, I can do a query on the base tables, however doing a query on the view with a WHERE on the primary key of the Child table returns no rows. Doing that same query on the base table returns rows as expected. Performing a 'flush table Parent' per the example above fixes the problem. Additionally, using the 'temptable' algorithm works, whereas explicitly using the 'merge' algorithm, or the 'undefined' default causes the problem to appear. All tables are InnoDB, 'select version()' returns '5.0.50-enterprise- gpl-log'. Having read the VIEW command documentation, it wouldn't seem that I'm doing anything against the rules in my query that would cause this. Any help would be greatly appreciated! thanks, -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding duplicate key
Something like this should help you find all of the dupes select email_address from table group by email_address having count(*)1; On Feb 11, 2008 4:23 PM, Ferindo Middleton [EMAIL PROTECTED] wrote: I have a table (customers) without a primary key. I want to make the email_address field the primary key, only problem is, several records already share the same email_address How do I write a query which will show me all the instances where email_address is duplicated throughout the table. -- Ferindo -- Grant Limberg [EMAIL PROTECTED]
Re: Query two different databases for differences
From: Peter Brawley
I'd like to run a query to find the records that
are present in one database but not the other.
See 'Compare data in two tables' at
http://www.artfulsoftware.com/infotree/queries.php.
Thanks. That's a start.
SELECT
MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;
This finds common rows. That doesn't help when the tables have about
20,000 rows and most are the same. How do I invert the query so that I
can find the 'uncommon' rows? Second, the primary key ('id' in the
example) values do not match, so how/where in the query can you specify
how to match rows from the two tables using other columns?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
finding duplicate key
I have a table (customers) without a primary key. I want to make the email_address field the primary key, only problem is, several records already share the same email_address How do I write a query which will show me all the instances where email_address is duplicated throughout the table. -- Ferindo
Re: Inefficient query processing?
On 11.02.2008 00:29 CE(S)T, Peter Brawley wrote: message (messageID) keylist (keylistID) tag ( tagID, readaccesskeylist references keylist(keylistID) ) message_revision_tag ( ???, messageID references message(messageID), tagID references tag(tagID)) Another table: message_revision(MessageId references message, RevisionNumber) Correction: message_revision_tag(MessageId, RevisionNumber, TagId) (MessageId, RevisionNumber) references message_revision (i) Finding messages which have a deny-access tag looks like a simple join: SELECT DISTINCT messageID FROM message_revision_tag AS mrt JOIN tag AS t ON mrt.tagID=t.tagID WHERE t.readaccesskeylist IS NOT NULL; (ii) We get the messages not in the above result with a simple exclusion join: SELECT messageID FROM message m LEFT JOIN ( SELECT DISTINCT messageID FROM message_revision_tag AS mrt JOIN tag AS t ON mrt.tagID=t.tagID WHERE t.readaccesskeylist IS NOT NULL ) AS banned USING (messageID) WHERE banned.messageID IS NULL, Or did I miss something? I'm afraid I cannot integrate this in my large query. It looks too simple and I don't know where to put its parts. Maybe I'll really have to show the full schema and the complete query... It's not only that my entire query will find messages that have no tag with a ReadAccessKeylist assigned; it will rather find messages that have no tag with a keylist which does not include the currently logged in user's UserId or one of this user's additional keys, which are again stored in a keylist. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 on OS X Leopard
Hello there, I am having problems running MySQL 5 on OS X Leopard... Nothing happens when I try to start MySQL in System Preferences. From the command line, this is the error I receive (when trying to start MySQL): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) On the MySQL 5 downloads site, it doesn't have a version for Leopard. Are there any workarounds for getting MySQL 5 running on Leopard? Happy programming, Unnsse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query two different databases for differences
On Feb 11, 2008 7:27 PM, James Eaton [EMAIL PROTECTED] wrote:
SELECT
MIN(TableName) as TableName, id, col1, col2, col3, ...
FROM (
SELECT 'Table a' as TableName, a.id, a.col1, a.col2, a.col3, ...
FROM a
UNION ALL
SELECT 'Table b' as TableName, b.id, b.col1, b.col2, b.col3, ...
FROM b
) AS tmp
GROUP BY id, col1, col2, col3, ...
HAVING COUNT(*) = 1
ORDER BY ID;
This finds common rows. That doesn't help when the tables have about
20,000 rows and most are the same. How do I invert the query so that I
can find the 'uncommon' rows? Second, the primary key ('id' in the
example) values do not match, so how/where in the query can you specify
how to match rows from the two tables using other columns?
SELECT A.message_number
FROM message_table A
LEFT JOIN delivery_table B
ON A.message_number = B.message_number
WHERE B.message_number is NULL
AND A.deleted = 0;
Will select every message_number that appears in A but not B and A's deleted
field equals 0.
--
We are all slave to our own paradigm. -- Joshua Williams
If the letters PhD appear after a person's name, that person will remain
outdoors even after it's started raining. -- Jeff Kay
Send INSERT statement from MS SQL SERVER to MySQL
Hi, Is it possible to send an INSERT statement from a Windows server running MS SQL SERVER 2005 to a Linux box running MySQL ? If so, how ? Do I need any special tools ? Any help would be appreciated. Warm Regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
On 11.02.2008 11:18 CE(S)T, Peter Brawley wrote: Unclear. Okay. Then the long form. My application is a messaging application that supports multiple users, messages with revisions, tags and access control. A user is identified by a UserId which I also call key. (Imagine it like the key you have for your front door.) A user can be assigned additional keys that give him way to additional messages. (Imagine it like the key for your office and the neighour's.) A user is also the author of a message revision. A keylist supports storing multiple keys for another entity. This table is only a product of database normalisation. It is identified by a KeylistId. Tags are predefined, i.e. an author cannot use tags that are not already there. Tags are identified by their TagId. Tags can also be used to restrict access to messages that have them assigned. For this, a tag can have a list of keys that can be used to access the message. If a keylist is assigned (ReadAccessKeylist IS NOT NULL), then any key from the list will do. E.g. you can restrict access on a certain tag to the sales and development departments, locking out research dept. and anonymous guests. If no key list is defined (ReadAccessKeylist IS NULL), then no access restrictions are imposed on that tag. A message contains multiple revisions (can be one, too) that keep the actual data, the message content, subject, timestamp etc. A message has a MessageId, a revision is identified by a MessageId and a RevisionNumber, both starting at 1. A message can have its own individual access keylist that behaves the same was as for tags, but it only applies to the single respective message. A message revision can be assigned tags that are used for classification and access control. These tag associations are stored in the message_revision_tag table. For search purposes, a single message revision is linked to that will be regarded. This is the SearchRevision of a message that is determined by other conditions and stored persistently. This is the relevant part of the database schema: message (MessageId) message_revision (MessageId references message, RevisionNumber, Author references user(UserId), CreatedTime, ...) keylist (KeylistId, Key references user(UserId)) user (UserId, AdditionalKeylist references keylist(KeylistId)) tag (TagId, ReadAccessKeylist references keylist(KeylistId)) message_revision_tag (MessageId, RevisionNumber, TagId references tag) One additional constraint: message_revision_tag (MessageId, RevisionNumber) reference to message_revision The main search query only retrieves MessageIds. It must only return messages that the current session user has access to. This access may come from the messages' ReadAccessKeylist or any of the assigned tags' ReadAccessKeylist. The session user can be granted access for his own personal key (UserId) or one of the additional keys in his keyring (AdditionalKeylist). If there are multiple access lists, the user must pass all of them to get the message. I.e. if a message has an individual keylist and also some tags assigned that restrict access, the user must be in each of those key lists to get access. This is probably the most tricky part to understand. Please tell me if you have questions. The entire SQL query is now: SELECT m.MessageId FROM message m WHERE -- Grant all access to administrators (so that they can alter the access key lists) (:isAdmin OR -- Message has no read access keylist (m.ReadAccessKeylist IS NULL OR EXISTS -- Message's read access keylist contains personal or additional user key (SELECT 1 FROM message m2, keylist m2k WHERE m2k.KeylistId = m2.ReadAccessKeylist AND m2.MessageId = m.MessageId AND (m2k.UserId = :sessionUserId OR m2k.UserId IN (SELECT uk.UserId FROM user u, keylist uk WHERE uk.KeylistId = u.AdditionalKeylist AND u.UserId = :sessionUserId AND NOT EXISTS -- There must not be any tag that denies access (SELECT EXISTS -- Tag's read access keylist contains personal or additional user key (SELECT 1 FROM keylist tk WHERE tk.KeylistId = t.ReadAccessKeylist AND (tk.UserId = :sessionUserId OR tk.UserId IN (SELECT uk.UserId FROM user u, keylist uk WHERE uk.KeylistId = u.AdditionalKeylist AND u.UserId = :sessionUserId))) AS Allowed FROM message_revision_tag mrt JOIN tag t USING (TagId) WHERE mrt.MessageId = m.MessageId AND mrt.RevisionNumber = m.SearchRevision AND t.ReadAccessKeylist IS NOT NULL -- Only regard tags with read access keylist (only they can fail the access test) HAVING NOT Allowed) :isAdmin is 0 or 1 depending on whether the session user is an administrator. Admins always get the result
Re: stored procedure, parameter type help needed
Jerry Schwartz wrote:
SELECT user_id, user_name FROM user_test WHERE user_id IN (SELECT
uid
FROM temp_uids);
[JS] Couldn't you replace the WHERE user_id IN (SELECT uid FROM
temp_uids)
with a simple JOIN? If IN is badly optimized, as I've read here, wouldn't
that be more efficient? Or am I (as usual) missing something?
I may be the one missing something :-). I have tried to think out of the
box and use other solutions, but haven't come up with a way that works
without using IN.
The query are to be used in a calendar-ish application, for finding when
people are free to attend meetings. The ids I send as parameter is the
ids of users that I want to check availability for. The id-list may
contain between 1 and 50 user ids.
Maybe I could use
JOIN ... ON (user_id=1 OR user_id=2 OR user_id=5...) (?)
but as far as I understand, I'd still have to generate this list as a
string because I do not know how many users to check for, and then
CONCAT the query, PREPARE etc. as described in Peter Brawley's email.
Then I think I prefere using IN.
As for the optimization of IN, I've read the following in the manual:
The search for the item then is done using a binary search. This means
IN is very quick if the IN value list consists entirely of constants.
(http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in;)
, so if I am to rely on the doc, it should be very quick the way I use it.
Thanks for your comment Jerry.
Magne
DROP PREPARE stmt;
END; ::
DELIMITER ;
CALL get_users('(2), (3)');
---
MW
Peter Brawley wrote:
Hi Magne
...the query I actually want to use this in, is a 100 line query
with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to
assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I
wanted
to make
the request fast, and the concatenating and string handling takes
some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about
creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I
wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single
quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query two different databases for differences
James I'd like to run a query to find the records that are present in one database but not the other. See 'Compare data in two tables' at http://www.artfulsoftware.com/infotree/queries.php. PB James Eaton wrote: I have two different databases on the same 5.0 server that have the same tables and structure. They also have very nearly the same data. For one of the tables I'd like to run a query to find the records that are present in one database but not the other. Is this possible and what would such a query look like? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inefficient query processing?
Yves it will rather find messages that have no tag with a keylist which does not include the currently logged in user's UserId or one of this user's additional keys, which are again stored in a keylist. Unclear. PB Yves Goergen wrote: On 11.02.2008 00:29 CE(S)T, Peter Brawley wrote: message (messageID) keylist (keylistID) tag ( tagID, readaccesskeylist references keylist(keylistID) ) message_revision_tag ( ???, messageID references message(messageID), tagID references tag(tagID)) Another table: message_revision(MessageId references message, RevisionNumber) Correction: message_revision_tag(MessageId, RevisionNumber, TagId) (MessageId, RevisionNumber) references message_revision (i) Finding messages which have a deny-access tag looks like a simple join: SELECT DISTINCT messageID FROM message_revision_tag AS mrt JOIN tag AS t ON mrt.tagID=t.tagID WHERE t.readaccesskeylist IS NOT NULL; (ii) We get the messages not in the above result with a simple exclusion join: SELECT messageID FROM message m LEFT JOIN ( SELECT DISTINCT messageID FROM message_revision_tag AS mrt JOIN tag AS t ON mrt.tagID=t.tagID WHERE t.readaccesskeylist IS NOT NULL ) AS banned USING (messageID) WHERE banned.messageID IS NULL, Or did I miss something? I'm afraid I cannot integrate this in my large query. It looks too simple and I don't know where to put its parts. Maybe I'll really have to show the full schema and the complete query... It's not only that my entire query will find messages that have no tag with a ReadAccessKeylist assigned; it will rather find messages that have no tag with a keylist which does not include the currently logged in user's UserId or one of this user's additional keys, which are again stored in a keylist. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
