Re: Group by question
On 2011-1-16 20:22, Jørn Dahl-Stamnes wrote: Hello, I got a table that store information about which photo-albums that a client is viewing. I want to get the N last visited albums and use the query: mysql select album_id, updated_at, created_at from album_stats order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 51 | 2011-01-16 13:03:04 | 2011-01-16 13:03:04 | | 10 | 2011-01-16 12:20:39 | 2011-01-16 12:20:39 | |2 | 2011-01-16 12:20:35 | 2011-01-16 12:20:35 | | 81 | 2011-01-16 12:20:34 | 2011-01-16 12:20:34 | | 97 | 2011-01-16 11:25:03 | 2011-01-16 11:19:05 | | 81 | 2011-01-16 11:19:04 | 2011-01-16 11:19:04 | |2 | 2011-01-16 11:19:02 | 2011-01-16 11:19:02 | | 10 | 2011-01-16 11:18:58 | 2011-01-16 11:13:04 | +--+-+-+ 8 rows in set (0.09 sec) The problem is that album_id 81 is occuring two times in the list. So I thought I should add a group by in the query: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 278 | 2011-01-13 14:02:50 | 2011-01-13 14:02:00 | | 281 | 2011-01-11 16:41:16 | 2011-01-11 16:35:41 | | 276 | 2010-12-15 14:42:57 | 2010-12-15 14:42:57 | | 275 | 2010-12-15 14:42:48 | 2010-12-15 14:42:48 | | 269 | 2010-09-11 14:09:10 | 2010-09-11 14:09:10 | | 271 | 2010-09-11 14:02:27 | 2010-09-11 14:02:27 | | 273 | 2010-09-11 13:59:06 | 2010-09-11 13:59:06 | | 270 | 2010-09-11 13:57:25 | 2010-09-11 13:57:25 | +--+-+-+ 8 rows in set (0.23 sec) But the result is not what I expected. What have I missed? perhaps i think first you need to retrieve the max(updated_at) group by album_id sets select album_id, updated_at, created_at from album_stats where updated_at in (select max(updated_at) from album_stats group by album_id) limit 8; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can't read dir of '.' (errno: 13)
Hi!
Jerry Schwartz wrote:
[[...]]
[[...]]
[JS] Sorry, that still doesn't make sense to em. To authenticate the user,
mysqld needs to read the mysql database. That is also where the databases are
listed (in `mysql`.`db`). If the daemon can read `mysql`.`user`, why can't it
read `mysql`.`db`? It's a MyISAM database, so everything is in the same file.
Now I understand how your question was meant - sorry to explain a
completely different issue first.
About reading files: MyISAM is stored file-per-table, so the table
mysql.user is stored in the three files mysql/user.{frm,MYD,MYI};
table mysql.db is stored in other files which might have got different
permissions. (This would be a handling error, obviously.)
What am I missing? Does SHOW DATABASES do something other than pull the
database names out of `mysql`.`db`?
AIUI, there are operations for which the server wants to read the
directory, and show databases seems to be one of them.
For examples, see these bug reports:
http://bugs.mysql.com/bug.php?id=22615
http://bugs.mysql.com/bug.php?id=42676
http://bugs.mysql.com/bug.php?id=51399
Sorry - I would tell you more details, if I knew them for sure.
Now, consider a case like this:
drwx--x--x user-A group-B .
-rw-rw-rw- user-A group-B file
In this case, file would be readable (and even writable) for everybody
knowing the name, but a ls . would fail for everybody but user-A:
There is no read permission on the directory for anybody but the owner.
Regards,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com
ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Loading Unicode Data to mySQL
Hi, Currently we are trying to load Unicode data encoded in UTF-8 to mySQL but the data is getting corrupted during load. Loading is done through Informatica (ETL Tool) and data is properly extracted and interpreted by Informatica but still it is failing to load it in the correct format. When we are trying to insert the same data manually it is successful i.e. data is getting loaded to mySQL successfully. Also when loading is done to a flat file or Oracle then also loading is happening as usual. Given below are the details: Server: MySQL Version: 5.1.51-enterprise-gpl-pro-log mysql SHOW VARIABLES LIKE 'character_set%'; +--++ Variable_name| Value | +--++ | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | character_set_filesystem | binary | character_set_results| utf8 | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir | /usr/share/mysql/charsets/ +--++ Mysql SHOW VARIABLES LIKE 'collation%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_unicode_ci | collation_database | utf8_unicode_ci | collation_server | utf8_unicode_ci +--+---+ Table details: Column Data type: TEXT Collation: utf8 - utf8_unicode_ci Engine: InnoDB Column Collation: Table Default ODBC DRIVER: 6.0 Please suggest based on the above settings what we can do to load Unicode data. Best Regards Swagat This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
Re: Loading Unicode Data to mySQL
2011/1/20 swagat.le...@accenture.com: Hi, I lack on knowledge about Informatica software. But if you are really sure that the problem is not on source data and not on backend configuration, then it is just in the middle. :-) I will be more specific (at least, as far as I can be). In a MySQL, a charset is negotiated on connection. This character set can be different from the server's default. For example, PHP connector uses latin instead of utf8 by default unless specifically configured. Check your software/ODBC settings (if they are available) http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.html Charsets can be a mess if you have several layers and only one of them fails, but if you understand fully all of MySQL parameters is not so difficult. -- Jaime Crespo MySQL Java Instructor Software Developer Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
I am designing an application that make heavy usage of one-to-many tags for items. That is, each item can have multiple tags, and there are tens of tags (likely to grow to hundreds). Most operation on the database are expected to be searches for the items that have a particular tag. That is, users will search per tags, not per items. These are the ways that I've thought about storing the tags, some bad and some worse. If there is a better way I'd love to know. 1) Each item will get a row in a tags table, with a column for each tag. mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tag1 bool, tag2 bool, tagN bool ); With this approach I would be adding a new column every time a new category is added. This looks to me a good way given that users will be searching per tag and a simple SELECT item FROM tags WHERE tag1=true; is an easy, inexpensive query. This table will get very large, there will likely be literally thousands of items (there will exist more items than tags). 2) Store the applicable tags one per line in a text field in the items table. mysql CREATE TABLE items ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tags text, ); This looks like a bad idea, searching by tag will be a mess. 3) Store the tags in a table and add items to a text field. For instance: mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tagName VARCHAR(100), items text, ); This looks to be the best way from a MySQL data retrieval perspective, but I do not know how expensive it will be to then split the items in PHP. Furthermore, adding items to tags could get real expensive. Caveat: at some point in the future there may be added the ability to have a tag hierarchy. For instance, there could exist a tag restaurant that will get the subtags italian and french. I could fake this with any approach by having a table of existing tags with a parentTag field, so if I plan on having this table anyway would method 3 above be preferable? Note: this message is cross-posted to the MySQL and the PHP lists as I am really not sure where is the best place to do the logic. My apologies to those who receive the message twice. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
I'd exclude (1) because new tags require restructuring the table, (2) and (3) because they break a cardinal rule of design and will be a mess to query, leaving ... 4) Standard many-many bridge table: mysql CREATE TABLE items_tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, itemID int, tagID INT ); Will not require a major overhaul if you later turn categories into a tree. PB - On 1/20/2011 8:32 AM, Dotan Cohen wrote: I am designing an application that make heavy usage of one-to-many tags for items. That is, each item can have multiple tags, and there are tens of tags (likely to grow to hundreds). Most operation on the database are expected to be searches for the items that have a particular tag. That is, users will search per tags, not per items. These are the ways that I've thought about storing the tags, some bad and some worse. If there is a better way I'd love to know. 1) Each item will get a row in a tags table, with a column for each tag. mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tag1 bool, tag2 bool, tagN bool ); With this approach I would be adding a new column every time a new category is added. This looks to me a good way given that users will be searching per tag and a simple SELECT item FROM tags WHERE tag1=true; is an easy, inexpensive query. This table will get very large, there will likely be literally thousands of items (there will exist more items than tags). 2) Store the applicable tags one per line in a text field in the items table. mysql CREATE TABLE items ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tags text, ); This looks like a bad idea, searching by tag will be a mess. 3) Store the tags in a table and add items to a text field. For instance: mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tagName VARCHAR(100), items text, ); This looks to be the best way from a MySQL data retrieval perspective, but I do not know how expensive it will be to then split the items in PHP. Furthermore, adding items to tags could get real expensive. Caveat: at some point in the future there may be added the ability to have a tag hierarchy. For instance, there could exist a tag restaurant that will get the subtags italian and french. I could fake this with any approach by having a table of existing tags with a parentTag field, so if I plan on having this table anyway would method 3 above be preferable? Note: this message is cross-posted to the MySQL and the PHP lists as I am really not sure where is the best place to do the logic. My apologies to those who receive the message twice. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
I think the canonical way would be to have one table for your items, one table
for your tags, and one table for your tag assignments.
CREATE TABLE items (
item_id INT(11) AUTO-INCREMENT PRIMARY KEY,
item_name VARCHAR(100) NOT NULL KEY,
...
);
CREATE TABLE tags (
tag_id INT(11) AUTO-INCREMENT PRIMARY KEY,
tag_name VARCHAR(100) NOT NULL KEY,
...
);
CREATE TABLE item_tags (
item_id INT(11) NOT NULL KEY,
tag_id INT(11) NOT NULL KEY
);
This way you could do
SELECT item_id, item_name FROM
tags JOIN item_tags ON tags.tag_id = item_tags.tag_id
JOIN items ON item_tags.item_id = items.item_id
WHERE ...
;
to get all of the items with a particular tag, or
SELECT tag_id, tag_name FROM
items JOIN item_tags ON items.item_id = item_tags.item_id
JOIN tags ON item_tags.tag_id = tags.tag_id
WHERE ...
;
with equal ease and efficiency.
Using an ever-lengthening bitmap for the tag assignments is a trap for the
unwary. The path to perdition is lined with the bodies of those who believed
We'll never need more than x...
As for setting up a hierarchy, that's trickier. One way to handle that is to
work like libraries do: 10 is fiction, 10.05 is crime novels, 10.05.07 is
British authors, and so forth. Your `tags` table then looks like
CREATE TABLE tags (
tag_id INT(11) AUTO-INCREMENT PRIMARY KEY,
tag_name VARCHAR(100) NOT NULL KEY,
tag_number VARCHAR(100) NOT NULL KEY,
...
);
Then you can search for tags by
tag_number LIKE ('10.%') or
tag_number LIKE ('10.05%')
and so forth. This scheme is infinitely extendable. To get the entire
hierarchy, you simply
SELECT tag_number, tag_name FROM tags ORDER BY tag_number;
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
-Original Message-
From: Dotan Cohen [mailto:dotanco...@gmail.com]
Sent: Thursday, January 20, 2011 9:32 AM
To: mysql.; php-general.
Subject: Organisational question: surely someone has implemented many Boolean
values (tags) and a solution exist
I am designing an application that make heavy usage of one-to-many
tags for items. That is, each item can have multiple tags, and there
are tens of tags (likely to grow to hundreds). Most operation on the
database are expected to be searches for the items that have a
particular tag. That is, users will search per tags, not per items.
These are the ways that I've thought about storing the tags, some bad
and some worse. If there is a better way I'd love to know.
1) Each item will get a row in a tags table, with a column for each tag.
mysql CREATE TABLE tags (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
item VARCHAR(100),
tag1 bool,
tag2 bool,
tagN bool
);
With this approach I would be adding a new column every time a new
category is added. This looks to me a good way given that users will
be searching per tag and a simple SELECT item FROM tags WHERE
tag1=true; is an easy, inexpensive query. This table will get very
large, there will likely be literally thousands of items (there will
exist more items than tags).
2) Store the applicable tags one per line in a text field in the items table.
mysql CREATE TABLE items (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
item VARCHAR(100),
tags text,
);
This looks like a bad idea, searching by tag will be a mess.
3) Store the tags in a table and add items to a text field. For instance:
mysql CREATE TABLE tags (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
tagName VARCHAR(100),
items text,
);
This looks to be the best way from a MySQL data retrieval perspective,
but I do not know how expensive it will be to then split the items in
PHP. Furthermore, adding items to tags could get real expensive.
Caveat: at some point in the future there may be added the ability to
have a tag hierarchy. For instance, there could exist a tag
restaurant that will get the subtags italian and french. I could
fake this with any approach by having a table of existing tags with a
parentTag field, so if I plan on having this table anyway would
method 3 above be preferable?
Note: this message is cross-posted to the MySQL and the PHP lists as I
am really not sure where is the best place to do the logic. My
apologies to those who receive the message twice.
Thanks!
--
Dotan Cohen
http://gibberish.co.il
http://what-is-what.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Can't read dir of '.' (errno: 13)
-Original Message-
From: Joerg Bruehe [mailto:joerg.bru...@oracle.com]
Sent: Thursday, January 20, 2011 6:54 AM
To: Jerry Schwartz
Cc: mysql@lists.mysql.com
Subject: Re: Can't read dir of '.' (errno: 13)
Hi!
Jerry Schwartz wrote:
[[...]]
[[...]]
[JS] Sorry, that still doesn't make sense to em. To authenticate the user,
mysqld needs to read the mysql database. That is also where the databases
are
listed (in `mysql`.`db`). If the daemon can read `mysql`.`user`, why can't
it
read `mysql`.`db`? It's a MyISAM database, so everything is in the same
file.
Now I understand how your question was meant - sorry to explain a
completely different issue first.
[JS] No problem. To me, knowledge is never useless.
About reading files: MyISAM is stored file-per-table, so the table
mysql.user is stored in the three files mysql/user.{frm,MYD,MYI};
table mysql.db is stored in other files which might have got different
permissions. (This would be a handling error, obviously.)
[JS] Stranger things have happened. I could tell tales that would turn your
hair white (mine already is).
What am I missing? Does SHOW DATABASES do something other than pull the
database names out of `mysql`.`db`?
AIUI, there are operations for which the server wants to read the
directory, and show databases seems to be one of them.
For examples, see these bug reports:
http://bugs.mysql.com/bug.php?id=22615
http://bugs.mysql.com/bug.php?id=42676
http://bugs.mysql.com/bug.php?id=51399
Sorry - I would tell you more details, if I knew them for sure.
[JS] It never occurred to me that SHOW DATABASE would scan the file directory.
Johan De Meersman came up with the same explanation.
Now, consider a case like this:
drwx--x--x user-A group-B .
-rw-rw-rw- user-A group-B file
In this case, file would be readable (and even writable) for everybody
knowing the name, but a ls . would fail for everybody but user-A:
There is no read permission on the directory for anybody but the owner.
[JS] Got it.
Regards,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com
ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On 20 January 2011 14:32, Dotan Cohen dotanco...@gmail.com wrote: I am designing an application that make heavy usage of one-to-many tags for items. That is, each item can have multiple tags, and there are tens of tags (likely to grow to hundreds). Most operation on the database are expected to be searches for the items that have a particular tag. That is, users will search per tags, not per items. These are the ways that I've thought about storing the tags, some bad and some worse. If there is a better way I'd love to know. 1) Each item will get a row in a tags table, with a column for each tag. mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tag1 bool, tag2 bool, tagN bool ); With this approach I would be adding a new column every time a new category is added. This looks to me a good way given that users will be searching per tag and a simple SELECT item FROM tags WHERE tag1=true; is an easy, inexpensive query. This table will get very large, there will likely be literally thousands of items (there will exist more items than tags). 2) Store the applicable tags one per line in a text field in the items table. mysql CREATE TABLE items ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), tags text, ); This looks like a bad idea, searching by tag will be a mess. 3) Store the tags in a table and add items to a text field. For instance: mysql CREATE TABLE tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tagName VARCHAR(100), items text, ); This looks to be the best way from a MySQL data retrieval perspective, but I do not know how expensive it will be to then split the items in PHP. Furthermore, adding items to tags could get real expensive. Caveat: at some point in the future there may be added the ability to have a tag hierarchy. For instance, there could exist a tag restaurant that will get the subtags italian and french. I could fake this with any approach by having a table of existing tags with a parentTag field, so if I plan on having this table anyway would method 3 above be preferable? Note: this message is cross-posted to the MySQL and the PHP lists as I am really not sure where is the best place to do the logic. My apologies to those who receive the message twice. Thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php I'd have my items table, my tags table and a join table for the two. My join table is really simple. UniqueID, ItemID, TagID. I'd recommend using a nested set approach for the tags (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html gives a good explanation on the issues and methodology of nested sets). -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 17:00, Richard Quadling rquadl...@gmail.com wrote: I'd have my items table, my tags table and a join table for the two. My join table is really simple. UniqueID, ItemID, TagID. Yes, that is the first approach that I mentioned. It looks to be a good compromise. I'd recommend using a nested set approach for the tags (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html gives a good explanation on the issues and methodology of nested sets). That is terrific, at least the first half. The second half, with the Venn diagrams, is awkward! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 18:20, Dotan Cohen dotanco...@gmail.com wrote: On Thu, Jan 20, 2011 at 17:00, Richard Quadling rquadl...@gmail.com wrote: I'd have my items table, my tags table and a join table for the two. My join table is really simple. UniqueID, ItemID, TagID. Yes, that is the first approach that I mentioned. It looks to be a good compromise. Sorry, that was _not_ the first approach that I mentioned. It looks to be the right method though, thanks! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 17:22, Jerry Schwartz je...@gii.co.jp wrote: I think the canonical way would be to have one table for your items, one table for your tags, and one table for your tag assignments. Thank you, I do agree that this is the best way. Other posters seem to agree as well! Using an ever-lengthening bitmap for the tag assignments is a trap for the unwary. The path to perdition is lined with the bodies of those who believed We'll never need more than x... 640 kb? As for setting up a hierarchy, that's trickier. One way to handle that is to work like libraries do: 10 is fiction, 10.05 is crime novels, 10.05.07 is British authors, and so forth. Your `tags` table then looks like Thanks. I prefer the parent tag field, though, I feel that it is more flexible. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 17:22, Peter Brawley peter.braw...@earthlink.net wrote: I'd exclude (1) because new tags require restructuring the table, (2) and (3) because they break a cardinal rule of design and will be a mess to query, leaving ... 4) Standard many-many bridge table: mysql CREATE TABLE items_tags ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, itemID int, tagID INT ); Will not require a major overhaul if you later turn categories into a tree. Terrific, Peter, this looks like the right direction. I appreciate the input. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On 20 January 2011 16:20, Dotan Cohen dotanco...@gmail.com wrote: On Thu, Jan 20, 2011 at 17:00, Richard Quadling rquadl...@gmail.com wrote: I'd have my items table, my tags table and a join table for the two. My join table is really simple. UniqueID, ItemID, TagID. Yes, that is the first approach that I mentioned. It looks to be a good compromise. I'd recommend using a nested set approach for the tags (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html gives a good explanation on the issues and methodology of nested sets). That is terrific, at least the first half. The second half, with the Venn diagrams, is awkward! -- Dotan Cohen http://gibberish.co.il http://what-is-what.com When you get heavily nested data, the adjacent set model (where you have a parentid for every uniqueid), you very quickly get into complicated logic trying to traverse n-levels. The nested set model is specifically built to handle this issue. I'd recommend getting to grips with it. It will make finding items belonging to a group (or a super group) a LOT easier. Especially if you have multiple tag hierarchies. -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
I cannot agree more with the others about using a join table. While it's tempting to go with your first solution due to fear of performance issues, you can usually address performance issues with a technical solution. Addressing problems that arise from a constraining design choice is much more difficult. David
best way to have a unique key
Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best way to have a unique key
uuid() Krishna On Fri, Jan 21, 2011 at 12:02 AM, Anthony Pace anthony.p...@utoronto.cawrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
Pseudo = Design Algorithm Design Algorithm = Actual Code Actual Code = Alterable db tables Alterable db tables = manipulated data through the app interface with data -- The lawyer in me says argue...even if you're wrong. The scientist in me... says shut up, listen, and then argue. But the lawyer won on appeal, so now I have to argue due to a court order. Furthermore, if you could be a scientific celebrity, would you want einstein sitting around with you on saturday morning, while you're sitting in your undies, watching Underdog?...Or better yet, would Einstein want you to violate his Underdog time? Can you imagine Einstein sitting around in his underware? Thinking about the relativity between his pubic nardsac, and his Fruit of the Looms, while knocking a few Dorito's crumbs off his inner brilliant white thighs, and hailing E = mc**2, and licking the orangy, delicious, Doritoey crust that layered his genetically rippled fingertips? But then again, J. Edgar Hoover would want his pantyhose intertwined within the equation. However, I digress, momentarily. But Einstein gave freely, for humanity, not for gain, other than personal freedom. An equation that benefited all, and yet gain is a personal product. Also, if you can answer it, is gravity anymore than interplanetary static cling? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best way to have a unique key
Dude, come on. I know that all primary keys have to be unique; however, I was obviously referring to the use of uuid over auto incrementation. On 1/20/2011 1:36 PM, Michael Dykman wrote: It is axiomatic in the relational model that a primary must be unique. This is not a quirk put forth by your current employer. Neither MySQL nor any other RDBMS will allow you to establish a primary key that is not unique. - michael dykman On Thu, Jan 20, 2011 at 1:32 PM, Anthony Paceanthony.p...@utoronto.ca wrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best way to have a unique key
I know of uuid() my problem is that there can be conflicts when copying the DB to a different machine, or working with sections of the db on different machines for load balancing. On 1/20/2011 1:44 PM, Krishna Chandra Prajapati wrote: Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best way to have a unique key
Although I did berate you for your obvious cheek, I will of course complement the acuteness of your response. On 1/20/2011 2:10 PM, Anthony Pace wrote: Dude, come on. I know that all primary keys have to be unique; however, I was obviously referring to the use of uuid over auto incrementation. On 1/20/2011 1:36 PM, Michael Dykman wrote: It is axiomatic in the relational model that a primary must be unique. This is not a quirk put forth by your current employer. Neither MySQL nor any other RDBMS will allow you to establish a primary key that is not unique. - michael dykman On Thu, Jan 20, 2011 at 1:32 PM, Anthony Paceanthony.p...@utoronto.ca wrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 19:21, Richard Quadling rquadl...@gmail.com wrote: That is terrific, at least the first half. The second half, with the Venn diagrams, is awkward! When you get heavily nested data, the adjacent set model (where you have a parentid for every uniqueid), you very quickly get into complicated logic trying to traverse n-levels. The nested set model is specifically built to handle this issue. I'd recommend getting to grips with it. It will make finding items belonging to a group (or a super group) a LOT easier. Especially if you have multiple tag hierarchies. Is that strategy widely deployed, then? It seems so unruly having to change on average half the database records for every new leaf. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 20:50, David Hutto smokefl...@gmail.com wrote: Pseudo = Design Algorithm Design Algorithm = Actual Code Actual Code = Alterable db tables Alterable db tables = manipulated data through the app interface with data -- The lawyer in me says argue...even if you're wrong. The scientist in me... says shut up, listen, and then argue. But the lawyer won on appeal, so now I have to argue due to a court order. Furthermore, if you could be a scientific celebrity, would you want einstein sitting around with you on saturday morning, while you're sitting in your undies, watching Underdog?...Or better yet, would Einstein want you to violate his Underdog time? Can you imagine Einstein sitting around in his underware? Thinking about the relativity between his pubic nardsac, and his Fruit of the Looms, while knocking a few Dorito's crumbs off his inner brilliant white thighs, and hailing E = mc**2, and licking the orangy, delicious, Doritoey crust that layered his genetically rippled fingertips? But then again, J. Edgar Hoover would want his pantyhose intertwined within the equation. However, I digress, momentarily. But Einstein gave freely, for humanity, not for gain, other than personal freedom. An equation that benefited all, and yet gain is a personal product. Also, if you can answer it, is gravity anymore than interplanetary static cling? Is this a troll? Am I about to be baited? -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
Is this a troll? Am I about to be baited? Baited to deploy what is designed to the consumer's specification? Surely. From what is wanted to what is needed. Troll on that. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- The lawyer in me says argue...even if you're wrong. The scientist in me... says shut up, listen, and then argue. But the lawyer won on appeal, so now I have to argue due to a court order. Furthermore, if you could be a scientific celebrity, would you want einstein sitting around with you on saturday morning, while you're sitting in your undies, watching Underdog?...Or better yet, would Einstein want you to violate his Underdog time? Can you imagine Einstein sitting around in his underware? Thinking about the relativity between his pubic nardsac, and his Fruit of the Looms, while knocking a few Dorito's crumbs off his inner brilliant white thighs, and hailing E = mc**2, and licking the orangy, delicious, Doritoey crust that layered his genetically rippled fingertips? But then again, J. Edgar Hoover would want his pantyhose intertwined within the equation. However, I digress, momentarily. But Einstein gave freely, for humanity, not for gain, other than personal freedom. An equation that benefited all, and yet gain is a personal product. Also, if you can answer it, is gravity anymore than interplanetary static cling? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 21:24, David Hutto smokefl...@gmail.com wrote: Is this a troll? Am I about to be baited? Baited to deploy what is designed to the consumer's specification? Surely. From what is wanted to what is needed. Troll on that. Actually, I'm the customer! But assuming that a customer exists, that implies compensation, and therefore fair bait. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 2:26 PM, Dotan Cohen dotanco...@gmail.com wrote: On Thu, Jan 20, 2011 at 21:24, David Hutto smokefl...@gmail.com wrote: Is this a troll? Am I about to be baited? Baited to deploy what is designed to the consumer's specification? Surely. From what is wanted to what is needed. Troll on that. Actually, I'm the customer! But assuming that a customer exists, that implies compensation, and therefore fair bait. Then that's different altogether. you get to decide what information is displayed, and what information is 'sensed', and on what platform. What do you want to sense and what do you want to display(not to say I'm an expert, but I like to think in CS)? -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- The lawyer in me says argue...even if you're wrong. The scientist in me... says shut up, listen, and then argue. But the lawyer won on appeal, so now I have to argue due to a court order. Furthermore, if you could be a scientific celebrity, would you want einstein sitting around with you on saturday morning, while you're sitting in your undies, watching Underdog?...Or better yet, would Einstein want you to violate his Underdog time? Can you imagine Einstein sitting around in his underware? Thinking about the relativity between his pubic nardsac, and his Fruit of the Looms, while knocking a few Dorito's crumbs off his inner brilliant white thighs, and hailing E = mc**2, and licking the orangy, delicious, Doritoey crust that layered his genetically rippled fingertips? But then again, J. Edgar Hoover would want his pantyhose intertwined within the equation. However, I digress, momentarily. But Einstein gave freely, for humanity, not for gain, other than personal freedom. An equation that benefited all, and yet gain is a personal product. Also, if you can answer it, is gravity anymore than interplanetary static cling? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
-Original Message- From: Dotan Cohen [mailto:dotanco...@gmail.com] Sent: Thursday, January 20, 2011 11:25 AM To: Jerry Schwartz Cc: mysql.; php-general. Subject: Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist As for setting up a hierarchy, that's trickier. One way to handle that is to work like libraries do: 10 is fiction, 10.05 is crime novels, 10.05.07 is British authors, and so forth. Your `tags` table then looks like Thanks. I prefer the parent tag field, though, I feel that it is more flexible. [JS] I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 2:40 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Dotan Cohen [mailto:dotanco...@gmail.com] Sent: Thursday, January 20, 2011 11:25 AM To: Jerry Schwartz Cc: mysql.; php-general. Subject: Re: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist As for setting up a hierarchy, that's trickier. One way to handle that is to work like libraries do: 10 is fiction, 10.05 is crime novels, 10.05.07 is British authors, and so forth. Your `tags` table then looks like Thanks. I prefer the parent tag field, though, I feel that it is more flexible. [JS] I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. No one argues with method of implementation(not that i explored the individual ideas), but with that available, you can hierarchy the concept of your db conceptualization. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- The lawyer in me says argue...even if you're wrong. The scientist in me... says shut up, listen, and then argue. But the lawyer won on appeal, so now I have to argue due to a court order. Furthermore, if you could be a scientific celebrity, would you want einstein sitting around with you on saturday morning, while you're sitting in your undies, watching Underdog?...Or better yet, would Einstein want you to violate his Underdog time? Can you imagine Einstein sitting around in his underware? Thinking about the relativity between his pubic nardsac, and his Fruit of the Looms, while knocking a few Dorito's crumbs off his inner brilliant white thighs, and hailing E = mc**2, and licking the orangy, delicious, Doritoey crust that layered his genetically rippled fingertips? But then again, J. Edgar Hoover would want his pantyhose intertwined within the equation. However, I digress, momentarily. But Einstein gave freely, for humanity, not for gain, other than personal freedom. An equation that benefited all, and yet gain is a personal product. Also, if you can answer it, is gravity anymore than interplanetary static cling? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best way to have a unique key
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 What conflicts are you expecting? according to the documentation: A UUID is designed as a number that is globally unique in space and time. Two calls to UUID() are expected to generate two different values, even if these calls are performed on two separate computers that are not connected to each other. On 11-01-20 14:11, Anthony Pace wrote: I know of uuid() my problem is that there can be conflicts when copying the DB to a different machine, or working with sections of the db on different machines for load balancing. On 1/20/2011 1:44 PM, Krishna Chandra Prajapati wrote: Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.14 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJNOJQBAAoJENgwSj9ZOOwr7NcIAJUVGGPhtiRRrNjrSUBZiO4c 0tsAKbsugnlJ7EI/61ALD8UCelBKKvDnRD0MFfCLHLTukbPkF+4YmwAi1tOMVo0J OteGOxXroo0dZhKt6/SommvtM9uXnHT6WJiTs8w5uP/TEUmqIECp4x3M0Fwjs9HY HzV/Tqo/pqlBpbdagahm+pm+9mK+g5AYR7xenBXwynu05XqClUCptSdh6NIhnBD5 fLw9e6AVOAeG1sbswR51pFtuDpXT0IlHn3U/7rdIioglYakphT21MQ5oM2kuTuis LE3xAR/YydiKa9GUPfghR/+0Xp7DGes1+HAXq4dkmnSWFEw218Jt5y6r131/EPg= =fra3 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 21:40, Jerry Schwartz je...@gii.co.jp wrote: Thanks. I prefer the parent tag field, though, I feel that it is more flexible. [JS] I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. I suppose for retrievals this structure has advantages, but unless MySQL has a ++ operator (or better yet, one that adds or subtracts 2 from an int) then it looks to be a pain to add nodes. But I will play with the idea. Maybe after I write the code (I'm saving that for tomorrow) I'll see it differently. Thanks. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 7:00 AM, Richard Quadling rquadl...@gmail.comwrote: I'd recommend using a nested set approach for the tags (http://dev.mysql.com/tech-resources/articles/hierarchical-data.html gives a good explanation on the issues and methodology of nested sets). Thanks for the link. That article proposes an interesting way to organize the categories. Have you implemented this in the wild? Clearly the design would work as it's pretty simple, and I like that it removes the need for recursive queries. Dotan, the Venn diagrams are just used to explain the concept. If you use the code to determine the left and right values, you can ignore the diagrams entirely. As long as you're not adding/removing categories every minute, having to recalculate left and right values isn't that big of a deal. Also, there's no reason you couldn't keep the parent_id field with the nested sets. It would come in handy for certain types of queries, though it's not necessary. On Thu, Jan 20, 2011 at 11:40 AM, Jerry Schwartz je...@gii.co.jp wrote: I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. The nested set method can be extended to any depth, and it pays off more the larger the hierarchy grows. While you can retrieve any branch (all ancestors) of a node with a single SQL query, the SQL engine itself actually must perform a recursive query meaning multiple hits on the parent_id index. On Thu, Jan 20, 2011 at 11:59 AM, Dotan Cohen dotanco...@gmail.com wrote: I suppose for retrievals this structure has advantages, but unless MySQL has a ++ operator (or better yet, one that adds or subtracts 2 from an int) then it looks to be a pain to add nodes. ++ or += wouldn't be any better here than x = x + 2. Once you're modifying indexed values, you'll pay a much higher price writing to disk than += could ever save you in CPU cycles. The beauty is that inserting a node requires only two update statements that will fix *all* categories that need to be adjusted. Adding categories to the hierarchical model is definitely faster so it comes down to your insert-to-select ratio. Moving a subtree is also much easier with the hierarchical model. David
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 22:05, David Harkness davi...@highgearmedia.com wrote: Thanks for the link. That article proposes an interesting way to organize the categories. Have you implemented this in the wild? Clearly the design would work as it's pretty simple, and I like that it removes the need for recursive queries. I am also interested in knowing if this approach is used in any production code. Dotan, the Venn diagrams are just used to explain the concept. If you use the code to determine the left and right values, you can ignore the diagrams entirely. As long as you're not adding/removing categories every minute, having to recalculate left and right values isn't that big of a deal. I understood that. My concern is exactly with adding new nodes. There is no incrementor (++i) in SQL, so knowingly coding a solution that will require incrementing two fields in half the database rows seems irresponsible. Also, there's no reason you couldn't keep the parent_id field with the nested sets. It would come in handy for certain types of queries, though it's not necessary. That is true. I could store both methods, and experiment to see which is preferable. But what a mess this would be if the two methods go out of sync! Isn't there a name for that in SQL, something along the lines of not storing the same data in two places lest one should change and not the other? The term escapes me. I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. The nested set method can be extended to any depth, and it pays off more the larger the hierarchy grows. While you can retrieve any branch (all ancestors) of a node with a single SQL query, the SQL engine itself actually must perform a recursive query meaning multiple hits on the parent_id index. That pays off more? For the guy writing code or for the database memory requirement? I suppose for retrievals this structure has advantages, but unless MySQL has a ++ operator (or better yet, one that adds or subtracts 2 from an int) then it looks to be a pain to add nodes. ++ or += wouldn't be any better here than x = x + 2. Once you're modifying indexed values, you'll pay a much higher price writing to disk than += could ever save you in CPU cycles. The beauty is that inserting a node requires only two update statements that will fix *all* categories that need to be adjusted. Only two update statements, but they are affecting on average half the database's rows! Adding categories to the hierarchical model is definitely faster so it comes down to your insert-to-select ratio. Moving a subtree is also much easier with the hierarchical model. Which do you call the hierarchical model? That term is not used in the linked article. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best way to have a unique key
I should have read more carefully.. I apologize for my snap response. At a guess: as I recall, under M$ SQLServer the typical (only?) form of unique identifier used is something very UUID-like. MY information might be dated. I was certified as a SQL Server administrator perhaps 12 years agoI would not be terribly surprised to learn that was their motivation. I recall, many years ago, having this same, largely theoretical concern regarding MD5 hashes. Since then experience has taught me that my worries were unfounded. UUID() should be very safe, even for very large datasets. - michael dykman On Thu, Jan 20, 2011 at 2:20 PM, Anthony Pace anthony.p...@utoronto.ca wrote: Although I did berate you for your obvious cheek, I will of course complement the acuteness of your response. On 1/20/2011 2:10 PM, Anthony Pace wrote: Dude, come on. I know that all primary keys have to be unique; however, I was obviously referring to the use of uuid over auto incrementation. On 1/20/2011 1:36 PM, Michael Dykman wrote: It is axiomatic in the relational model that a primary must be unique. This is not a quirk put forth by your current employer. Neither MySQL nor any other RDBMS will allow you to establish a primary key that is not unique. - michael dykman On Thu, Jan 20, 2011 at 1:32 PM, Anthony Paceanthony.p...@utoronto.ca wrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - 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?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
On Thu, Jan 20, 2011 at 12:21 PM, Dotan Cohen dotanco...@gmail.com wrote: I understood that. My concern is exactly with adding new nodes. There is no incrementor (++i) in SQL, so knowingly coding a solution that will require incrementing two fields in half the database rows seems irresponsible. It only requires updating the category rows. If you have several hundred categories this is a non-issue. If you have several thousand categories, you probably have millions of products, and you'd want to do some performance analysis on it. Even still, this is necessary only when adding new categories. If you are doing this often, you could leave spaces in the left and right values so that you could minimize the number of rows that need to be updated. The article makes every leaf use x and x+1 for left and right which forces another update to add a child. If instead you used x and x+20 you'd leave space for more children without any updates. This could be applied from top to bottom, starting with the root category getting 0 and MAX_INT for its values. However, it's probably not even worth applying that complexity until you prove that frequent category additions are causing problems. Most systems will be querying against the categories table far more frequently, and that's where this model pays off. If you want to see all products in category X and its subcategories, it's a single *non-recursive* query. That's huge if you are doing a lot of searches like this. But what a mess this would be if the two methods go out of sync! Sure, but these values would be maintained by your code--not end-users. It just comes down to making sure your code is correct through appropriate unit tests. By moving the logic to a stored procedure, you can ensure the table is locked during the updates to keep two users from adding a new category simultaneously. That pays off more? For the guy writing code or for the database memory requirement? Performance-wise. The nested set method looks to be moderately more complex code-wise, but luckily that is done just once while querying the database is done again and again. As with all optimizations, it's best to measure and make sure there's a problem before trying to solve it. Once you've built a few hierarchical systems, you'll be able to make a gut call up front. Only two update statements, but they are affecting on average half the database's rows! Of a single table: categories. Hopefully you have far more items that get categorized than you do categories. Which do you call the hierarchical model? That term is not used in the linked article. Well, both models are hierarchical in the sense that there's a parent-child relationship. By hierarchical here I mean that the method of implementation involves each category pointing to its parent directly via a parent_id column. Searching for all subcategories of category X requires searching first for all children, then all grandchildren, and so on, resulting in a recursive query. Using the nested sets model requires a single non-recursive query to get the same data. David
RE: best way to have a unique key
http://www.mysqlperformanceblog.com/2007/03/13/to-uuid-or-not-to-uuid/ -Original Message- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, January 20, 2011 10:45 AM To: Anthony Pace Cc: mysql. Subject: Re: best way to have a unique key uuid() Krishna On Fri, Jan 21, 2011 at 12:02 AM, Anthony Pace anthony.p...@utoronto.cawrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [PHP] RE: Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
[JS] I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. I suppose for retrievals this structure has advantages, but unless MySQL has a ++ operator (or better yet, one that adds or subtracts 2 from an int) then it looks to be a pain to add nodes. [JS] Not at all. Somebody, somehow, has to assign a name to the tag and designate its place in the hierarchy. I don't see how you can avoid that being done by a human. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com But I will play with the idea. Maybe after I write the code (I'm saving that for tomorrow) I'll see it differently. Thanks. -- Dotan Cohen http://gibberish.co.il http://what-is-what.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] Organisational question: surely someone has implemented many Boolean values (tags) and a solution exist
My concern is exactly with adding new nodes. There is no incrementor (++i) in SQL, so knowingly coding a solution that will require incrementing two fields in half the database rows seems irresponsible. Yes, and an edge list model may perform better in other respects too: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html PB - On 1/20/2011 2:21 PM, Dotan Cohen wrote: On Thu, Jan 20, 2011 at 22:05, David Harknessdavi...@highgearmedia.com wrote: Thanks for the link. That article proposes an interesting way to organize the categories. Have you implemented this in the wild? Clearly the design would work as it's pretty simple, and I like that it removes the need for recursive queries. I am also interested in knowing if this approach is used in any production code. Dotan, the Venn diagrams are just used to explain the concept. If you use the code to determine the left and right values, you can ignore the diagrams entirely. As long as you're not adding/removing categories every minute, having to recalculate left and right values isn't that big of a deal. I understood that. My concern is exactly with adding new nodes. There is no incrementor (++i) in SQL, so knowingly coding a solution that will require incrementing two fields in half the database rows seems irresponsible. Also, there's no reason you couldn't keep the parent_id field with the nested sets. It would come in handy for certain types of queries, though it's not necessary. That is true. I could store both methods, and experiment to see which is preferable. But what a mess this would be if the two methods go out of sync! Isn't there a name for that in SQL, something along the lines of not storing the same data in two places lest one should change and not the other? The term escapes me. I disagree. The method I proposed can be extended to any depth, and any leaf or branch can be retrieved with a single query. The nested set method can be extended to any depth, and it pays off more the larger the hierarchy grows. While you can retrieve any branch (all ancestors) of a node with a single SQL query, the SQL engine itself actually must perform a recursive query meaning multiple hits on the parent_id index. That pays off more? For the guy writing code or for the database memory requirement? I suppose for retrievals this structure has advantages, but unless MySQL has a ++ operator (or better yet, one that adds or subtracts 2 from an int) then it looks to be a pain to add nodes. ++ or += wouldn't be any better here than x = x + 2. Once you're modifying indexed values, you'll pay a much higher price writing to disk than += could ever save you in CPU cycles. The beauty is that inserting a node requires only two update statements that will fix *all* categories that need to be adjusted. Only two update statements, but they are affecting on average half the database's rows! Adding categories to the hierarchical model is definitely faster so it comes down to your insert-to-select ratio. Moving a subtree is also much easier with the hierarchical model. Which do you call the hierarchical model? That term is not used in the linked article. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Interrupt ALTER Process
Hi, I have an InnoDB with 12 millions of data. For some reason I need to alter the table structure by expanding the varchars value. It's been an hour and slow down the performance. Is it safe to kill the process? Thanks. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Interrupt ALTER Process
Remember, the InnoDB table has a full table lock now since it is doing and ALTER TABLE. You may want to kill it and try this instead: In this example, the table with 12M rows is called BigTable 1) CREATE TABLE BigTable2 LIKE BigTable; 2) ALTER TABLE BigTable MODIFY COLUMN column-name VARCHAR(new size); 3) INSERT INTO BigTable2 SELECT * FROM BigTable; 4) DROP TABLE BigTable; 5) ALTER TABLE BigTable2 RENAME BigTable; Give it a try !!! Rolando A. Edwards MySQL DBA (SCMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: Willy Mularto [mailto:sangpr...@gmail.com] Sent: Thursday, January 20, 2011 10:19 PM To: mysql@lists.mysql.com Subject: Interrupt ALTER Process Hi, I have an InnoDB with 12 millions of data. For some reason I need to alter the table structure by expanding the varchars value. It's been an hour and slow down the performance. Is it safe to kill the process? Thanks. Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: best way to have a unique key
I have to say, something similar was my first thought, too - you never mention uuid in your original post. As already stated, uuid() should be a Universal Unique IDentifier. It's afaik a random 128-bit number; given the space to choose from it should be rather unique. I have to admit that I'm not entirely confident about that myself, either, though: as Pratchett put it, one-in-a-million chances tend to pop up nine times out of ten. The code should have bits for handling duplicate primaries regardless of the method used to generate it, tough, so there's no reason to not do it. Having two subsequent UUID() calls generate pre-existing numbers seems to me to be likely in the same way as having Bush return his dirty oil dollars to Irak. On Thu, Jan 20, 2011 at 8:10 PM, Anthony Pace anthony.p...@utoronto.cawrote: Dude, come on. I know that all primary keys have to be unique; however, I was obviously referring to the use of uuid over auto incrementation. On 1/20/2011 1:36 PM, Michael Dykman wrote: It is axiomatic in the relational model that a primary must be unique. This is not a quirk put forth by your current employer. Neither MySQL nor any other RDBMS will allow you to establish a primary key that is not unique. - michael dykman On Thu, Jan 20, 2011 at 1:32 PM, Anthony Paceanthony.p...@utoronto.ca wrote: Due to certain reasons, the company I am doing business with has decided that the primary key, for an orders table, be a unique key; however, I don't like the possibility of it conflicting if moved to another machine. What are some pitfalls of using a unique key, that is generated by a server side script, rather than by mysql? What are the best ways to do this? Please keep in mind this variable will also be displayed on the customer's Receipt, but again, since it's random, it doesn't have to mean anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
