Re: Table Size
Dan Nelson wrote: In the last episode (Oct 27), Baron Schwartz said: InnoDB has the following extra things, plus some things I might forget: a) the primary key B-Tree b) row versioning information for every row c) 16k page size; each page might not be completely full Those are all counted towards the table size. Actually, the primary key B-Tree might not be; I'd need to look that up. But I think it is. H. I just tested -- yes, the PK counts towards table size. In fact, in InnoDB, all indexes count towards table size, since there is a single .ibd file for the whole thing. So you've got the space taken up by your `repid` index to consider as well. It's true they're in the same file, but the secondary indexes show up in the 'Index_length' column in SHOW TABLE STATUS. I was double-checking that the primary key contributes to the 'Data_length' column, not the 'Index_length' column. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left outer join trouble
Hi, I'm trying to write a query which returns a single record which contains concatenated values for referencing records: SELECT tickets.id AS id, CAST(GROUP_CONCAT(tags.name SEPARATOR ' ') AS CHAR) AS tags, CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS text FROM tickets LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id LEFT OUTER JOIN taggings AS taggings ON taggings.taggable_id = tickets.id LEFT OUTER JOIN tags AS tags ON taggings.tag_id = tags.id GROUP BY id; The problem with this query is, that it returns too many matches in the concatenated fields when more than one concatenation is used. Ie. given the data: CREATE TABLE tickets (id integer); CREATE TABLE events (ticket_id integer, value varchar(32)); CREATE TABLE tags (id integer, name varchar(32)); CREATE TABLE taggings (taggable_id integer, tag_id integer); INSERT INTO tickets VALUES (1); INSERT INTO events VALUES (1, 'Event A'); INSERT INTO events VALUES (1, 'Event B'); INSERT INTO events VALUES (1, 'Event C'); INSERT INTO tags VALUES (1, 'Tag A'); INSERT INTO tags VALUES (2, 'Tag B'); INSERT INTO taggings VALUES (1, 1); INSERT INTO taggings VALUES (1, 2); The query returns duplicates: +--+-+-+ | id | tags| text | +--+-+-+ |1 | Tag A Tag B Tag A Tag B Tag A Tag B | Event A Event A Event B Event B Event C Event C | +--+-+-+ I suspect this has to do with the multiple GROUP_CONCATs as it works fine when using only a single GROUP_CONCAT: mysql SELECT tickets.id AS id, -CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS text - FROM tickets - LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id - GROUP BY id - ; +--+-+ | id | text| +--+-+ |1 | Event A Event B Event C | +--+-+ I'm tempted to solve this using a view or two, but would like to know if there's a better way. Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer join trouble
Hi, Morten wrote: Hi, I'm trying to write a query which returns a single record which contains concatenated values for referencing records: SELECT tickets.id AS id, CAST(GROUP_CONCAT(tags.name SEPARATOR ' ') AS CHAR) AS tags, CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS text FROM tickets LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id LEFT OUTER JOIN taggings AS taggings ON taggings.taggable_id = tickets.id LEFT OUTER JOIN tags AS tags ON taggings.tag_id = tags.id GROUP BY id; The problem with this query is, that it returns too many matches in the concatenated fields when more than one concatenation is used. Ie. given the data: CREATE TABLE tickets (id integer); CREATE TABLE events (ticket_id integer, value varchar(32)); CREATE TABLE tags (id integer, name varchar(32)); CREATE TABLE taggings (taggable_id integer, tag_id integer); INSERT INTO tickets VALUES (1); INSERT INTO events VALUES (1, 'Event A'); INSERT INTO events VALUES (1, 'Event B'); INSERT INTO events VALUES (1, 'Event C'); INSERT INTO tags VALUES (1, 'Tag A'); INSERT INTO tags VALUES (2, 'Tag B'); INSERT INTO taggings VALUES (1, 1); INSERT INTO taggings VALUES (1, 2); The query returns duplicates: +--+-+-+ | id | tags| text | +--+-+-+ |1 | Tag A Tag B Tag A Tag B Tag A Tag B | Event A Event A Event B Event B Event C Event C | +--+-+-+ I suspect this has to do with the multiple GROUP_CONCATs as it works fine when using only a single GROUP_CONCAT: mysql SELECT tickets.id AS id, -CAST(GROUP_CONCAT(events.value SEPARATOR ' ') AS CHAR) AS text - FROM tickets - LEFT OUTER JOIN events AS events ON tickets.id = events.ticket_id - GROUP BY id - ; +--+-+ | id | text| +--+-+ |1 | Event A Event B Event C | +--+-+ I'm tempted to solve this using a view or two, but would like to know if there's a better way. GROUP_CONCAT() takes an optional DISTINCT modifier, and that might do what you're looking for. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer join trouble
Baron Schwartz wrote: I'm tempted to solve this using a view or two, but would like to know if there's a better way. GROUP_CONCAT() takes an optional DISTINCT modifier, and that might do what you're looking for. It sure does the trick. I'll use that, I was afraid that I was missing something fundamental in the joins. Thanks, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left outer join trouble
Martin Gainty wrote: Good Morning- Good afternoon :-) http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by- extension/ I did'nt see your where clause ? I'm probably missing your point here. But there's no where clause because I want all records from the tickets table returned, and I do the joins using outer left joins. Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and filesystems question
Hi, i want to ask for your opinions about the best combination for mysql with filesystem (like ext2, ext3... and so on). We have problems with mysql under fedora 6 with ext3 and i want to know under wich filesystem mysql works best. I will be glad to read your opinions. Thanks in advanced. Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and filesystems question
In production we use both reiserfs and xfs. I am migrating our data partitions for mysql because it has faster write rates than reiserfs. You need to stop using fedora and at least use CentOS. Fedora is not stable for production work. Just my two cents :) Keith Ali Nebi wrote: Hi, i want to ask for your opinions about the best combination for mysql with filesystem (like ext2, ext3... and so on). We have problems with mysql under fedora 6 with ext3 and i want to know under wich filesystem mysql works best. I will be glad to read your opinions. Thanks in advanced. Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and filesystems question
I dashed off this email too fast. What I meant to say was I am migrating our data partitions for MySQL from reiserfs to xfs because xfs has faster write rates than reiserfs. Craig Huffstetler wrote: I agree, Fedora is not very good at production work. Reiserfs is one of the best file systems I have come across for MySQL Production. On 10/28/07, *B. Keith Murphy * [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: In production we use both reiserfs and xfs. I am migrating our data partitions for mysql because it has faster write rates than reiserfs. You need to stop using fedora and at least use CentOS. Fedora is not stable for production work. Just my two cents :) Keith Ali Nebi wrote: Hi, i want to ask for your opinions about the best combination for mysql with filesystem (like ext2, ext3... and so on). We have problems with mysql under fedora 6 with ext3 and i want to know under wich filesystem mysql works best. I will be glad to read your opinions. Thanks in advanced. Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and filesystems question
You seemed to have answered your own question. What did you mean to ask? On 10/28/07, B. Keith Murphy [EMAIL PROTECTED] wrote: I dashed off this email too fast. What I meant to say was I am migrating our data partitions for MySQL from reiserfs to xfs because xfs has faster write rates than reiserfs. Craig Huffstetler wrote: I agree, Fedora is not very good at production work. Reiserfs is one of the best file systems I have come across for MySQL Production. On 10/28/07, *B. Keith Murphy * [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: In production we use both reiserfs and xfs. I am migrating our data partitions for mysql because it has faster write rates than reiserfs. You need to stop using fedora and at least use CentOS. Fedora is not stable for production work. Just my two cents :) Keith Ali Nebi wrote: Hi, i want to ask for your opinions about the best combination for mysql with filesystem (like ext2, ext3... and so on). We have problems with mysql under fedora 6 with ext3 and i want to know under wich filesystem mysql works best. I will be glad to read your opinions. Thanks in advanced. Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Post Installation for Mysql on Ubuntu
Hello: I am preparing to install the latest mysql server on ubuntu - I think it will be version 5+. It has been some time since I have done a setup and I would appreciate updated instructions. The ubuntu install via Adept Manager will ask for a root password. Following that I know that I must add one or more user accounts. I see some good info at http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html Any other good examples would be welcome. Thanks Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
