Optimizing GROUP BY and ORDER BY
I have a query: SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE ( Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP BY Country ORDER BY Cnt DESC LIMIT 8 that gets the top 8 non-US countries from the properties table. There is about 500,000 rows in the properties table. This is a costly query: ++-++---+---+-+-+--+---+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+--+---+---+ | 1 | SIMPLE | properties | range | Country | Country | 7 | NULL | 74602 | Using where; Using index; Using temporary; Using filesort | ++-++---+---+-+-+--+---+---+ 1 row in set (0.00 sec) Any ideas on how to get rid of the "Using temporary; Using filesort" or do this in a better way with PHP? Thanks! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing GROUP BY and ORDER BY
On Fri, Jul 25, 2008 at 12:27 AM, Michael Stearne <[EMAIL PROTECTED]> wrote: > I have a query: > > SELECT Country, COUNT( Country ) AS Cnt > FROM properties WHERE ( > Country != 'USA' AND > Country != 'US' AND > Country != 'Unit' AND > Country != 'United States' > AND Country != ' ' > AND Country IS NOT NULL ) > GROUP BY Country > ORDER BY Cnt > DESC LIMIT > > This is a costly query I suggest that this is not a well normalized. I suggest that at a minium you should be using a 'Country' which would include a distinct entry for each country. If such a table exists a column could be added which would store a count of number of rows in the Properties table that reference the record in the Country table. This count could be maintained through your application, or via triggers if you are using MySQL 5.0 > . -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I (can I) use aggregate functions inside a select
I may be approaching this all wrong, but I need to know a percentage of total sales within a select statement. So I can do something like this: Select company, state, sales, sum(sales) / sales as percent >From Sales Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I (can I) use aggregate functions inside a select
yes, u can use the below sql. regards anandkl On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > I may be approaching this all wrong, but I need to know a percentage of > total sales within a select statement. > > So I can do something like this: > Select company, state, sales, sum(sales) / sales as percent > From Sales > > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network EngineerSafe Data, Inc. > (910) 285-7200 [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
RE: How do I (can I) use aggregate functions inside a select
I get: Error Code : 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 10:37 AM To: David Ruggles Cc: mysql Subject: Re: How do I (can I) use aggregate functions inside a select yes, u can use the below sql. regards anandkl On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > I may be approaching this all wrong, but I need to know a percentage of > total sales within a select statement. > > So I can do something like this: > Select company, state, sales, sum(sales) / sales as percent > From Sales > > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network EngineerSafe Data, Inc. > (910) 285-7200 [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I (can I) use aggregate functions inside a select
David Ruggles wrote: > I may be approaching this all wrong, but I need to know a percentage of > total sales within a select statement. > > So I can do something like this: > Select company, state, sales, sum(sales) / sales as percent > From Sales > > mmh, you want sum(sales where company="foo")/sum(sales) you can do this only when doing 2 queries and storing al least one result re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I (can I) use aggregate functions inside a select
Hi David, Try Select company, state, sales, sum(sales) / sales as percent >From Sales GROUP BY company, state; Not sure if you always want to group by state; if each company exists in only one state then the group by is irrelevant, if not then it will give you the by-state breakdown. On Fri, 2008-07-25 at 10:53 -0400, David Ruggles wrote: > I get: > Error Code : 1140 > Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is > illegal if there is no GROUP BY clause > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network Engineer Safe Data, Inc. > (910) 285-7200[EMAIL PROTECTED] > > > > -Original Message- > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: Friday, July 25, 2008 10:37 AM > To: David Ruggles > Cc: mysql > Subject: Re: How do I (can I) use aggregate functions inside a select > > > yes, u can use the below sql. > > regards > anandkl > > > On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > > > I may be approaching this all wrong, but I need to know a percentage of > > total sales within a select statement. > > > > So I can do something like this: > > Select company, state, sales, sum(sales) / sales as percent > > From Sales > > > > > > Thanks, > > > > David Ruggles > > CCNA MCSE (NT) CNA A+ > > Network EngineerSafe Data, Inc. > > (910) 285-7200 [EMAIL PROTECTED] > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > No virus found in this incoming message. > Checked by AVG - http://www.avg.com > Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 > 6:51 AM > > > -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I (can I) use aggregate functions inside a select
>-Original Message- >From: David Ruggles [mailto:[EMAIL PROTECTED] >Sent: Friday, July 25, 2008 10:53 AM >To: 'mysql' >Subject: RE: How do I (can I) use aggregate functions inside a select > >I get: >Error Code : 1140 >Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns >is >illegal if there is no GROUP BY clause > [JS] I think you can do what you want with a little bit of modification, but I'm not sure exactly what you want. If what you want is to have the sales % for each company, for example, you could use SELECT MIN(company) AS comp, MIN(state) AS st, SUM(sales) AS comp_tot, SUM(sales) * 100 / (SELECT SUM(sales) FROM sales_table)) AS pct_sales FROM sales_table GROUP BY company, state; I tested a simplified case, but I believe this will work. The use of the MIN function is because you need a group function, and because the MIN and MAX of company is the same through the entire group it works just fine. >Thanks, > >David Ruggles >CCNA MCSE (NT) CNA A+ >Network Engineer Safe Data, Inc. >(910) 285-7200 [EMAIL PROTECTED] > > > >-Original Message- >From: Ananda Kumar [mailto:[EMAIL PROTECTED] >Sent: Friday, July 25, 2008 10:37 AM >To: David Ruggles >Cc: mysql >Subject: Re: How do I (can I) use aggregate functions inside a select > > >yes, u can use the below sql. > >regards >anandkl > > >On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: >> >> I may be approaching this all wrong, but I need to know a percentage >of >> total sales within a select statement. >> >> So I can do something like this: >> Select company, state, sales, sum(sales) / sales as percent >> From Sales >> >> >> Thanks, >> >> David Ruggles >> CCNA MCSE (NT) CNA A+ >> Network EngineerSafe Data, Inc. >> (910) 285-7200 [EMAIL PROTECTED] >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >http://lists.mysql.com/[EMAIL PROTECTED] >> >> >No virus found in this incoming message. >Checked by AVG - http://www.avg.com >Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: >7/25/2008 >6:51 AM > > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: How do I (can I) use aggregate functions inside a select
Sum() is driven by the group by. I need it to be equivalent to this: Select company, state, sales, sales / (select sum(sales) from sales) as percent from sales Which of course I could just use that, but the select I'm actually working with isn't that simple and if there was some way to do what I asked below, that would be much easier. I've gathered that is really the only option when it comes down to it so I'm working on writing my select using the above syntax. Thanks all! Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Ian Simpson [mailto:[EMAIL PROTECTED] Sent: Friday, July 25, 2008 11:15 AM To: David Ruggles Cc: 'mysql' Subject: RE: How do I (can I) use aggregate functions inside a select Hi David, Try Select company, state, sales, sum(sales) / sales as percent >From Sales GROUP BY company, state; Not sure if you always want to group by state; if each company exists in only one state then the group by is irrelevant, if not then it will give you the by-state breakdown. On Fri, 2008-07-25 at 10:53 -0400, David Ruggles wrote: > I get: > Error Code : 1140 > Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is > illegal if there is no GROUP BY clause > > Thanks, > > David Ruggles > CCNA MCSE (NT) CNA A+ > Network Engineer Safe Data, Inc. > (910) 285-7200[EMAIL PROTECTED] > > > > -Original Message- > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: Friday, July 25, 2008 10:37 AM > To: David Ruggles > Cc: mysql > Subject: Re: How do I (can I) use aggregate functions inside a select > > > yes, u can use the below sql. > > regards > anandkl > > > On 7/25/08, David Ruggles <[EMAIL PROTECTED]> wrote: > > > > I may be approaching this all wrong, but I need to know a percentage of > > total sales within a select statement. > > > > So I can do something like this: > > Select company, state, sales, sum(sales) / sales as percent > > From Sales > > > > > > Thanks, > > > > David Ruggles > > CCNA MCSE (NT) CNA A+ > > Network EngineerSafe Data, Inc. > > (910) 285-7200 [EMAIL PROTECTED] > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > > > No virus found in this incoming message. > Checked by AVG - http://www.avg.com > Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 > 6:51 AM > > > -- Ian Simpson System Administrator MyJobGroup No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.5.6/1572 - Release Date: 7/25/2008 6:51 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing GROUP BY and ORDER BY
On Fri, Jul 25, 2008 at 12:35 PM, Arthur Fuller <[EMAIL PROTECTED]> wrote: > ORDER BY implies a sort of the result set. I don't think there is any way > around that. I guess so. What I am doing is to just run the query once per day and store the results in memcache. Michael > > Arthur > > On Fri, Jul 25, 2008 at 4:27 AM, Michael Stearne <[EMAIL PROTECTED]> > wrote: >> >> I have a query: >> >> SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE ( >> Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country >> != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP >> BY Country ORDER BY Cnt DESC LIMIT 8 >> >> that gets the top 8 non-US countries from the properties table. There >> is about 500,000 rows in the properties table. >> >> This is a costly query: >> >> ++-++---+---+-+-+--+---+---+ >> | id | select_type | table | type | possible_keys | key | >> key_len | ref | rows | Extra >>| >> >> ++-++---+---+-+-+--+---+---+ >> | 1 | SIMPLE | properties | range | Country | Country | 7 >> | NULL | 74602 | Using where; Using index; Using temporary; Using >> filesort | >> >> ++-++---+---+-+-+--+---+---+ >> 1 row in set (0.00 sec) > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: How do I (can I) use aggregate functions inside a select
Why? Because it's Friday and I'm feeling silly :) mysql> SELECT * FROM sales; +--+---++ | company | state | sales | +--+---++ | ABC | AZ| 140.01 | | XYZ | AZ| 17.76 | | ABC | NY| 123.45 | | XYZ | NY| 123.00 | | Widgets Inc. | NY| 45.09 | | ABC | NY| 23.73 | | Widgets Inc. | CA| 97.30 | +--+---++ 7 rows in set (0.00 sec) mysql> SELECT 'each' AS 'By', company AS Company, state AS State, LPAD( SUM(sales), 9, ' ' ) AS Sales, -> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' ' ) AS Percent, -> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) ) -> AS Graph -> FROM sales -> GROUP BY company, state -> UNION -> SELECT ' company', company, '', LPAD( SUM(sales), 9, ' ' ), -> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' ' ), -> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) ) -> FROM sales -> GROUP BY company -> UNION -> SELECT ' state', '', state, LPAD( SUM(sales), 9, ' ' ), -> LPAD( FORMAT( SUM(sales)*100/(SELECT SUM(sales) FROM sales),2), 9, ' ' ), -> REPEAT( '*', SUM(sales)*50/(SELECT SUM(sales) FROM sales) ) -> FROM sales -> GROUP BY state -> UNION -> SELECT ' -', '-', '-', REPEAT( '-', 9 ), REPEAT( '-', 9 ), REPEAT( ' ', 50 ) -> UNION -> SELECT '-', '-', '-', REPEAT( '-', 9 ), REPEAT( '-', 9 ), REPEAT( ' ', 50 ) -> ORDER BY 1,2 -> ; +---+--+---+---+---++ | By| Company | State | Sales | Percent | Graph | +---+--+---+---+---++ | company | ABC | |287.19 | 50.35 | * | | company | Widgets Inc. | |142.39 | 24.97 | | | company | XYZ | |140.76 | 24.68 | | | -| -| - | - | - | | | state| | AZ|157.77 | 27.66 | ** | | state| | CA| 97.30 | 17.06 | * | | state| | NY|315.27 | 55.28 | | | - | -| - | - | - | | | each | ABC | AZ|140.01 | 24.55 | | | each | ABC | NY|147.18 | 25.81 | * | | each | Widgets Inc. | CA| 97.30 | 17.06 | * | | each | Widgets Inc. | NY| 45.09 | 7.91 | | | each | XYZ | AZ| 17.76 | 3.11 | ** | | each | XYZ | NY|123.00 | 21.57 | *** | +---+--+---+---+---++ 14 rows in set, 2 warnings (0.01 sec) mysql> -- Just my 0.0002 million dollars worth, Shawn "Where there's duct tape, there's hope." "Perl is the duct tape of the Internet." Hassan Schroeder, Sun's first webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding index to Memory table LOSES ROWS!
I have a memory table with a few hundred thousand rows. As soon as I add an index to the table, it loses 75% of the rows! No error is produced in the log file or on the client machine. The rows 'A' to 'E' are present, but rows 'F' to 'Z' are missing. I have max_heap_table_size=1024M and tmp_table_size=1024M which should be plenty of RAM. 1) Why does adding an index lose the rows? 2) Why wasn't an error reported? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bulk INSERT performance question
List, I am bulk inserting a huge amount of data into a MyISAM table (a wikipedia page dump). Before I issued SOURCE filename.sql; I did an ALTER TABLE page DISABLE KEYS; LOCK TABLES page WRITE; The dump consists of about 1,200 bulk INSERT statements with roughly 12,000 tuples each. For the first hour or so it performed pretty good, taking about 5 seconds for each INSERT and using all the CPU it could get. But a while later time consumption increased up to 10 minutes. In the same time, mysqld is barely using any CPU (less than 2%), there is no significant I/O going on, and there is still unused memory. Thoughts? Thanks a lot -- Tobi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm unsubscribe to mysql@lists.mysql.com
Am Freitag 25 Juli 2008 21:19:56 schrieb [EMAIL PROTECTED]: > To confirm that you would like > > [EMAIL PROTECTED] > > removed from the mysql mailing list, please click on > the following link: > > http://lists.mysql.com/u/mysql/488a275c5444d766/lists=feilner-it.net > > This confirmation serves two purposes. First, it verifies that we are > able to get mail through to you. Second, it protects you in case > someone forges a subscription request in your name. > > We haven't checked whether your address is currently on the mailing list. > To see what address you used to subscribe, look at the messages you are > receiving from the mailing list. Each message has your address hidden > inside its return path; for example, [EMAIL PROTECTED] receives messages > with return path: mysql-return-[EMAIL PROTECTED] > > > --- Administrative commands for the mysql list --- > > I can handle administrative requests automatically. Please > do not send them to the list address! Instead, send > your message to the correct command address: > > To subscribe to the list, send a message to: ><[EMAIL PROTECTED]> > > To remove your address from the list, just send a message to > the address in the ``List-Unsubscribe'' header of any list > message. If you haven't changed addresses since subscribing, > you can also send a message to: ><[EMAIL PROTECTED]> > > or for the digest to: ><[EMAIL PROTECTED]> > > For addition or removal of addresses, I'll send a confirmation > message to that address. When you receive it, simply reply to it > to complete the transaction. > > If you need to get in touch with the human owner of this list, > please send a message to: > > <[EMAIL PROTECTED]> > > Please include a FORWARDED list message with ALL HEADERS intact > to make it easier to help you. > > --- Enclosed is a copy of the request I received. > > Received: (qmail 24237 invoked by uid 48); 25 Jul 2008 19:19:55 - > Date: 25 Jul 2008 19:19:55 - > Message-ID: <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Unsubscribe request > From: <[EMAIL PROTECTED]> > > This message was generated because of a request from 84.56.89.14. -- Best Regards - Mit freundlichen Gruessen Markus Feilner - Feilner IT Linux & GIS Linux Solutions, Training, Seminare und Workshops - auch Inhouse Koetztingerstr 6c93057 Regensburg Telefon:+49 941 8 10 79 89 Mobil: +49 170 3 02 70 92 WWW: www.feilner-it.net mail: [EMAIL PROTECTED] -- My OpenVPN book - http://www.packtpub.com/openvpn/book OPENVPN : Building and Integrating Virtual Private Networks My new book - Out now: http://www.packtpub.com/scalix/book SCALIX Linux Administrator's Guide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm unsubscribe to mysql@lists.mysql.com
Am Freitag 25 Juli 2008 21:19:53 schrieb [EMAIL PROTECTED]: > To confirm that you would like > > [EMAIL PROTECTED] > > removed from the mysql mailing list, please click on > the following link: > > http://lists.mysql.com/u/mysql/488a2759cd767dbb/lists=feilner-it.net > > This confirmation serves two purposes. First, it verifies that we are > able to get mail through to you. Second, it protects you in case > someone forges a subscription request in your name. > > We haven't checked whether your address is currently on the mailing list. > To see what address you used to subscribe, look at the messages you are > receiving from the mailing list. Each message has your address hidden > inside its return path; for example, [EMAIL PROTECTED] receives messages > with return path: mysql-return-[EMAIL PROTECTED] > > > --- Administrative commands for the mysql list --- > > I can handle administrative requests automatically. Please > do not send them to the list address! Instead, send > your message to the correct command address: > > To subscribe to the list, send a message to: ><[EMAIL PROTECTED]> > > To remove your address from the list, just send a message to > the address in the ``List-Unsubscribe'' header of any list > message. If you haven't changed addresses since subscribing, > you can also send a message to: ><[EMAIL PROTECTED]> > > or for the digest to: ><[EMAIL PROTECTED]> > > For addition or removal of addresses, I'll send a confirmation > message to that address. When you receive it, simply reply to it > to complete the transaction. > > If you need to get in touch with the human owner of this list, > please send a message to: > > <[EMAIL PROTECTED]> > > Please include a FORWARDED list message with ALL HEADERS intact > to make it easier to help you. > > --- Enclosed is a copy of the request I received. > > Received: (qmail 24222 invoked by uid 48); 25 Jul 2008 19:19:52 - > Date: 25 Jul 2008 19:19:52 - > Message-ID: <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Unsubscribe request > From: <[EMAIL PROTECTED]> > > This message was generated because of a request from 84.56.89.14. -- Best Regards - Mit freundlichen Gruessen Markus Feilner - Feilner IT Linux & GIS Linux Solutions, Training, Seminare und Workshops - auch Inhouse Koetztingerstr 6c93057 Regensburg Telefon:+49 941 8 10 79 89 Mobil: +49 170 3 02 70 92 WWW: www.feilner-it.net mail: [EMAIL PROTECTED] -- My OpenVPN book - http://www.packtpub.com/openvpn/book OPENVPN : Building and Integrating Virtual Private Networks My new book - Out now: http://www.packtpub.com/scalix/book SCALIX Linux Administrator's Guide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weekly Headline News
A changing world brings freshness. We can prove that reading makes a perfect man.Pick up a copy of Weekly Headline News Try it today and leave a mark and thank you note. http://weeklyheadlinenews.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bulk INSERT performance question
At 06:46 PM 7/25/2008, you wrote: List, I am bulk inserting a huge amount of data into a MyISAM table (a wikipedia page dump). Before I issued SOURCE filename.sql; I did an ALTER TABLE page DISABLE KEYS; LOCK TABLES page WRITE; The dump consists of about 1,200 bulk INSERT statements with roughly 12,000 tuples each. For the first hour or so it performed pretty good, taking about 5 seconds for each INSERT and using all the CPU it could get. But a while later time consumption increased up to 10 minutes. In the same time, mysqld is barely using any CPU (less than 2%), there is no significant I/O going on, and there is still unused memory. Thoughts? Thanks a lot -- Tobi Tobi, It will be much faster if you use a Load Data Infile on an empty table. Your method is likely slowing down because the table has keys that needs to be maintained. You could remove all of the keys, unique or otherwise, and build them after the data has been loaded. (Disable Keys will not disable unique keys or the primary key). Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
