Re: How NOT to log SHOW INNODB STATUS in the query log.
2006/1/25, Nathan Gross [EMAIL PROTECTED]: Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. If you have a linux box (or any acceptable shell) cat query.log | grep -i -v 'show innodb status' query_clean.log grep -i : case insensitive grep -v : everything but the patterm given cat : well a cat is a cat... Thanks -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How NOT to log SHOW INNODB STATUS in the query log.
Aye. -v. thanks! -nat On 1/26/06, Pooly [EMAIL PROTECTED] wrote: 2006/1/25, Nathan Gross [EMAIL PROTECTED]: Hi; My query.log is full of 'show innodb status' queries. How do I get this ascii log file not to log these. OR some help with a grep script to copy the file without these lines. If you have a linux box (or any acceptable shell) cat query.log | grep -i -v 'show innodb status' query_clean.log grep -i : case insensitive grep -v : everything but the patterm given cat : well a cat is a cat... Thanks -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert...on duplicate key update...help
I'm trying to change a couple of replace statements to
insert...on duplicate key update (using Perl/DBI).
foreach my $key (keys %e_items) {
my $sql = insert table1
(id, date, time, uid, type, seq, value)
values
(?, ?, ?, ?, ?, ?, ?)
on duplicate key update;
my $sth = $dbh-prepare($sql);
$sth-execute($e_items{$key}-[0], $date, $e_items{$key}-[3],
$uid, $e_items{$key}-[1], $e_items{$key}-[2],
$e_items{$key}-[4]) || die $sth-errstr;
}
The manual says more is needed at the end of my sql, but
I'm not sure of the syntax. (Looks to me like all required
info is present ;) id is primary key and the only unique
index.
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYISAM only: Can I remove the ibdata file?
I recently changed all my ibd files to MYISAM. Can I safely remove the ibdata file and restart mysql? Thanks; -nat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difficult Problem: SQLDescribeCol call on MySQL Error
Hello. I'm not a MyODBC expert and not completely sure if it is your case, but it seems that there're several bugs similar to your. See: http://bugs.mysql.com/bug.php?id=10148 You can find more by searching on the 'SQLDescribeCol' keyword in the bugs database. Check that you're using the latest version of MyODBC. Daxin Zuo wrote: This call works fine on Oracle, Access, MS SQL. But Not works on MySQL. MySQL 5.0.15, MySQL ODBC drive 3.51, The OP is Windows 2000/2003, Program in VC++ In my SQLDescribeCol call on MySQL I get correct value on: ColumnName, BufferLength, NameLengthPtr, NullablePtr I get corect value on DataTypePtr if the type is not Text. For Text, it return -1. I get correct value on ColumnSizePtr only if the column type is varchar, and datetime , I get all 0 on DecimalDigitsPtr. The SQLRETURN always 0. Any Instruction is welcome. Thanks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Postcode Search
[EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Shaun [EMAIL PROTECTED] wrote on 01/23/2006 11:51:32 AM: Hi, We have a dataset of uk postcodes and their relevant X-Coordinates and Y-Coordinates, a table of properties (houses), a table of users and a table of offices - users work in an office - table structures below. Is it possible to run a search of all properties in the properties table that come within a certain distance of the users postcode, currently we do this by downloading all properties into an array and stripping out the ones that don't come within the radius with php. Any advice would be greatly appreciated. # -- MySQL dump -- # # Table structure for table 'Offices' # CREATE TABLE Offices ( Office_ID int(11) DEFAULT '' NOT NULL auto_increment, Subscriber_ID int(11), Type varchar(10), Address_Line_1 varchar(50), Address_Line_2 varchar(50), City varchar(50), County varchar(50), Postcode varchar(10), Telephone varchar(12), Fax varchar(12), Radius tinyint(4), PRIMARY KEY (Office_ID) ); # # Table structure for table 'Postcodes' # CREATE TABLE Postcodes ( PCDSECT varchar(6) DEFAULT '' NOT NULL , SORTSECT varchar(6), PCDDIST varchar(4), SORTDIST varchar(4), PCDAREA char(2), X_COORD double(7,1) unsigned , Y_COORD double(7,1) unsigned , PRIMARY KEY (PCDSECT) ); # # Table structure for table 'Properties' # CREATE TABLE Properties ( CHK varchar(20), Property_ID int(11) DEFAULT '' NOT NULL auto_increment, Insertion_Date date, Status varchar(20), Property_Name_Or_Number varchar(50), Address_Line_1 varchar(50), Address_Line_2 varchar(50), City varchar(50), County varchar(50), Postcode varchar(12), PRIMARY KEY (Property_ID) ); # # Table structure for table 'Users' # CREATE TABLE Users ( User_ID int(11) DEFAULT '' NOT NULL auto_increment, Office_ID int(11), Type varchar(20), Title varchar(4), Firstname varchar(20), Lastname varchar(20), Password varchar(20) DEFAULT '' NOT NULL , Email varchar(50), PRIMARY KEY (User_ID) ); # --- Dump ends --- Assuming a general distance formula of R=SQR((x1-x2)^2 + (y1-y2)^2) Here is a parameterized example for a single user: SET @userID = ?? /* your pick */ SET @TargetR = ?? /* again, your pick */ SELECT @sourceX:= pc.X_COORD, @sourceY := pc.Y_COORD FROM postcodes pc INNER JOIN office o ON o.postcode = PC.pcdsect INNER JOIN users u ON u.office_id = o.office_id AND u.user_id = @userID; CREATE TEMPORARY TABLE tmpShortList (KEY(pcdsect)) SELECT pc.pcdsect, (@sourceX - pc.X_COORD) X_DIFF, (@sourceY - pc.Y_COORD) Y_DIFF, FROM Postcodes pc WHERE pc.X_COORD BETWEEN (@SourceX - @TargetR) and (@SourceX + @TargetR) AND pc.Y_COORD BETWEEN (@SourceY - @TargetR) and (@SourceY + @TargetR) HAVING SQR(X_DIFF*X_DIFF + Y_DIFF*Y_DIFF) = @TargetR; Now you have a table of all Postal codes (tmpShortList) that fall within a certain radius (@TargetR) of a certain user (@UserID). Do with it what you will. In your example, you wanted to see all properties were within that search radius. SELECT p.* FROM properties p INNER JOIN tmpShortList sl ON sl.pcdsect = p.postcode; HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Hi Shawn, Sorry for not replying sooner, had to get to get create table permissions added to the server. In our table of postcodes we only have the 1st part of the postcode plust the next one after the space i.e. mysql SELECT * FROM Postcodes LIMIT 10; +-+--+-+--+-+--+--+ | PCDSECT | SORTSECT | PCDDIST | SORTDIST | PCDAREA | X_COORD | Y_COORD | +-+--+-+--+-+--+--+ | AB10 1 | AB10 1 | AB10| AB10 | AB | 393439.0 | 806083.6 | | AB10 6 | AB10 6 | AB10| AB10 | AB | 392722.5 | 804986.1 | | AB10 7 | AB10 7 | AB10| AB10 | AB | 392137.3 | 803705.5 | | AB11 5 | AB11 5 | AB11| AB11 | AB | 394945.3 | 805929.4 | | AB11 6 | AB11 6 | AB11| AB11 | AB | 393777.1 | 805585.0 | | AB11 7 | AB11 7 | AB11| AB11 | AB | 393878.7 | 804655.6 | | AB11 8 | AB11 8 | AB11| AB11 | AB | 395810.7 | 804873.8 | | AB11 9 | AB11 9 | AB11| AB11 | AB | 395330.2 | 805247.4 | | AB12 3 | AB12 3 | AB12| AB12 | AB | 394715.1 | 801526.0 | | AB12 4 | AB12 4 | AB12| AB12 | AB | 391968.1 | 797593.4 | +-+--+-+--+-+--+--+ 10 rows in set (0.00 sec) mysql So I need to compare an office postcode i.e. SN1 1QS to the Postcode table in the 1st query, is this possible? -- MySQL General
Re: MYISAM only: Can I remove the ibdata file?
Hello. Before removing the ibd files, add skip-innodb to your configuration file, restart the server and check if your applications still work fine. Nathan Gross wrote: I recently changed all my ibd files to MYISAM. Can I safely remove the ibdata file and restart mysql? Thanks; -nat -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with character sets and collation
Hello.
Please, execute the following statements in mysql command line and php,
and provide its output to the list:
show variables like '%char%';
show variables like '%collation%';
Include the CREATE statement for your table as well.
Chris wrote:
I think I have a problem with mysql related character sets and collation.
With language English (en-utf-8), MySQL charset UTF-8 Unicode and a MySQL
connection collation: ascii_general_ci. I can execute a sql statement in
phpmyadmin, like INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
Stra�e')
But if I try to execute the insert using a php script I get the error 1406
record too long. Using the same insert but without the � character, the sql
statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
StraXe')
How would I configure mysql so characters like � can be inserted without
problems.
Thanks
chris
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert...on duplicate key update...help
Hello.
Perhaps you have forgotten to add col_name=expr to the end of your
query. See:
http://dev.mysql.com/doc/refman/5.0/en/insert.html
Jonathan Mangin wrote:
I'm trying to change a couple of replace statements to
insert...on duplicate key update (using Perl/DBI).
foreach my $key (keys %e_items) {
my $sql = insert table1
(id, date, time, uid, type, seq, value)
values
(?, ?, ?, ?, ?, ?, ?)
on duplicate key update;
my $sth = $dbh-prepare($sql);
$sth-execute($e_items{$key}-[0], $date, $e_items{$key}-[3],
$uid, $e_items{$key}-[1], $e_items{$key}-[2],
$e_items{$key}-[4]) || die $sth-errstr;
}
The manual says more is needed at the end of my sql, but
I'm not sure of the syntax. (Looks to me like all required
info is present ;) id is primary key and the only unique
index.
Thanks,
Jon
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_connections
as somebody in the list already pointed out it all depends on the amount of RAM you have , the formula has been taken from Jeremy's book , high performance Mysql Expressed mathematically, that is: min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool net_buffer and as far as I know mysql does not reserve system resources based on max_connections, it will use/release RAM based on the current connections Kishore Jalleda On 1/25/06, Asad Habib [EMAIL PROTECTED] wrote: Are there any statistics that I can use to determine how high or low I should set max_connections? I am using MySQL as the backend for a Java web application that will need to accommodate a moderate amount of traffic. Also, if max_connections is set too high relative to the needs of the application, does this waste system resources? In other words, does MySQL reserve system resources based on the value of max_connections or does it dynamically reserve/release system resources based on what the application's current needs are? The application itself uses a connection pool and hence idle connections are recycled and reused. Any help would be appreciated. Thanks. - Asad -- 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]
DB Tables on separate hardisks
Hi MySql,
Would it be possible to have the PK tables on hardisk-A and FK tables on
hardisk-B using only one database?
to envision:
CUSTOMER_MYSQL_DB
CUSTOMER_TBL {PK} [HD-A], CUSTOMER_PURCHASES_TBL {FK} [HD-B]
-Melvin
-
Bring words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
Re: DB Tables on separate hardisks
Melvin Zamora wrote:
Hi MySql,
Would it be possible to have the PK tables on hardisk-A and FK tables on
hardisk-B using only one database?
to envision:
CUSTOMER_MYSQL_DB
CUSTOMER_TBL {PK} [HD-A], CUSTOMER_PURCHASES_TBL {FK} [HD-B]
-Melvin
-
Bring words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
You can do it symlinks (see the manual for this info.)
HTH
--
Thanks,
James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collect SQL Statements
Moeller, Thorsten, AO wrote: Hi, is there a possibility to collect the sql statements issued to a mysql db to analyse them?? perhaps there is an extra tool or script for this?? Thanks for any suggestions! I don't know about collecting all queries, but we're doing this in our my.cnf file: # install slow query log long_query_time = 10 log_slow_queries = /var/log/httpd/mysql_slow_query.log On a server running 4.0.20 It's not a box I set up, so I don't know if you have to do anything special to get it working, but I've noticed that it's there, and is working. Might be what you're looking for. Yani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting SQL_BIG_SELECTS
I've determined that I need to set SQL_BIG_SELECTS=1 for an application I have which connects to a MySQL 4.x database. However, I don't see how to apply it universally to that application's connection. Is it something I need to put in the connection string? Something I need to put in each query? Basically, is there a way to set it globally on the MySQL server so it will apply to all sessions/connections? Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting SQL_BIG_SELECTS
David P. Donahue [EMAIL PROTECTED] wrote on 01/26/2006 02:19:25 PM: I've determined that I need to set SQL_BIG_SELECTS=1 for an application I have which connects to a MySQL 4.x database. However, I don't see how to apply it universally to that application's connection. Is it something I need to put in the connection string? Something I need to put in each query? Basically, is there a way to set it globally on the MySQL server so it will apply to all sessions/connections? Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com How to specify that option depends entirely on how you are forming your connection with the MySQL server. Which connection library are you using and how are you opening your connection? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Setting SQL_BIG_SELECTS
How to specify that option depends entirely on how you are forming your connection with the MySQL server. Which connection library are you using and how are you opening your connection? The application is an ASP .NET web app hosted on Mono on an Apache web server running Linux. My data access class uses the MySQLConnector .NET library (1.0.7) to issue SELECT statements to populate DataSets. The connection string is as follows: Uid=myUsername;Pwd=myPassword;Server=192.168.0.12;Database=myDatabase; The function which returns the results of the query boils down to the following: mySqlConnection = new MySqlConnection(stringDatabaseConnection); mySqlDataAdapter = new MySqlDataAdapter(stringSelect, mySqlConnection); dataSetSQL = new DataSet(); mySqlDataAdapter.Fill(dataSetSQL); return dataSetSQL; Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Tables on separate hardisks
At 1:07 pm -0500 26/1/06, JamesDR wrote: You can do it symlinks (see the manual for this info.) Just in case you are tempted to try _without_ reading the manual, this is only supported for MyISAM tables with MySQL versions = 4. http://dev.mysql.com/doc/refman/4.1/en/symbolic-links-to-tables.html HTH, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with character sets and collation
Sorry, I am unable to work the command line. I have used this script
instead.
$sql = show variables like '%char%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
$count = mysql_num_rows($result);
//echo $count;
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value) echo
$key=$valueBR;
echo BR;
$sql = show variables like '%collation%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value) echo
$key=$valueBR;
hope this provides the appropriate info.
Thanks
Gleb Paharenko [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello.
Please, execute the following statements in mysql command line and php,
and provide its output to the list:
show variables like '%char%';
0=character_set_client
1=latin1
0=character_set_connection
1=latin1
0=character_set_database
1=ascii
0=character_set_results
1=latin1
0=character_set_server
1=latin1
0=character_set_system
1=utf8
0=character_sets_dir
1=C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
show variables like '%collation%';
0=collation_connection
1=latin1_swedish_ci
0=collation_database
1=ascii_general_ci
0=collation_server
1=latin1_swedish_ci
Include the CREATE statement for your table as well.
CREATE TABLE my_table (location_id varchar(20) NOT NULL default '',name
varchar(50) NOT NULL default '',PRIMARY KEY (location_id)) TYPE=MyISAM
Chris wrote:
I think I have a problem with mysql related character sets and collation.
With language English (en-utf-8), MySQL charset UTF-8 Unicode and a MySQL
connection collation: ascii_general_ci. I can execute a sql statement in
phpmyadmin, like INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
Stra?e')
But if I try to execute the insert using a php script I get the error
1406
record too long. Using the same insert but without the ? character, the
sql
statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
StraXe')
How would I configure mysql so characters like ? can be inserted without
problems.
Thanks
chris
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Invitación: Webinar MySQL 5.0 - Nuevas funcionalidades para la Empresa
Amigos me llegó este mail, quiza les sea de ayuda. Jorge Paiva Lima - Peru MySQL 5.0 - Nuevas funcionalidades para la Empresa Miércoles 1 de Febrero de 2006 MySQL les invita a participar en el primer webinar en castellano para España y Latinoamérica. En este webinar, en colaboración con nuestro partner Afina, explicaremos: a.. Resúmen de la arquitectura de los diferentes motores de la base de datos b.. Nuevas capacidades de MySQL 5.0, incluyendo Stored Procedures, Triggers, Views, e Information Schema. La versión 5.0 de MySQL se ha descargado más de cuatro millones de veces desde su lanzamiento en Octubre de 2005 c.. Presentación de MySQL Network, paquete de soporte y servicios de la base de datos empresarial todo en uno. d.. Explicación de los servicios que se ofrecerán a través de Afina Para registrarse, visite http://www.mysql.com/news-and-events/web-seminars/mysql-5.0.sp.php Quién: Gerardo Narvaja y Jimmy Guerrero, MySQL AB, y Gustavo La Iglesia, Responsable de Formación y Servicios de Afina. Qué: MySQL 5.0 - Nuevas funcionalidades para la empresa Cuándo: 1 de Febrero de 2006, 4:00 pm CET (Madrid), 11:00 am COT (Caracas) Duración: Aproximadamente una hora. Dónde: Acceso sencillo a través de Internet desde su oficina. Para qué: Conocer las nuevas funcionalidades de MySQL 5.0 y los servicios a los que puede acceder a través de Afina. Atentamente, MySQL AB Si desea informarse acerca de los servicios que Afina ofrece como Partner Gold Certificado de MySQL puede hacerlo llamando al 91 411 47 85 o envie un email a [EMAIL PROTECTED] -- Copyright © 2006 MySQL AB| www.mysql.com
NOT IN vs IS NULL
In general, I try to stay away from very large IN(..) lists because I have seen them regularly degrade performance, but in this case the alternative that I have found doesn't seem to perform any faster. Could anyone give me some insight as to which of the following queries is better (and why) - or if there is another query that would be faster than either? I am using MySQL 4.1.14. There are three tables, offers: CREATE TABLE `offers` ( `offer_id` int(11) NOT NULL auto_increment, `advertiser_id` int(11) NOT NULL default '0', ... PRIMARY KEY (`offer_id`), KEY `advertiser_id` (`advertiser_id`) ) paytable: CREATE TABLE `paytable` ( `offer_id` int(11) NOT NULL default '0', `login_id` int(11) NOT NULL default '0', ... PRIMARY KEY (`offer_id`,`login_id`) ) publisher_advertiser_blocks: CREATE TABLE `publisher_advertiser_blocks` ( `login_id` int(11) NOT NULL default '0', `advertiser_id` int(11) NOT NULL default '0', PRIMARY KEY (`login_id`,`advertiser_id`) ) The goal of these queries is to select all offer_id's from `paytable` for a known login_id where that login_id is not blocked from that offer_id. While testing I simply selected count(*) to keep my result set from crowding the screen. The two queries have about the same execution time but very different EXPLAIN results... without further ado, here they are: explain select count(*) from paytable where login_id=# and offer_id NOT IN ( select distinct offer_id from offers left join publisher_advertiser_blocks pab using (advertiser_id) where pab.login_id=# ); +++--++---+-+-+--+-+- -+ | id | select_type| table| type | possible_keys | key | key_len | ref | rows| Extra | +++--++---+-+-+--+-+- -+ | 1 | PRIMARY| paytable | index | NULL | PRIMARY | 5 | NULL | 1773152 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | offers | eq_ref | PRIMARY,advertiser_id | PRIMARY | 4 | func | 1 | Using where; Using temporary | | 2 | DEPENDENT SUBQUERY | pab | eq_ref | PRIMARY | PRIMARY | 8 | const,affiliate.offers.advertiser_id | 1 | Using where; Using index | +++--++---+-+-+--+-+- -+ 3 rows in set (0.00 sec) explain select count(*) from paytable left join ( select distinct offer_id from offers left join publisher_advertiser_blocks pab using (advertiser_id) where pab.login_id=# ) as a using (offer_id) where login_id=# and a.offer_id IS NULL; ++-++---+---+---+-+-+-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-++---+---+---+-+-+-+---+ | 1 | PRIMARY | paytable | index | NULL | PRIMARY | 5 | NULL| 1773152 | Using where; Using index | | 1 | PRIMARY | derived2 | ALL | NULL | NULL |NULL | NULL| 309 | Using where; Not exists | | 2 | DERIVED | pab| ref | PRIMARY | PRIMARY | 4 | | 2 | Using where; Using index; Using temporary | | 2 | DERIVED | offers | ref | advertiser_id | advertiser_id | 2 | affiliate.pab.advertiser_id | 8 | Using where | ++-++---+---+---+-+-+-+---+ 4 rows in set (0.01 sec) Thanks in advance, -- Devananda vdv http://devananda-vdv.blogspot.com/ http://mycat.sourceforge.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re:Can not get answers
Thank to Mike give the result about the third question. But the first and second question not answer. The first question related to the function of mysql-administrator's subassembly.A picture about this question in the accessories. http://forums.mysql.com/read.php?34,61375,61375#msg-61375 The second question related to the RAID setup in mysql-administrator. http://forums.mysql.com/read.php?34,61374,61374#msg-61374 - Original Message - From: wangxu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, January 23, 2006 3:57 PM Subject: Can not get answers I can not recieve answer about follow url for a long time. I very need the answers of those. Please help me. thanks! http://forums.mysql.com/read.php?34,61375,61375#msg-61375 http://forums.mysql.com/read.php?34,61374,61374#msg-61374 http://forums.mysql.com/read.php?34,60411,60411#msg-60411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to connect tomcat with mysql pl help
Hi All, Iam using tomcat 5x and mysql5.x the proble is when i try to connect mysql and tomcat using j/connector 3.x tomcat throws error as access denied to [EMAIL PROTECTED] host using password 'YES'. Please help me to overcome this problem. Thanks in advance sankar - Do you Yahoo!? With a free 1 GB, there's more in store with Yahoo! Mail.
