Re: Need to know how to do INSERTS, using variables for VALUE
In the last episode (Jan 22), [EMAIL PROTECTED] said: > Hello, > Doing C coding with MSql. > What am I missing here? > > I am needing to do standard INSERT statements using variables for the > VALUE fields These fields are changed in the application befre I > issue the INSERT. The current proram uses : before the variable to > make it work. > > Like this: INSERT INTO EMP_MASTER (EMP_NO, SUPERVISOR,BADGE_NO) > VALUES (:emp_no, :super, :badge_no); > > The :emp_no etc. is obviously what is not working...Why? Mysql doesn't support named bind variables in the C API; it only understands the "?" markers. If you take a look at the MYSQL_BIND struct, you can see there is no name field. Which really just means that when you call mysql_stmt_bind_param() you have to pass your variables in the same order as they appeared in your query when you called mysql_stmt_prepare(). http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-prepare.html http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-bind-param.html This has some example code: http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-execute.html My guess is that if you were to extend mysql_stmt_prepare() and mysql_stmt_bind_param() to support named variables and submitted them to bugs.mysql.com as a patch, no-one would object :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need to know how to do INSERTS, using variables for VALUE
Hello, Doing C coding with MSql. What am I missing here? I am needing to do standard INSERT statements using variables for the VALUE fields These fields are changed in the application befre I issue the INSERT. The current proram uses : before the variable to make it work. Like this: INSERT INTO EMP_MASTER (EMP_NO, SUPERVISOR,BADGE_NO) VALUES (:emp_no, :super, :badge_no); The :emp_no etc. is obviously what is not working...Why? Works great in DB/2 . I just can't find how to do it in MySql. Thanks for any help here! Lee ** Start the year off right. Easy ways to stay in shape. http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489
Re: adding then removing index produces different query results
mysql mysql wrote: Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4) drop index 5) execute same query takes 0.2 seconds and uses a different method of returning results from the original query in 1) 6) restart mysql 7) execute query takes 2 minutes Because your o/s does caching as well and probably has the whole .MYD (data) file in memory since there hasn't been a reason to swap it out. Try doing a big query in the middle (select * from other_big_table order by rand() limit 1) and/or restarting mysql between #4 and #5. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
adding then removing index produces different query results
Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4) drop index 5) execute same query takes 0.2 seconds and uses a different method of returning results from the original query in 1) 6) restart mysql 7) execute query takes 2 minutes Here's the SQL I used to produce the behaviour: mysql> select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; [data omitted for clarity] 25 rows in set (1 min 50.23 sec) mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; ++-+--++---+-+-++++ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+-+-++++ | 1 | SIMPLE | phantom_products | ALL| label_id_idx | NULL| NULL| NULL | 787738 | Using filesort | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+-+-++++ 2 rows in set (0.00 sec) mysql> create index title_idx on phantom_products(title); Query OK, 777262 rows affected (1 min 58.08 sec) Records: 777262 Duplicates: 0 Warnings: 0 select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; 25 rows in set (11.03 sec) mysql> explain select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; ++-+--++---+---+-+++-+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+---+-+++-+ | 1 | SIMPLE | phantom_products | index | label_id_idx | title_idx | 258 | NULL | 785367 | | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+---+-+++-+ mysql> select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' LIMIT 75, 25; 25 rows in set (0.01 sec) mysql> drop index title_idx on phantom_products; Query OK, 777262 rows affected (53.89 sec) Records: 777262 Duplicates: 0 Warnings: 0 mysql> SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; 25 rows in set (0.02 sec) mysql> explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; ++-+--+--+---+--+-++---+--+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--+--+---+--+-++---+--+ | 1 | SIMPLE | phantom_labels | ALL | PRIMARY | NULL | NULL| NULL | 17632 | Using where; Using temporary; Using filesort | | 1 |
Re: Did NOT condition on VARCHAR change with 5.0.45?
`method` is the name of the column in the table. In my opinion it should return every record from the table where `method` is equal to NULL, ZERO or EMPTY STRING, as this table doesn't allow null, and the field type is varchar, this would be EMPTY STRING's only. This has always worked in the past. However now EVERY row is returned regardless of the contents of `method`. Thoughts? > What is method mean in your sql statement? > > On Jan 15, 2008 5:33 PM, Martijn Tonies <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > >I am having a problem with MySQL 5.0.45 returning what I consider to be > > >unexpected results. > > > > > >Basically I am attempting the following query, and ALL records are being > > >returned. > > > > > >" SELECT * FROM `jos_products_orders` WHERE NOT `method` " > > > > For the uninformed, what should this do according to you? > > > > Martijn Tonies > > Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle > > & > > MS SQL Server > > Upscene Productions > > http://www.upscene.com > > My thoughts: > > http://blog.upscene.com/martijn/ > > Database development questions? Check the forum! > > http://www.databasedevelopmentforum.com > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/mysql?unsub=1 > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing data types in mysql!
Hello, I was pulling data from one datasource [ oracle ] earlier which had a couple of fields as integer. Now i am moving to a newer data source and the same fields are now varchar's in the newer oracle database. I am planning to change the data types of those fields from integer to varchar's as am not doing any arithmetic with those fields. Does anyone see any issues with this approach ? Will this break any of the existing functionality when i move from INT to varchar's ? Thanks ! Lakshmi
Configure options
Hi, We normally use the binaries, but there's a few patches I want to try out, so I've gotten the source. Now I've also compiled sources many times, so that process isn't a problem. However, what I wanted to do is try to compile the source with the exact same configure options that the official binaries are using -- I couldn't find anywhere that listed the options MySQL uses for making official binaries for all the various packages they support. Ideally I'd like to be able to start with known good set of options (since the binaries generally work excellent in our environment) but be able to tweak compile options and see what the differences are -- or apply patches and end up with a binary that is exactly like the official ones, just patched. Thanks in advance! -Gavin Towey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corruption? Performance issue + strange 'explain'
Hi, Have you tried changing the date format like delete from TelecomAccountPosting where LocID=19014 and InvDate='2008-01-15'; I know it shouldn't matter but I have had similar issues due to the date format. Also are you sure there are 3773 records matching this criteria - according to your table structure there is an UNIQUE index on LocID and InvDate so there should not be more than one record. Dobromir Velev WebSitePulse.com On Tuesday 22 January 2008 02:39, Daniel Kasak wrote: > On Tue, 2008-01-22 at 11:23 +1100, Chris wrote: > > > Why is it saying 'Impossible WHERE noticed after reading const tables'? > > > > http://dev.mysql.com/doc/refman/4.1/en/explain.html > > MySQL has read all const (and system) tables and notice that the WHERE > > clause is always false. > > > > ie - no rows match that query and so there's nothing to 'explain'. > > There must be a problem then. In this particular example, there were > 3773 records returned by this select. > > > I'd suspect that the time is spent trying to check or clean up the > > foreign key reference. Are there lots of locations with that id in the > > tlocations table? > > 1 > > > I'd also assume that since it's named 'id' it would be > > a primary key (and indexed) ? > > Indexed, yes. > > I'll go ahead with that restore from a backup tonight. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Packing list sort
Thank you very much, that seems very simple now that I see it. Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 10:26 AM To: David Ruggles; 'mysql' Subject: RE: Packing list sort Try One of These (including name): SELECT name,species,birth FROM animals ORDER BY IF(species='hamster',0,1),species,name; OR SELECT name,species,birth FROM (SELECT name,species,birth,IF(species='hamster',0,1) sortorder FROM animals) A ORDER BY sortorder,species,name; -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 10:23 AM To: David Ruggles; 'mysql' Subject: RE: Packing list sort Try One of These: SELECT name,species,birth FROM animalsORDER BY IF(species='hamster',0,1),species; OR SELECT name,species,birth FROM (SELECT name,species,birth,IF(species='hamster',0,1) sortorder FROM animals) A ORDER BY sortorder,species; -Original Message- From: David Ruggles [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 9:53 AM To: 'mysql' Subject: Packing list sort I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ 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] -- 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]
RE: Packing list sort
Try One of These (including name): SELECT name,species,birth FROM animals ORDER BY IF(species='hamster',0,1),species,name; OR SELECT name,species,birth FROM (SELECT name,species,birth,IF(species='hamster',0,1) sortorder FROM animals) A ORDER BY sortorder,species,name; -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 10:23 AM To: David Ruggles; 'mysql' Subject: RE: Packing list sort Try One of These: SELECT name,species,birth FROM animalsORDER BY IF(species='hamster',0,1),species; OR SELECT name,species,birth FROM (SELECT name,species,birth,IF(species='hamster',0,1) sortorder FROM animals) A ORDER BY sortorder,species; -Original Message- From: David Ruggles [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 9:53 AM To: 'mysql' Subject: Packing list sort I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ 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] -- 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: Packing list sort
Try One of These: SELECT name,species,birth FROM animalsORDER BY IF(species='hamster',0,1),species; OR SELECT name,species,birth FROM (SELECT name,species,birth,IF(species='hamster',0,1) sortorder FROM animals) A ORDER BY sortorder,species; -Original Message- From: David Ruggles [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 22, 2008 9:53 AM To: 'mysql' Subject: Packing list sort I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Packing list sort
David Ruggles schrieb: I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ you need to add an additional field like 'priority' and do "ORDER BY priority DESC, species ASC" -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Packing list sort
I have googled, read the mysql documentation and searched the list archive. I don't know if I just don't know the correct term to use or if I have some other problem. In a nutshell I generate packing lists where the items are normally sorted alphabetically. However, there is one type of item that always gets loaded first. I would like for any of these items to always be at the top of the list. Here's the classic animal example: Given: +--+-++ | name | species | birth | +--+-++ | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Puffball | hamster | 1999-03-30 | | Slim | snake | 1996-04-29 | +--+-++ I want hamsters to always sort out first, but still have everything else in alphabetical order Like this: +--+-++ | name | species | birth | +--+-++ | Puffball | hamster | 1999-03-30 | | Chirpy | bird| 1998-09-11 | | Whistler | bird| 1997-12-09 | | Claws| cat | 1994-03-17 | | Fluffy | cat | 1993-02-04 | | Fang | dog | 1990-08-27 | | Bowser | dog | 1989-08-31 | | Buffy| dog | 1989-05-13 | | Slim | snake | 1996-04-29 | +--+-++ 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: Query optimization
-What I'am trying to do: Bit hard to explain. I've got a table consisting of ip addresses (ipv4_src), destination addresses (ipv4_dst), and port numbers (port_dst) and some other irrelevant columns. Ultimately my goal is to find a linear order in a subset of ports. For example, host A connects to B on port 1, 2, 3, 4,...20, I would like to find this linear relation. To achieve this, I have written some mathematical formula's. Unfortunately there is one downside to my formula: It can be fooled by adding some extreme values. It cannot find a linear relation in this list: 1, 2, 3, 4,...20, 45000. Although there are 20 numbers lined up, the 45000 number ruïns the result of the algorithm. So the query I've submitted, is ment to remove extreme values. The methods boxplot(pF.port_dst,"LOW") and boxplot(pF.port_dst,"HIGH") calculate in linear time the allowed range of numbers. Extreme values won't be included in this range. So in the example, the range would be [1,20] therby omitting the value 45000. Finally I would like to filter my table with port numbers for every tuple and remove all the numbers not fitting in the range. -In human readable pseudo code this is the query: SELECT source,dest,port,octets FROM ( SELECT source,dest,boxplot(port,"LOW") AS low,boxplot(port,"HIGH") AS high FROM --Calculate the LOW and HIGH values for each source,dest pair. ( SELECT source,dest,port,octets FROM... GROUP BY source,dest,port --This removes the duplicate entries. ) pF GROUP BY source,dest ) boxplot ( SELECT source,dest,port,octets FROM... GROUP BY source,dest,port --This removes the duplicate entries (again!). ) filter WHERE filter.source=boxplot.source AND filter.dest=boxplot.dest AND filter.port>=boxplot.LOW AND filter.port<=boxplot.HIGH --Relate the tables 'boxplot' and 'filter' to eachother AND select only the tuples where port is in the range [LOW,HIGH] from the filter table. -Here is the original query I would like to optimize again: SELECT filter.ipv4_src, filter.ipv4_dst, filter.port_dst, filter.octets FROM ( SELECT pF.ipv4_src, pF.ipv4_dst, boxplot(pF.port_dst,"LOW") AS low,boxplot(pF.port_dst,"HIGH") AS high FROM ( SELECT ipv4_src, ipv4_dst, port_dst, octets FROM ... GROUP BY ipv4_src, ipv4_dst, port_dst ) pF GROUP BY pF.ipv4_src, pF.ipv4_dst ) boxplot, ( SELECT ipv4_src, ipv4_dst, port_dst, octets FROM ... GROUP BY ipv4_src, ipv4_dst, port_dst )filter WHERE filter.ipv4_src=boxplot.ipv4_src AND filter.ipv4_dst=boxplot.ipv4_dst AND filter.port_dst>=boxplot.low AND filter.port_dst<=boxplot.low I'll try the 'EXPLAIN' query tonight, but therefor I've got to create a small database first to speed up the results. Thnx in advance for your help. On Jan 22, 2008 8:15 AM, Sebastian Mendel <[EMAIL PROTECTED]> wrote: > Joris Kinable schrieb: > > > Optimize query > > > > I've got one query, which I would like to improve a lot since it takes > > very long (>24 hours) to execute. Here is the idea: > > 1. Take the table (other rows in this > > table are not mentioned for clearity) and remove all duplicate > > tuple's. This is done by subquery 'filter'. > > 2. The same query is performed by the boxplot query, but this time an > > aditional group by command is executed, therby calculating a User > > Defined Function boxplot(row,type) which returns a double value. > > 3. Finally the results of the query in step 2 are used to select a > > subset of results from the 'filter' table. > > 4. As you can see, the subquery 'pF' used in step 2 is identical to > > the query 'filter'. It's an extreme waste to calculate the same table > > twice. I've tried to create a temporary table from filter, but > > unfortunately Mysql doesn't allow you to access a temporary table > > twice in the same query. I prefer a 1 query answer, instead of > > creating views, or temporary tables. > > > > Is there a way to improve this query, therby improving the execution time? > > > > Query: > > > > SELECT filter.ipv4_src, filter.ipv4_dst, filter.port_dst, filter.octets FROM > > ( > > SELECT pF.ipv4_src, pF.ipv4_dst, boxplot(pF.port_dst,"LOW") AS > > low,boxplot(pF.port_dst,"HIGH") AS high FROM > > ( > > SELECT ipv4_src, ipv4_dst, port_dst, octets FROM mytable WHERE > > prot=6 GROUP BY ipv4_src, ipv4_dst, port_dst ORDER BY > > ipv4_src,ipv4_dst,port_dst ASC > > ) pF GROUP BY pF.ipv4_src, pF.ipv4_dst HAVING > > COUNT(filter.port_dst)>10 > > ) boxplot, > > ( > > SELECT ipv4_src, ipv4_dst, port_dst, octets FROM mytable WHERE prot=6 > > GROUP BY ipv4_src, ipv4_dst, port_dst ORDER BY > > ipv4_src,ipv4_dst,port_dst ASC > > ) filter > > WHERE filter.ipv4_src=boxplot.ipv4_src AND > > filter.ipv4_dst=boxplot.ipv4_dst AND filter.port_dst>=boxplot.low AND > > filter.port_dst<=boxplot.low > > what you are trying to do? > > and how about formating your query in a human readable way? > > did you tried EXPLAIN? > > what type of synta
MySQL Cluster
Hi, I would be setting up a mysql cluster, i'm researching what's the minimum number of machine to make sure i have fault tolerant and data backups. I found this: Q: What are the minimum system requirements for MySQL Cluster? A: A minimum of three computers is required to run a viable cluster. However, the minimum recommended number of computers in a MySQL Cluster is four: one each to run the management and SQL nodes, and two computers to serve as data nodes: OS:Linux (RedHat, SUSE), Solaris, AIX, HP-UX, Mac OS X CPU:Intel/AMD x86 Memory:512MB RAM HDD:3GB Network:1+ nodes (Standard Ethernet - TCP/IP) To add to this question, can i add 2 more machines so i have a hot standby for both the management and SQL nodes? Also, how can i load balanced, in such a way my web applications will split the to the 2 SQL nodes? Do i need a 2rd party setup for this like ldirectord? TIA Regards, nhadie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
