Re: fields separator
sed -r "s/ +/\t/g" infile.txt or perl -e "s/\s+/\t/g and print $_.\"\n\" while <>" < infile.txt -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mmultiple languages in the MySQL database
How do I store multiple languages in the MySQL database. Is there any configuration in MySql database server to support multilingual data. Normally if I use UTF-8 character encoding in database, it supports multiple languages. But it is not happening in MySql. Thanks Krish -- View this message in context: http://www.nabble.com/Mmultiple-languages-in-the-MySQL-database-tf2348859.html#a6540543 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to find last price and date product was sold
You could do something like that by using a derived table (subselect) to pick the max date for each product sale and then joining that on the products table again to pick up the amounts. Note that you must use mysql 5.x to be able to use subselects. Also this will return multiple rows for a given account if there are more than one sale on the last day each item has been sold. (There are several ways around that if it's a problem). I just ran such a query on the table I'm working on right now to test the idea... there are about 3000 rows in this table so the 0.52 second query time seems high, but there's only a primary key index. I think indexing on the transaction date (date_xact) would probably help in this example... mysql> select t1a.account, maxdate, amount from (select account, max (date_xact) maxdate from transactions t1 group by account) t1a left join transactions t2 on t1a.account=t2.account and maxdate=t2.date_xact order by t1a.account; +-++---+ | account | maxdate| amount| +-++---+ | 0 | 2005-08-17 | -15.06| | 1 | 2006-07-24 | 26790.00 | | 2 | 2006-07-14 | 1500.00 | | 2 | 2006-07-14 | 2040.00 | | 2 | 2006-07-14 | 2520.00 | | 3 | 2006-07-14 | -193.98 | | 3 | 2006-07-14 | -328.98 | | 3 | 2006-07-14 | -418.21 | | 4 | 2006-07-14 | -186.00 | | 4 | 2006-07-14 | -252.96 | | 4 | 2006-07-14 | -312.48 | | 5 | 2006-07-14 | -43.50| | 5 | 2006-07-14 | -59.16| | 5 | 2006-07-14 | -73.08| | 9 | 2006-06-27 | 60.06 | | 9 | 2006-06-27 | 196.77| | 10 | 2006-03-27 | 60.04 | | 11 | 2006-04-13 | 65.00 | | 12 | 2006-06-23 | -272.03 | | 13 | 2006-02-16 | 100.00| | 14 | 2006-07-14 | 114.75| | 14 | 2006-07-14 | 156.06| | 14 | 2006-07-14 | 192.78| | 15 | 2006-07-24 | -18240.00 | | 15 | 2006-07-24 | -8550.00 | | 16 | 2006-07-11 | -800.00 | | 17 | 2004-07-07 | -51.87| | 17 | 2004-07-07 | -50.49| | 17 | 2004-07-07 | -27.31| | 18 | 2006-06-01 | 288.77| | 19 | 2006-05-11 | 175.00| | 20 | 2006-01-05 | 50.00 | | 21 | 2006-07-15 | 152.90| | 22 | 2006-07-19 | -600.00 | | 23 | 2006-05-31 | 10.00 | | 24 | 2005-07-29 | -277.83 | | 25 | 2005-11-08 | -178.00 | | 26 | 2006-03-24 | 94.24 | | 26 | 2006-03-24 | 74.40 | | 26 | 2006-03-24 | 248.00| | 27 | 2006-03-24 | 22.04 | | 27 | 2006-03-24 | 17.40 | | 27 | 2006-03-24 | 58.00 | | 28 | 2006-06-07 | 185.00| | 29 | 2006-03-27 | 136.00| | 30 | 2006-07-18 | 398.16| | 31 | 2006-02-04 | 500.00| | 32 | 2006-04-06 | 64.00 | | 35 | 2006-04-15 | 1000.00 | | 37 | 2005-12-23 | 200.00| | 38 | 2006-05-12 | -51.04| | 39 | 2005-07-31 | 1191.00 | | 40 | 2006-05-29 | 65.00 | +-++---+ 53 rows in set (0.52 sec) Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 11:36 PM, mos wrote: This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- 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]
Need to find last price and date product was sold
This should be easy but I can't find a way of doing it in 1 step. I have a Trans table like: Product_Code: X(10) Date_Sold: Date Price_Sold: Float Now there will be 1 row for each Product_Code, Date combination. So over the past year a product_code could have over 300 rows, one row for each day it was sold. There are thousands of products. What I need to do is find the last price_sold for each product_code. Not all products are sold each day so a product might not have been sold for weeks. The only solution I've found is to do: drop table if exists CurrentPrices; create table CurrentPrices select Prod_Code, cast(max(Date_Sold) as Date), -1.0 Price_Sold from Trans group by Prod_Code; alter table CurrentPrices add index ix_ProdCode (Prod_Code); update CurrentPrices CP, Trans T set CP.Price_Sold=T.Price_Sold and T.Date_Sold=CP.Date_Sold; Is there a way to shorten this? It may take 2-3 minutes to execute. I don't really need a new table as long as I get the Prod_Code and the last Date_Sold. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql]Concurrency with inserts
In the last episode (Sep 28), Henda Carvalho said: > One more question, > > What kind of tables does mysql create by default? innodb or myisam? The default is myisam. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where url = 'x' with url a TEXT field
You can create FULLTEXT indexes on text fields - but that may not be the best solution for your situation since you are querying for an exact match. In MySQL 5.0.3 and later, you can create VARCHAR columns of up to 65,535 character lengths: http://dev.mysql.com/doc/refman/5.0/en/char.html I seem to recall you were asking about storing URLs up to the practical limit, 2083 characters. I've never used a varchar column that long before; I'm not sure what the performance implications might be, or how effective a database index on a field that length would be. Still, worth a try. Could be that it will work smashingly. HTH, Dan On 9/27/06, Peter Van Dijck <[EMAIL PROTECTED]> wrote: Hi, since urls can be longer than 255 chars, I made the url field a TEXT field. The problem is, I can't make an index on it, so doing 'WHERE url='xxx'' becomes a very sloow query. Any ideas for solutions? Am I mistaken in the idea that I can't make an index on a TEXT field? Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- 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: where url = 'x' with url a TEXT field
I think you have to specify a key length when you use an index on a text field... mysql> alter table t2 add index i2(t1(3)); That would create an index (called "i2") on the first 3 characters of field "t1" of table "t2". I think that's right? Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 8:53 PM, Peter Van Dijck wrote: Hi, since urls can be longer than 255 chars, I made the url field a TEXT field. The problem is, I can't make an index on it, so doing 'WHERE url='xxx'' becomes a very sloow query. Any ideas for solutions? Am I mistaken in the idea that I can't make an index on a TEXT field? Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- 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]
where url = 'x' with url a TEXT field
Hi, since urls can be longer than 255 chars, I made the url field a TEXT field. The problem is, I can't make an index on it, so doing 'WHERE url='xxx'' becomes a very sloow query. Any ideas for solutions? Am I mistaken in the idea that I can't make an index on a TEXT field? Thanks! Peter -- Find 1s of videoblogs and podcasts at http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Low priority copy?
This is a situation where u should use mysqlhotcopy. That gives u a snapshot of the current table and lets u work on it "offline" while the real table is available. hotcopy table A to B blank table A to allow inserts work on table B merge A into B delete A rename B to A -- Chris Wagner CBTS GE Aircraft Engines [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Red Hat slow query log
Brian Dunning wrote: Before I do this, I just wanted to check with you all to see if this is the correct command: /etc/rc.d/init.d/mysqld restart --log-slow-queries If so, where exactly will I find the slow query log? Will the slow query log be turned off by default next time I restart it? That's not going to work. The init script only recognises restart, stop, start as valid parameters. Once it sees one of those commands that's it. You could either hack the init.d/mysqld script or edit the my.cnf properly and set a log file: log-slow-queries= /path/to/log -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql]Concurrency with inserts
One more question, What kind of tables does mysql create by default? innodb or myisam? Best regards Henda On 9/27/06, Dan Nelson <[EMAIL PROTECTED]> wrote: In the last episode (Sep 27), Henda Carvalho said: > The links that you gave me doesn't say what is suposed to happen if 2 > different users are trying to make an insert (using the insert > statement) to the same table. > > Will the transaction abort for one of the users? No; they will both proceed. If you are using innodb tables, client A won't see the records inserted by client B until B commits (and vice versa). If using myisam, each client will see the others' records immediately. -- Dan Nelson [EMAIL PROTECTED]
Re: [mysql]Concurrency with inserts
In the last episode (Sep 27), Henda Carvalho said: > The links that you gave me doesn't say what is suposed to happen if 2 > different users are trying to make an insert (using the insert > statement) to the same table. > > Will the transaction abort for one of the users? No; they will both proceed. If you are using innodb tables, client A won't see the records inserted by client B until B commits (and vice versa). If using myisam, each client will see the others' records immediately. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[mysql]Concurrency with inserts
Hi there, The links that you gave me doesn't say what is suposed to happen if 2 different users are trying to make an insert (using the insert statement) to the same table. Will the transaction abort for one of the users? Best regards, Henda On 9/27/06, Dan Nelson <[EMAIL PROTECTED]> wrote: In the last episode (Sep 27), Henda Carvalho said: > Can somebody please tell me what is the behaviour of mysql when 2 > different users are trying to make an insert to the same table. http://dev.mysql.com/doc/refman/5.0/en/table-locking.html http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html -- Dan Nelson [EMAIL PROTECTED]
Red Hat slow query log
Before I do this, I just wanted to check with you all to see if this is the correct command: /etc/rc.d/init.d/mysqld restart --log-slow-queries If so, where exactly will I find the slow query log? Will the slow query log be turned off by default next time I restart it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [mysql]Concurrency with inserts
In the last episode (Sep 27), Henda Carvalho said: > Can somebody please tell me what is the behaviour of mysql when 2 > different users are trying to make an insert to the same table. http://dev.mysql.com/doc/refman/5.0/en/table-locking.html http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[mysql]Concurrency with inserts
Hello, Can somebody please tell me what is the behaviour of mysql when 2 different users are trying to make an insert to the same table. Best regards, Henda Mendes de Carvalho
[mysql]Concurrency with inserts
Hello, Can somebody please tell me what is the behaviour of mysql when 2 different users are trying to make an insert to the same table. Best regards, Henda Mendes de Carvalho
Re: Re: Low priority copy?
The table switch-a-roo scheme would accomplish this - it lets you copy the data into the duplicate table, and can run as long as needed since it won't be tying up a table that your users are trying to access. Then once the move is completed, the table rename operation should complete very quickly, transparently to your users. I agree with Brent, your problem with mysql locking up is not because the copy operation is so intense, but because of the fact that the table is locked during the copy. Since none of your apps or users would know about or try to access the duplicate table, you wouldn't have a locking problem. Dan On 9/27/06, Brian Dunning <[EMAIL PROTECTED]> wrote: This is the kind of thing I've been trying, but anything like this locks up the machine, all the users get errors, and I have to restart mysql. This is why I'm looking for something like a "LOW PRIORITY" solution, hoping that it won't try to use resources until they're available. On Sep 27, 2006, at 12:37 PM, Dan Buettner wrote: > Brian, I'm not sure there's a quick way to copy 14 million records, no > matter how you slice it. Disabling the indexes on the destination > table might help - but then you've got to devote some time to when you > re-enable them. > > You might try this workaround, where you're copying into a duplicate > of your new table structure. > > - CREATE TABLE newtable2 LIKE newtable > - INSERT INTO newtable2 SELECT * from oldtable /* or however you're > copying */ > - RENAME TABLE newtable TO newtable_bkup, newtable2 TO newtable > > Dan > > > On 9/27/06, Brian Dunning <[EMAIL PROTECTED]> wrote: >> I have a very busy 14,000,000 record table. I made a new version of >> the table, that's more efficient, and now I just need to copy over >> the data. Just about anything I try swamps the machine and locks up >> MySQL with "too many connections" because it's so damn busy. Can >> anyone suggest the most efficient way to copy over all the data to >> the new table with low priority so I don't kill the machine? It's OK >> if it takes up to around 10 minutes. Thanks... >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/mysql? >> [EMAIL PROTECTED] >> >> -- 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: Inserting/updating only when combo of fields is unique
Thanks Dan, I believe that's exactly what I was looking for. Thanks for not saying "RTFM" even though it clearly applies. :) On Sep 27, 2006, at 12:57 PM, Dan Julson wrote: Brian, Look at the ON DUPLICATE KEY UPDATE syntax within the INSERT SYNTAX of the Docs. That should give you what you want. -Dan I have a need to insert a record only when the combination of 3 fields is unique. I do this by having an index with all 3 fields, and doing an INSERT IGNORE. This works fine. Here's the element I can't figure out how to add: When there is a pre- existing record, I want to update two of its columns, rather than adding a new record. Any suggestions??? -- -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Low priority copy?
This is the kind of thing I've been trying, but anything like this locks up the machine, all the users get errors, and I have to restart mysql. This is why I'm looking for something like a "LOW PRIORITY" solution, hoping that it won't try to use resources until they're available. On Sep 27, 2006, at 12:37 PM, Dan Buettner wrote: Brian, I'm not sure there's a quick way to copy 14 million records, no matter how you slice it. Disabling the indexes on the destination table might help - but then you've got to devote some time to when you re-enable them. You might try this workaround, where you're copying into a duplicate of your new table structure. - CREATE TABLE newtable2 LIKE newtable - INSERT INTO newtable2 SELECT * from oldtable /* or however you're copying */ - RENAME TABLE newtable TO newtable_bkup, newtable2 TO newtable Dan On 9/27/06, Brian Dunning <[EMAIL PROTECTED]> wrote: I have a very busy 14,000,000 record table. I made a new version of the table, that's more efficient, and now I just need to copy over the data. Just about anything I try swamps the machine and locks up MySQL with "too many connections" because it's so damn busy. Can anyone suggest the most efficient way to copy over all the data to the new table with low priority so I don't kill the machine? It's OK if it takes up to around 10 minutes. Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting/updating only when combo of fields is unique
Brian, Look at the ON DUPLICATE KEY UPDATE syntax within the INSERT SYNTAX of the Docs. That should give you what you want. -Dan I have a need to insert a record only when the combination of 3 fields is unique. I do this by having an index with all 3 fields, and doing an INSERT IGNORE. This works fine. Here's the element I can't figure out how to add: When there is a pre- existing record, I want to update two of its columns, rather than adding a new record. Any suggestions??? -- -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting/updating only when combo of fields is unique
I have a need to insert a record only when the combination of 3 fields is unique. I do this by having an index with all 3 fields, and doing an INSERT IGNORE. This works fine. Here's the element I can't figure out how to add: When there is a pre- existing record, I want to update two of its columns, rather than adding a new record. Any suggestions??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Low priority copy?
Brian, I'm not sure there's a quick way to copy 14 million records, no matter how you slice it. Disabling the indexes on the destination table might help - but then you've got to devote some time to when you re-enable them. You might try this workaround, where you're copying into a duplicate of your new table structure. - CREATE TABLE newtable2 LIKE newtable - INSERT INTO newtable2 SELECT * from oldtable /* or however you're copying */ - RENAME TABLE newtable TO newtable_bkup, newtable2 TO newtable Dan On 9/27/06, Brian Dunning <[EMAIL PROTECTED]> wrote: I have a very busy 14,000,000 record table. I made a new version of the table, that's more efficient, and now I just need to copy over the data. Just about anything I try swamps the machine and locks up MySQL with "too many connections" because it's so damn busy. Can anyone suggest the most efficient way to copy over all the data to the new table with low priority so I don't kill the machine? It's OK if it takes up to around 10 minutes. Thanks... -- 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: Low priority copy?
I'm guessing what's happening is that your "import" is locking the table, putting everything else on hold. People keep connecting, getting put on hold until you run out of connections. It's not that you machine is "so busy", it just can't do two things at once. One of the limitations of MyISAM tables, Innodb works differently and wouldn't have this problem. The simplest solution would be to import a little bit at a time by selecting ranges from the old table (i.e. based on date) and inserting them into the new table. This can be done in just about anything, shell script, php, perl, etc. I do a nightly import of currently 5 million records, "importing" records contained in 20 minute intervals. Takes about 20 minutes for the php script to finish, depending on how busy the machine is and how big the table I'm importing into is. - Original Message - From: "Brian Dunning" <[EMAIL PROTECTED]> To: Sent: Wednesday, September 27, 2006 2:54 PM Subject: Low priority copy? I have a very busy 14,000,000 record table. I made a new version of the table, that's more efficient, and now I just need to copy over the data. Just about anything I try swamps the machine and locks up MySQL with "too many connections" because it's so damn busy. Can anyone suggest the most efficient way to copy over all the data to the new table with low priority so I don't kill the machine? It's OK if it takes up to around 10 minutes. Thanks... -- 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: Inner Join on MyISAM table
Harini, can you post the output you get from the EXPLAIN command?
That is, type "EXPLAIN " and then the query below, and MySQL will show
you an execution plan for the query that could help identify why it's
taking so long.
Dan
On 9/27/06, Harini Raghavan <[EMAIL PROTECTED]> wrote:
Hi All,
I am using MyISAM storage engine for 2 of the tables in my application
so as to use the full text search features. When I join any one of the
MyISAM table with other INNODB table, the query is very fast. But, if I
do inner join with both theMyISAM table, then the query takes 5 mins to
execute.
Here is the query: The employment_summary and executive_summary tables
are MyISAM type.
select SQL_CALC_FOUND_ROWS distinct(exec1.id) as id, exec1.firstName as
firstName, exec1.middleName as middleName, exec1.lastName as lastName,
exec1.age as age from executive as exec1
inner join (select emp.id as empId, emp.executive_id as execId,
emp.active as active, emp.shortTitle as shortTitle, emp.director as
director
from employment as emp
inner join (select * from employment_summary where ((
MATCH(job_description) AGAINST(' +University +of +Pennsylvania' IN
BOOLEAN MODE as empSummary
on (empSummary.id = emp.id)
inner join (target_company targetComp
inner join company comp on
(targetComp.company_id = comp.id )) on ( emp.company_id =
targetComp.company_id )
inner join (select id from executive_summary where ((
MATCH(firstName) AGAINST(' +John' IN BOOLEAN MODE as execSummary
on execSummary.id = emp.executive_id ) as res
on ( res.active = 1 and res.execId=exec1.id)
order by lastName, firstName, id limit 0,200
Any ideas?
Thanks,
Harini
--
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]
Low priority copy?
I have a very busy 14,000,000 record table. I made a new version of the table, that's more efficient, and now I just need to copy over the data. Just about anything I try swamps the machine and locks up MySQL with "too many connections" because it's so damn busy. Can anyone suggest the most efficient way to copy over all the data to the new table with low priority so I don't kill the machine? It's OK if it takes up to around 10 minutes. Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inner Join on MyISAM table
Hi All,
I am using MyISAM storage engine for 2 of the tables in my application
so as to use the full text search features. When I join any one of the
MyISAM table with other INNODB table, the query is very fast. But, if I
do inner join with both theMyISAM table, then the query takes 5 mins to
execute.
Here is the query: The employment_summary and executive_summary tables
are MyISAM type.
select SQL_CALC_FOUND_ROWS distinct(exec1.id) as id, exec1.firstName as
firstName, exec1.middleName as middleName, exec1.lastName as lastName,
exec1.age as age from executive as exec1
inner join (select emp.id as empId, emp.executive_id as execId,
emp.active as active, emp.shortTitle as shortTitle, emp.director as
director
from employment as emp
inner join (select * from employment_summary where ((
MATCH(job_description) AGAINST(' +University +of +Pennsylvania' IN
BOOLEAN MODE as empSummary
on (empSummary.id = emp.id)
inner join (target_company targetComp
inner join company comp on
(targetComp.company_id = comp.id )) on ( emp.company_id =
targetComp.company_id )
inner join (select id from executive_summary where ((
MATCH(firstName) AGAINST(' +John' IN BOOLEAN MODE as execSummary
on execSummary.id = emp.executive_id ) as res
on ( res.active = 1 and res.execId=exec1.id)
order by lastName, firstName, id limit 0,200
Any ideas?
Thanks,
Harini
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex right left join
Hi. Hope this is the correct list. I'm strugling with a right and left join query: SELECT category.name AS cname, service.name AS tname, service.id AS sid FROM provider LEFT JOIN providerservice ON provider.id = providerservice.provider_id RIGHT JOIN service ON providerservice.service_id = service.id INNER JOIN category ON service.category_id = category.id WHERE providerservice.id IS NULL && provider.id = '1' ORDER BY category.name, service.name As you can see, I want to get all services that the provider with provider.id=1 doesn't have. this worked with Mysql 4.1, but doesn't work with 5.0. Kristian
Trouble compiling C on OS X
First off, I apologize for the fact that I'm not more versed in the inner
workings of GCC and linking libraries and etc, I'm a web developer and I'm
just using C to try to cut down on some speed problems.
This is my little test program:
#include
#include
#include
#include "mysql.h"
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
void exiterr(int exitcode) {
fprintf( stderr, "%s\n", mysql_error(&mysql) );
}
int main( int argc, char *argv[] ) {
uint i = 0;
if(!(mysql_connect(&mysql,"localhost","root","password")))
exiterr(1);
if (mysql_select_db(&mysql,"hallow"))
exiterr(2);
if(mysql_query(&mysql,"SELECT * FROM sections"))
exiterr(3);
if(!(res = mysql_store_result(&mysql)))
exiterr(4);
while((row = mysql_fetch_row(res))) {
for(i=0; i < mysql_num_fields(res); i++)
printf("%s|",row[i]);
printf("");
}
mysql_free_result(res);
mysql_close(&mysql);
return 0;
}
and I'm attempting to compile it with this command:
gcc -I/usr/include concept.c -L/usr/lib/mysql -lmysqlclient -lm -o concept
-lz
I'm quite certain that the header files are in /usr/include. And, what I
assume to be the libraries are in /usr/lib/mysql. (libdbug.a,libmygcc.a,
libmysql.imp, etc...)
This is the error I get when trying to compile:
/usr/bin/ld: Undefined symbols:
_mysql_connect
collect2: ld returned 1 exit status
Any thoughts on why this might be happening? Is there any other information
that I can provide which will help to determine whats wrong?
Thanks in advance for the help,
Tyler McMullen
stored procedures and regex
Hi Can mysql's stored procedures do regex function -- Gregory Machin [EMAIL PROTECTED] www.linuxpro.co.za
RE: AW: Count of children
For us the querying of trees is more important than the speed of writing them. So each time we add a child or change a parent or whatever, we trigger a stored procedure that updates a paths table. Then our query for children is pretty simple: SELECT Node.* FROM Node JOIN Paths P ON Node.id = Paths.descendantID WHERE P.ancestorID = ; So to perform a count I can just do this part without the join: SELECT COUNT(*) FROM Paths WHERE Paths.ancestorID = ; Our system is structured using a sort of GoF composite parent so some nodes can be Groups and others can only be leaves. If I want to return children nodes that are not leaves I can do this: SELECT Node.* FROM Node JOIN Paths P ON Node.id = Paths.childID WHERE P.ancestorID = AND Node.isLeaf = false; The Paths table is pretty simple; just "descendantID" and "ancestorID" columns that make up a composite primary key. Some people also add another column called "depth" as this can make recalculating the tree easier. R. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 8:20 AM To: André Hänsel Cc: mysql@lists.mysql.com Subject: Re: AW: Count of children André With an edge list, the solution entails recursion, so you need either an sproc or application proc. With a nested sets model, the count is dead simple. If the id of the target row is N, and the left & right node columns are named leftedge and rightedge, the query is SELECT COUNT(t2.id) FROM tbl t1 JOIN tbl t2 ON t2.leftedge > t1.leftedge AND t2.leftedge < t1.rightedge WHERE t1.id=N; PB - André Hänsel wrote: I will use any model that is suitable. ;) I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them. -Ursprüngliche Nachricht- Von: Peter Brawley [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 27. September 2006 16:49 An: André Hänsel Cc: mysql@lists.mysql.com Betreff: Re: Count of children André, I want the count of all sub-entries for a specific entry. Depends on the model you are using--edge list or nested sets? PB - André Hänsel wrote: I have a table with id and parent_id. I want the count of all sub-entries for a specific entry. I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem. I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts. Is there a smart solution/best practice for my problem? Now I can't think of another sentence starting with an i. ;-) Best regards, André -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Count of children
André With an edge list, the solution entails recursion, so you need either an sproc or application proc. With a nested sets model, the count is dead simple. If the id of the target row is N, and the left & right node columns are named leftedge and rightedge, the query is SELECT COUNT(t2.id) FROM tbl t1 JOIN tbl t2 ON t2.leftedge > t1.leftedge AND t2.leftedge < t1.rightedge WHERE t1.id=N; PB - André Hänsel wrote: I will use any model that is suitable. ;) I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them. -Ursprüngliche Nachricht- Von: Peter Brawley [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 27. September 2006 16:49 An: André Hänsel Cc: mysql@lists.mysql.com Betreff: Re: Count of children André, I want the count of all sub-entries for a specific entry. Depends on the model you are using--edge list or nested sets? PB - André Hänsel wrote: I have a table with id and parent_id. I want the count of all sub-entries for a specific entry. I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem. I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts. Is there a smart solution/best practice for my problem? Now I can't think of another sentence starting with an i. ;-) Best regards, André -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Count of children
I've found this website to be extremely helpful: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html Donna DeVaudreuil André Hänsel <[EMAIL PROTECTED]> 09/27/2006 10:55 AM To <[EMAIL PROTECTED]> cc Subject AW: Count of children I will use any model that is suitable. ;) I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them. > -Ursprüngliche Nachricht- > Von: Peter Brawley [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 27. September 2006 16:49 > An: André Hänsel > Cc: mysql@lists.mysql.com > Betreff: Re: Count of children > > André, > > >I want the count of all sub-entries for a specific entry. > > Depends on the model you are using--edge list or nested sets? > > PB > > - > > André Hänsel wrote: > > I have a table with id and parent_id. > > I want the count of all sub-entries for a specific entry. > > > > I found several documents about working with graphs/trees > in MySQL but I > > could not find a solution for my problem. > > > > I can imagine two possibilities, but one is memory > intensive and the other > > one creates load on updates. > > The first is, that I select all entries and then use a > procedural language > > to determine recursively whether an node is a sub-node of > the specific node. > > The second is, that I store the sub-node count with each > node and when I do > > an insert, I walk the tree upwards and increment the node-counts. > > > > Is there a smart solution/best practice for my problem? > > > > Now I can't think of another sentence starting with an i. ;-) > > > > Best regards, > > André > > > > > > > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.407 / Virus Database: 268.12.9/457 - Release > Date: 9/26/2006 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Count of children
By default it doesn't, but you can change that behaviour. Quoting from this page http://dev.mysql.com/doc/refman/5.0/en/stored- procedures.html : Recursive stored procedures are disabled by default, but can be enabled on the server by setting the max_sp_recursion_depth server system variable to a nonzero value. See Section 5.2.3, “System Variables”, for more information. There is also a very thorough article discussing stored procedures in MySQL which gives an example of tree traversal here: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html Douglas Sims [EMAIL PROTECTED] On Sep 27, 2006, at 9:40 AM, João Cândido de Souza Neto wrote: You must do that by a some language script, unfortunatly mysql is no recursive. ""André Hänsel"" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] Sorry, my english sentence was imprecise. :) I want the count of all sub-entries AND THEIR sub-sub-entries. Example: A / \ B C / \ D E \ F So I want to know that C has 3 sub-nodes. -Ursprüngliche Nachricht- Von: Rob Desbois [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 27. September 2006 15:48 An: André Hänsel; mysql@lists.mysql.com Betreff: re: Count of children André, Your sentence 'I want the count of all sub-entries for a specific entry' converts straight into SQL: 'I want' SELECT the count of all entries COUNT(*) FROM myTable with a specific parent WHERE parent_id = 5 You've missed one of the major benefits of SQL - it's designed to read like an English sentence! --Rob _ _ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email _ _ -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Count of children
I will use any model that is suitable. ;) I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them. > -Ursprüngliche Nachricht- > Von: Peter Brawley [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 27. September 2006 16:49 > An: André Hänsel > Cc: mysql@lists.mysql.com > Betreff: Re: Count of children > > André, > > >I want the count of all sub-entries for a specific entry. > > Depends on the model you are using--edge list or nested sets? > > PB > > - > > André Hänsel wrote: > > I have a table with id and parent_id. > > I want the count of all sub-entries for a specific entry. > > > > I found several documents about working with graphs/trees > in MySQL but I > > could not find a solution for my problem. > > > > I can imagine two possibilities, but one is memory > intensive and the other > > one creates load on updates. > > The first is, that I select all entries and then use a > procedural language > > to determine recursively whether an node is a sub-node of > the specific node. > > The second is, that I store the sub-node count with each > node and when I do > > an insert, I walk the tree upwards and increment the node-counts. > > > > Is there a smart solution/best practice for my problem? > > > > Now I can't think of another sentence starting with an i. ;-) > > > > Best regards, > > André > > > > > > > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.407 / Virus Database: 268.12.9/457 - Release > Date: 9/26/2006 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count of children
André, I want the count of all sub-entries for a specific entry. Depends on the model you are using--edge list or nested sets? PB - André Hänsel wrote: I have a table with id and parent_id. I want the count of all sub-entries for a specific entry. I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem. I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts. Is there a smart solution/best practice for my problem? Now I can't think of another sentence starting with an i. ;-) Best regards, André -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count of children
You must do that by a some language script, unfortunatly mysql is no recursive. ""André Hänsel"" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] Sorry, my english sentence was imprecise. :) I want the count of all sub-entries AND THEIR sub-sub-entries. Example: A / \ B C / \ D E \ F So I want to know that C has 3 sub-nodes. > -Ursprüngliche Nachricht- > Von: Rob Desbois [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 27. September 2006 15:48 > An: André Hänsel; mysql@lists.mysql.com > Betreff: re: Count of children > > André, > > Your sentence 'I want the count of all sub-entries for a > specific entry' converts straight into SQL: > > 'I want' > SELECT > the count of all entries > COUNT(*) FROM myTable > with a specific parent > WHERE parent_id = 5 > > You've missed one of the major benefits of SQL - it's > designed to read like an English sentence! > --Rob > > > __ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > __ > > -- > 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: AW: Count of children
André > Sorry, my english sentence was imprecise. :) I want the count of all > sub-entries AND THEIR sub-sub-entries. Example: A / \ B C / \ D E \ F So I want to know that C has 3 sub-nodes. Aha! I thought it seemed like an easy question ;-) As far as i know, the only way to do this is one of the methods you suggested - recursively adding up, or storing a count which is updated on insert/delete operations. --Rob > -Ursprüngliche Nachricht- > Von: Rob Desbois [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 27. September 2006 15:48 > An: André Hänsel; mysql@lists.mysql.com > Betreff: re: Count of children > > André, > > Your sentence 'I want the count of all sub-entries for a > specific entry' converts straight into SQL: > > 'I want' > SELECT > the count of all entries > COUNT(*) FROM myTable > with a specific parent > WHERE parent_id = 5 > > You've missed one of the major benefits of SQL - it's > designed to read like an English sentence! > --Rob > > > __ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > __ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ < -- Original Message -- FROM: André Hänsel<[EMAIL PROTECTED]> TO:"'Rob Desbois'" <[EMAIL PROTECTED]> DATE: Wed, 27 Sep 2006 16:00:25 +0200 SUBJECT: AW: Count of children Sorry, my english sentence was imprecise. :) I want the count of all sub-entries AND THEIR sub-sub-entries. Example: A / \ B C / \ D E \ F So I want to know that C has 3 sub-nodes. > -Ursprüngliche Nachricht- > Von: Rob Desbois [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 27. September 2006 15:48 > An: André Hänsel; mysql@lists.mysql.com > Betreff: re: Count of children > > André, > > Your sentence 'I want the count of all sub-entries for a > specific entry' converts straight into SQL: > > 'I want' > SELECT > the count of all entries > COUNT(*) FROM myTable > with a specific parent > WHERE parent_id = 5 > > You've missed one of the major benefits of SQL - it's > designed to read like an English sentence! > --Rob > > > __ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > __ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Count of children
Sorry, my english sentence was imprecise. :) I want the count of all sub-entries AND THEIR sub-sub-entries. Example: A / \ B C / \ D E \ F So I want to know that C has 3 sub-nodes. > -Ursprüngliche Nachricht- > Von: Rob Desbois [mailto:[EMAIL PROTECTED] > Gesendet: Mittwoch, 27. September 2006 15:48 > An: André Hänsel; mysql@lists.mysql.com > Betreff: re: Count of children > > André, > > Your sentence 'I want the count of all sub-entries for a > specific entry' converts straight into SQL: > > 'I want' > SELECT > the count of all entries > COUNT(*) FROM myTable > with a specific parent > WHERE parent_id = 5 > > You've missed one of the major benefits of SQL - it's > designed to read like an English sentence! > --Rob > > > __ > This email has been scanned by the MessageLabs Email Security System. > For more information please visit http://www.messagelabs.com/email > __ > > -- > 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: Count of children
André, Your sentence 'I want the count of all sub-entries for a specific entry' converts straight into SQL: 'I want' SELECT the count of all entries COUNT(*) FROM myTable with a specific parent WHERE parent_id = 5 You've missed one of the major benefits of SQL - it's designed to read like an English sentence! --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count of children
You can use it: SELECT parent_id, count( * ) FROM table WHERE parent_id =1 GROUP BY parent_id It´ll works fine. ""André Hänsel"" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] I have a table with id and parent_id. I want the count of all sub-entries for a specific entry. I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem. I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts. Is there a smart solution/best practice for my problem? Now I can't think of another sentence starting with an i. ;-) Best regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: transitioning from ms sql
Hi Arjun, Check out the MySQL Migration Toolkit and some other tools available at http://www.mysql.com/products/tools/migration-toolkit/ and http://dev.mysql.com/downloads/gui-tools/5.0.html. Do a test migration and some testing on the migrated dataset, and you can then probably set up a plan for the transition. Sincerely, Raj Mehrotra hccs - Experts in Healthcare Learning [EMAIL PROTECTED] -Original Message- From: Arjun Bhandari [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 12:11 AM To: mysql@lists.mysql.com Subject: transitioning from ms sql Hi, I have been using MS SQL for the last one year, however would not like to transition to mysql. At the first glance it looks very different from ms sql and the tools are also different. can someone tell me if there is any document which explains the equivalence and how i could port a lot of my queries, tables, views and stored procedures to my sql from ms sql. Best Regards, Arjun ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Any unauthorized use of the information contained in this email or its attachments is prohibited. If this email is received in error, please contact the sender and delete the material from your computer systems. Do not use, copy, or disclose the contents of this email or any attachments. Abu Dhabi Investment Authority (ADIA) accepts no responsibility for the content of this email to the extent that the same consists of statements and opinions made which are the senders own and not made on behalf of ADIA. Nor does ADIA accept any liability for any errors or omissions in the content of this email caused by electronic and technical failures. Although ADIA has taken reasonable precautions to ensure that no viruses are present in this email, ADIA accepts no responsibility for any loss or damage arising from the use of this email or its attachments. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: fields separator
I'm used to hammering all kinds of data into MySQL. Often I do it with a PHP (or Perl) script that handles this kind of thing well. However, often my data originates on a PC (don't ask), so I run it through MS Excel. Excel (and OpenOffice, I presume) has good tools for parsing text files, and can then export to a tab-separated file. Ugly, but as I said my data comes in all kinds of nasty formats, such as web pages. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 27, 2006 3:20 AM > To: mysql@lists.mysql.com > Subject: fields separator > > hi, everyone! > I have a text file like this: > 10:10:00 0 0 1 99 > 10:20:00 0 0 1 99 > 10:40:00 11 3 4 83 > 11:00:00 1 1 2 97 > 11:05:00 2 1 1 96 > > I need to load this file into a table, but I cannot figure > out the syntax of LOAD DATA INFILE command. As you can see, > the fields are separated by a variable number of blank spaces > (not tabs). How can I do this? > > > -- > 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: Re: fields separators
Daniel, is it that the columns in the file are separated by a variable
number of spaces? Or is it that the columns are fixed-width,
therefore the number of spaces varies with length of data? If the
file is fixed-width, the SQL below ought to work pretty well.
Either way, your situation is not too well supported by LOAD DATA
INFILE, unfortunately.
I'd suggest an intermediate step: read complete lines from the file,
inserting them as text or varchar into a temp table. Then run an
INSERT ... SELECT to move the data from the temp table to your real
table.
- CREATE TABLE temp_data (dataline VARCHAR(255));
- LOAD DATA INFILE 'data.txt' INTO TABLE temp_data
FIELDS TERMINATED BY '' ENCLOSED BY '';
INSERT INTO test (server_time, usr, sys, wio, idle)
SELECT
TRIM( SUBSTRING(dataline, 1, 8) ),
TRIM( SUBSTRING(dataline, 9, 8) ),
TRIM( SUBSTRING(dataline, 17, 8) ),
TRIM( SUBSTRING(dataline, 25, 8) ),
TRIM( SUBSTRING(dataline, 33, 8) )
FROM temp_data;
TRUNCATE TABLE temp_data;
HTH,
Dan
On 27 Sep 2006 08:49:40 -, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> >Did you try using a space (' ') as the separator? Did you get an error?
And at 7:41 + 27/9/06, [EMAIL PROTECTED] wrote:
>Yes, I did.
...
>So, I need to specify somehow that the fields are delimited by any
>number of spaces...
One answer of course is grep. However, as far as I can determine,
MySQL can only apply grep in the context of a LIKE clause, So...
Do it on your text file before importing, if you have a text editor
that can handle regular expressions. Just search for / +/ and replace
with ' '. Then import using a single ' ' as the 'enclosed by' string.
--
Cheers... Chris
Due to the nature of my application, this would be rather difficult. I would
prefer to import this files directly to mysql. Any other suggestions?
Thanks in advance!
--
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]
Count of children
I have a table with id and parent_id. I want the count of all sub-entries for a specific entry. I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem. I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts. Is there a smart solution/best practice for my problem? Now I can't think of another sentence starting with an i. ;-) Best regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Design flaw for unique key
My application contains a table `event` which is essentially a historical log. Currently it contains a UNIQUE KEY on three fields - the location which generated the event `location_id`, the timestamp the event was generated `timestamp`, and the type of event `type`. I have discovered that this uniqueness guarantee is invalid: some event types have their own type-specific data which is stored in external tables for the event, and this may be required in addition before uniqueness can be guaranteed. The DB design and/or application need to be modified but I'm not sure of the appropriate solution. I must continue to prevent insertion of duplicate events (however 'duplicate' is defined for a particular event type). Possible solutions I have come up with are: 1: Remove the UNIQUE property of the key (I still want it for search indexing) and perform a check for duplicates before INSERT by doing a SELECT for anything that matches the current event. If it's already there, don't insert it. This seems rather crude, and obviously generates much more demand on the DB engine. 2: Create an additional field in `event` which contains any data additional to the current key required to uniquely identify the event. When inserting an event with type-specific data, this field can be derived from teh extra data required for uniqueness, and used in the INSERT - a duplicate key error would then be correct. This still seems a little crude in that the data is replicating that in another table, albeit for a different purpose. Are there any other possible solutions people can come up with? Has anyone had a similar issue before? Many thanks in advance, --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fields separators
> >Did you try using a space (' ') as the separator? Did you get an error?
And at 7:41 + 27/9/06, [EMAIL PROTECTED] wrote:
>Yes, I did.
...
>So, I need to specify somehow that the fields are delimited by any
>number of spaces...
One answer of course is grep. However, as far as I can determine,
MySQL can only apply grep in the context of a LIKE clause, So...
Do it on your text file before importing, if you have a text editor
that can handle regular expressions. Just search for / +/ and replace
with ' '. Then import using a single ' ' as the 'enclosed by' string.
--
Cheers... Chris
Due to the nature of my application, this would be rather difficult. I would
prefer to import this files directly to mysql. Any other suggestions?
Thanks in advance!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
design: save a decision-tree in a db
Hello MySQL Fans First of all, this is not about decision-trees and datamining. And it is not even MySQL-specific. I would like to use a decision-tree to save our business-rules. Rules are existing and extended frequently. Due to the complexness a hierarchical tree would fit in. (e.g. The Nested Set Model on http://dev.mysql.com/tech-resources/articles/hierarchical-data.html) As I understood a decision-tree there are decision-nodes and leaf-nodes. (http://dms.irb.hr/tutorial/images/dtree_image.gif) Until now, I try to have a "check" and a "set" field in each node, either-way NULL, if it is a decision or a leaf node. But I'm not very happy about it. Does someone have experience to share about the table design and/or queries? Thanks a lot! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
key_buffer_size - need help
Hlo, We have all our tables as Innodb type. As I understand the variable key_buffer_size is used only for MyISAM tables. Currently we have this var set to 256M on a 4GB RAM machine. Only the Temporary tables created using (CREATE TEMPORARY TABLE) will be of MyISAM type. And there are a lot of temporary tables being created in our system. What is an optimal value to the key_buffer_size variable in such a case? Ratheesh Bhat K J
Re: fields separators
Pe 27 Sep 2006, la 10:29, Chris <[EMAIL PROTECTED]> a scris:
>Did you try using a space (' ') as the separator? Did you get an error?
And at 7:41 + 27/9/06, [EMAIL PROTECTED] wrote:
Yes, I did.
...
So, I need to specify somehow that the fields are delimited by any
number of spaces...
One answer of course is grep. However, as far as I can determine,
MySQL can only apply grep in the context of a LIKE clause, So...
Do it on your text file before importing, if you have a text editor
that can handle regular expressions. Just search for / +/ and replace
with ' '. Then import using a single ' ' as the 'enclosed by' string.
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Whenever I watch TV and see those poor starving kids all over the
world, I can't help but cry. I mean I'd love to be skinny like
that, but not with all those flies and death and stuff.
-- Mariah Carey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Stat - Help required
Ratheesh K J wrote: I have checked the queries. They look quite ok. We have a lot lot of CREATE TEMPORARY TABLE running every second. How else can tmp tables be created? What is the exact relation between missing index and tmp tables being created. Do you mean to say in sorting? Sorting, grouping, table joins, subqueries (possibly).. - Original Message - From: "Chris" <[EMAIL PROTECTED]> To: "Ratheesh K J" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, September 27, 2006 12:50 PM Subject: Re: Mysql Stat - Help required Ratheesh K J wrote: Hlo, I got this Stat of MySQL. I want to know is there something to worry about, especially the number of temp tables. That looks like a sign of missing indexes. Temporary tables shouldn't be created that often if your queries are indexed properly, even though they're not showing up as "slow queries". It'll be a pain but you could write a script to go through your queries and 'explain' then and see which ones are using temp tables and see whether they need additional indexes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Stat - Help required
I have checked the queries. They look quite ok. We have a lot lot of CREATE TEMPORARY TABLE running every second. How else can tmp tables be created? What is the exact relation between missing index and tmp tables being created. Do you mean to say in sorting? - Original Message - From: "Chris" <[EMAIL PROTECTED]> To: "Ratheesh K J" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, September 27, 2006 12:50 PM Subject: Re: Mysql Stat - Help required Ratheesh K J wrote: Hlo, I got this Stat of MySQL. I want to know is there something to worry about, especially the number of temp tables. That looks like a sign of missing indexes. Temporary tables shouldn't be created that often if your queries are indexed properly, even though they're not showing up as "slow queries". It'll be a pain but you could write a script to go through your queries and 'explain' then and see which ones are using temp tables and see whether they need additional indexes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: odd behaviour with auto_increment
On 9/26/2006 4:02 PM, Dan Buettner wrote: > Jorrit, it's a known behavior, not a bug. > > Recent versions of MySQL will, when given a zero (0) as a value for an > auto incrementing identity column, simply fill in the next auto > incrementing value ... unless you flip a switch to specifically tell > it not to do that ... see: > http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html and look > for NO_AUTO_VALUE_ON_ZERO > > HTH, > Dan > > Dan, That explains it! I see the NO_AUTO_VALUE_ON_ZERO in the dump as well. Thanks! Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fields separators
Pe 27 Sep 2006, la 10:29, Chris <[EMAIL PROTECTED]> a scris:
>
>[EMAIL PROTECTED] wrote:
>> hi, everyone!
>> I have a text file like this:
>> 10:10:00 0 0 1 99
>> 10:20:00 0 0 1 99
>> 10:40:00 11 3 4 83
>> 11:00:00 1 1 2 97
>> 11:05:00 2 1 1 96
>>
>> I need to load this file into a table, but I cannot figure out the syntax of
>> LOAD DATA INFILE command. As you can see, the fields are separated by a
>> variable number of blank spaces (not tabs). How can I do this?
>
>Did you try using a space (' ') as the separator? Did you get an error?
Yes, I did.
load data infile '/home/kido/data.txt' into table test fields enclosed by '
'(server_time,usr,sys,wio,idle);
the result was that first field (e.g. 10:10:00) was populated correctly, while
the others fileds were populated with NULL values.
mysql> select * from test;
+-+-+--+--+--+--+
| id | server_time | usr | sys | wio | idle |
+-+-+--+--+--+--+
| 399 | 06:05:00| NULL | NULL | NULL | NULL |
| 400 | 06:06:00| NULL | NULL | NULL | NULL |
I also tried:
load data infile '/home/kido/data.txt' into table test fields terminated by '
'(server_time,usr,sys,wio,idle);
and the corresponding fileds were populated with 0 (zerros):
| 505 | 11:10:00|0 |0 |0 |0 |
| 506 | 11:20:00|0 |0 |0 |0 |
| 507 | 11:40:00|0 |0 |0 |0 |
| 508 | 12:00:00|0 |0 |0 |0 |
So, I need to specify somehow that the fields are delimited by any number of
spaces...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fields separator
[EMAIL PROTECTED] wrote:
hi, everyone!
I have a text file like this:
10:10:00 0 0 1 99
10:20:00 0 0 1 99
10:40:00 11 3 4 83
11:00:00 1 1 2 97
11:05:00 2 1 1 96
I need to load this file into a table, but I cannot figure out the syntax of
LOAD DATA INFILE command. As you can see, the fields are separated by a
variable number of blank spaces (not tabs). How can I do this?
Did you try using a space (' ') as the separator? Did you get an error?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transitioning from ms sql
On Wed, 2006-09-27 at 13:00 +0800, Wai-Sun Chia wrote: > On 9/27/06, Arjun Bhandari <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I have been using MS SQL for the last one year, however would not like to > > Huh? If you would NOT like to transition to MySQL, then why are you > asking all these stuff? Stop picking on the OP for a typo. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Stat - Help required
Ratheesh K J wrote: Hlo, I got this Stat of MySQL. I want to know is there something to worry about, especially the number of temp tables. That looks like a sign of missing indexes. Temporary tables shouldn't be created that often if your queries are indexed properly, even though they're not showing up as "slow queries". It'll be a pain but you could write a script to go through your queries and 'explain' then and see which ones are using temp tables and see whether they need additional indexes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fields separator
hi, everyone! I have a text file like this: 10:10:00 0 0 1 99 10:20:00 0 0 1 99 10:40:00 11 3 4 83 11:00:00 1 1 2 97 11:05:00 2 1 1 96 I need to load this file into a table, but I cannot figure out the syntax of LOAD DATA INFILE command. As you can see, the fields are separated by a variable number of blank spaces (not tabs). How can I do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Stat - Help required
Hlo, I got this Stat of MySQL. I want to know is there something to worry about, especially the number of temp tables. Key Reads/Key Read Requests = 0.007094 (Cache hit = 99.992906%) Key Writes/Key Write Requests = 0.239130 Connections/second = 8.741 (/hour = 31467.279) KB received/second = 0.730 (/hour = 2628.283) KB sent/second = 1.795 (/hour = 6460.812) Temporary Tables Created/second = 0.914 (/hour = 3290.991) Opened Tables/second = 0.616 (/hour = 2217.483) Slow Queries/second = 0.014 (/hour = 48.639) % of slow queries = 0.032% Queries/second = 41.838 (/hour = 150618.094) We have the tmp_table_size variable set to 64M. And we saw that there are many temp tables created on disk in about 3 - 5 seconds. How do we analyze the optimum value for the tmp_table_size variable? Thanks, Ratheesh Bhat K J
