optimizing a select statement over a database with >50 million recs

Need help on optimizing the select statement: Table structure table1 -- id p_id table2 -- p_id out_id table3 -- out_id name_id table4 --- name_id (unique) prev_id start_id (unique) end_id (unique) Only table4 has unique fields all other fields are not unique. The followin

Re: Limit connections from same host

At 20:32 -0800 2/28/04, Scott Haneda wrote: on 02/28/2004 07:08 PM, Paul DuBois at [EMAIL PROTECTED] wrote: At 17:22 -0800 2/28/04, Scott Haneda wrote: How can I limit the connections from the same host in mysql. You can put a limit on the number of connections per hour from a given account (W

Re: Limit connections from same host

on 02/28/2004 07:08 PM, Paul DuBois at [EMAIL PROTECTED] wrote: > At 17:22 -0800 2/28/04, Scott Haneda wrote: >> How can I limit the connections from the same host in mysql. > > You can put a limit on the number of connections per hour from a given > account (WITH MAX_CONNECTIONS_PER_HOUR), but n

Re: Update from 3 to 4 and some privs don't make sense

At 18:12 -0800 2/28/04, Scott Haneda wrote: When from 3 to 4, ran the update_privs_table thngy... All my users used to say Global Privileges "USAGE" now they are all "CREATE TEMPORARY TABLES, LOCK TABLES" I am not sure this is good or bad, nor am I sure how these settings got in there. They got

RE: Query Help

At 2:45 + 2/29/04, John Berman wrote: Got it working at last SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In (select members_.List_ from members_ where members_.EmailAddr_ like ('"& em & "' union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON members_.List

Re: Limit connections from same host

At 17:22 -0800 2/28/04, Scott Haneda wrote: How can I limit the connections from the same host in mysql. You can put a limit on the number of connections per hour from a given account (WITH MAX_CONNECTIONS_PER_HOUR), but not from a given host. -- Paul DuBois, MySQL Documentation Team Madison, Wisco

Remove Russ Myrick

Can someone remove this guy and his challenge response system. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]

RE: Query Help

Got it working at last SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In (select members_.List_ from members_ where members_.EmailAddr_ like ('"& em & "' union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON members_.List_ = lists_.Name_ WHERE (members_.EmailAddr

Re: Timestamp woes

[EMAIL PROTECTED] wrote: Hi, (Please note: NEWBIE WARNING, below questions might sound stupid, but feel free to flame.) ;-) I have a table "tbl_users" with a field "dat_an_time" which is a timestamp(14). In that I have values such as: 2004022215 20040227042018 20040223015329 etc I have search

Update from 3 to 4 and some privs don't make sense

When from 3 to 4, ran the update_privs_table thngy... All my users used to say Global Privileges "USAGE" now they are all "CREATE TEMPORARY TABLES, LOCK TABLES" I am not sure this is good or bad, nor am I sure how these settings got in there. What are your suggestions? I don't think I want any

Timestamp woes

Hi, (Please note: NEWBIE WARNING, below questions might sound stupid, but feel free to flame.) ;-) I have a table "tbl_users" with a field "dat_an_time" which is a timestamp(14). In that I have values such as: 2004022215 20040227042018 20040223015329 etc I have searched google/the manual fo

Test mail (please ignore)

test -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query Help

Paul Well getting there, im now selecting the correct number of columns but get a cant be distinct error ? John B -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: 29 February 2004 00:47 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Query Help At 0:32 +

Limit connections from same host

How can I limit the connections from the same host in mysql. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Nov

procedure entry point question

Hello,   I'm working with MySql 4.0.18 on Windows2000 and have a question about the C API function mysql_real_escape_string( ).   The program I'm working on compiles and links flawlessly, but at runtime, I get a system pop-up with the error: "The procedure entry point mysql_real_escape_stri

RE: Query Help

At 0:32 + 2/29/04, John Berman wrote: Paul Err no, new area for me this My two queries independently look like this This give me all the lists some one is not a member of SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select members_.List_ from members_ where members_.EmailA

RE: Query Help

Paul Err no, new area for me this My two queries independently look like this This give me all the lists some one is not a member of SELECT lists_.Name FROM lists_ WHERE (((lists_.Name_) Not In (select members_.List_ from members_ where members_.EmailAddr_ like ('"& em & "' And for a

Re: Query Help

At 23:09 + 2/28/04, John Berman wrote: Hi Using MySql 4.x and need some help with a query There are two tables Lists Which holds list name +other stuff Members Which holds list name from above, email address + other stuff I want to list all the lists and then which lists a member is associat

Re: Update question

At 18:41 -0400 2/28/04, Juan E Suris wrote: Here's my table definition: CREATE TABLE `files` ( `id` int(11) NOT NULL auto_increment, `checksum` char(32) NOT NULL default '', `size` bigint(20) NOT NULL default '0', PRIMARY KEY (`id`), KEY `checksum` (`checksum`(8)) This table is used to store info

CR & LF

Hi Using MySql 4.x Simple one this (I think) What do I actually need to use to add a CR and LF with some data that gets imported into my dbase Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL

Query Help

Hi Using MySql 4.x and need some help with a query There are two tables Lists Which holds list name +other stuff Members Which holds list name from above, email address + other stuff I want to list all the lists and then which lists a member is associated with. Eg List1 - Member List2 - Not

Update question

Here's my table definition: CREATE TABLE `files` ( `id` int(11) NOT NULL auto_increment, `checksum` char(32) NOT NULL default '', `size` bigint(20) NOT NULL default '0', PRIMARY KEY (`id`), KEY `checksum` (`checksum`(8)) This table is used to store information on files. When I delete a file, ins

Backup Questions

Can anyone help me with some questions about backup scripts?   I've written a bash script to take database-level backups of each of the databases in our copy of MySQL. We are running MySQL 4.0.15 on Linux Mandrake 9.1 and using a mix of MyISAM and InnoDB databases. This is my script, with the

Re: Can't enable replication !

At 15:46 + 2/28/04, Mike Rabbitt wrote: Hi guys - I am trying to set up replication but cannot get it enabled on the MASTER server - I have added the bin-log line in my.conf and restarted the server - no logs are created tho - The option is log-bin and the filename is my.cnf. What did you *ac

Re: help on PHP code

electroteque wrote: well dude u never showed what the parser returned, i am pretty sure u need curly brackets if u are including more than one line in an if statement well dude u r like totally wrong. -- jimoe at sohnen-moe dot com -- MySQL General Mailing List For list archives: http://lists.my

Re: Feature request related to COMPRESS and UNCOMPRESS functions

Hi! On Feb 27, Lester Hightower wrote: > To whom it may concern at Mysql AB: > > I see from the Mysql 4.1.1 CHANGELOG that new COMPRESS(), UNCOMPRESS(), > and UNCOMPRESSED_LENGTH() functions were added. That is great news, and > something I have been very interested in for a long time, as eviden

Re: Problem with regexp

On Feb 28, 2004, at 10:33 AM, Michael Stassen wrote: In the meantime, perhaps the following will help: mysql> select 'This is an [OT] Test' regexp "[ [:punct:]]OT[ [:punct:]]" as mtch; Hey, thanks - that works! ___/ / __/ / / Ed Leafe http://leafe.com/ http://op

Query cache and queries with non-english characters

Recently I've come around that mysql (4.1.0 at least) treats different queries containing non-english characters (in my case characters from cp1251 charset) as the same query and therefore returns wrong results. For example the following queries are regarded as identical while they are not: SELECT

Can't enable replication !

Hi guys - I am trying to set up replication but cannot get it enabled on the MASTER server - I have added the bin-log line in my.conf and restarted the server - no logs are created tho - Ther are no unusual message in mysql.err - and the following ' 4 test 3306 60 ' is the only entry in the

Re: Problem with regexp

Ed Leafe wrote: Hi, I'm trying to get a particular pattern to match, and a pattern that works in Python and other languages is not working in MySQL. I'm using 4.1.0 on RH Linux. I archive messages from an email list, and we have a standard that posts to the list that are Off Topic shou

Problem with regexp

Hi, I'm trying to get a particular pattern to match, and a pattern that works in Python and other languages is not working in MySQL. I'm using 4.1.0 on RH Linux. I archive messages from an email list, and we have a standard that posts to the list that are Off Topic should be labeled by inclu

Storing utf-8 on MySQL 3.23.58, and preparing for MySQL 4.1

MySQL Listers, Currently I am running MySQL 3.23, which I know is not really designed to handle utf-8. I am eagerly anticipating full utf-8 support in version 4.1. In the meantime, I am curious to know if there are any particular considerations I should take into account when storin

RE: problem with 4.0.18

Andrea Riela wrote: > Could you help me? well, I've tryed with ktrace, see my kdump: http://www.nesys.it/kdump It's possible, a pid file problem? thanks for your support Regards Andrea -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://list