Re: IP address to searchable number

Scott, This may not be your exact solution, but if your network address is 24 bit then you could just check against the last octet, i.e. x >= 10 and x =< 100. James --- Scott Haneda <[EMAIL PROTECTED]> wrote: > Given a IP range such as: > 12.8.197.10 -> 12.8.197.100 > > I want to store those 2

Re: SELECT and LIMIT question

Yes. I want to retrieve 5 of each. If I put LIMIT 5, the entire query retrieves only 5 for table1.col2 with value of 1 because 1 is the first in the IN (1,2,3) clause. If I put LIMIT 15, the query returns 15 rows same as above instead 5 for each 1, 2, and 3 values. This is because there are more

Date range with empty rows (Was: Intra-table join)

On Wed, 13 Oct 2004 20:25:48 -0700, Chris <[EMAIL PROTECTED]> wrote: >> create table test_table >> (d date not null, >> name char(32) not null, >> loc integer not null, >> type integer not null, >> amount integer, >> primary key (d, name, loc, type)); snip snip > This is how

Re: join syntax

What do you mean, it doesn't work? Michael Marco wrote: The query > select * from a join b on a.x = b.y; works on mySQL 3.23.56 but doesn't work on mySQL 3.23.58. I had to change it in > select * from a,b where a.x = b.y; Is it a well-known behavior or is it a bit strange? Thanks, Marco -- MySQL

Re: SELECT and LIMIT question

Why do you think it doesn't work with LIMIT? Do you want 5 of each? Michael Jay K wrote: Hi, I have multiple queries like this: SELECT col1, col2, col3, col4 FROM table1, table2 where table1.col1 = table2.col1 and table1.col2 = 1 ORDER BY col3 desc LIMIT 5 and SELECT col1, col2, col3, col4 FROM ta

Re: IP address to searchable number

In the last episode (Oct 13), Scott Haneda said: > Given a IP range such as: > 12.8.197.10 -> 12.8.197.100 > > I want to store those 2 values in a database, before I insert a new value, I > would like to test for the new values existence. > > Any idea what I should convert a IP address into in or

Re: Some basic and advanced replication questions

> (e) If you had just a one way master->slave relationship and you delete > data on the slave, that is very bad. The slave is now not the same as > the master, the record will not be re-inserted, if you go to update the > record on the master, when the command gets replicated to the slave an > err

Re: cross database joins performance hit?

I've worked on projects before where splitting up the schema into databases was used. Joins across DB's are fine, but there is another place that the performance can hit you. If you use something like perl's Apache::DBI, you will increase the number of open connections to your database. That's ass

Re: How to flush process which are in sleep state

Sleeping 'processes' are connections left open by clients. Simply change your clients to close the connection whent hey are done (don't use pconnect). Also your wait_timeout is probably still set to the default 28800 seconds. This should be set to match the idle timeout of your client (apache or wh

Re: Replication and LOAD DATA LOCAL INFILE

My best guess would be that your slave tables have slightly different data/structure than your master table. Try taking a fresh snapshot from the master and trying again. -Eric On Tue, 12 Oct 2004 10:33:24 -0700, Kenneth Lim <[EMAIL PROTECTED]> wrote: > Hi - > > I'm seeing these error/warning

Re: 2003 server problem

I have gone back to installing 4.02. I have found that no log file or my.ini is been written either. I have already successfully installed mysql 4.02 on another windoze 2003 server. so I don't quite understand why the installation is falling over. -- MySQL General Mailing List For list archives:

Re: Login error

You need to finish reading the install guide here: http://dev.mysql.com/doc/mysql/en/Post-installation.html -Eric On Tue, 12 Oct 2004 16:31:48 -0400, Kamal Ahmed <[EMAIL PROTECTED]> wrote: > Hi, > > I have MySql running on Redhat 9, and I am trying to create a New > Database. > > When I issue

Re: mysql dynamic librairie client

gcc linker will hanlde .a files using -lmysqlclient and -L /path/to/libmysqlclient.a -Eric On Wed, 13 Oct 2004 17:54:09 +0200, Romain Moyne <[EMAIL PROTECTED]> wrote: > Hello, :) > > I have installed the MySQL binary package .tar.gz version 4.1.5. It > runs very good. But in this package there

Re: fetch certain number of matches

I have a table got about 20 rows. I want my web application be able to perform search (dynamic query on this table). the search can be quite complex because we need to look at other tables through relationships. So the query may be an expensive one. But the ideal thing is to get the count of

RE: speed issue - inserts slowing down selects

Daniel, also consider selecting as high priority. That will cause select queries to jump ahead of pending insert queries. That will allow each select to only have to wait for at most one insert to complete. Cheers, -Dana -Original Message- From: Daniel Cummings [mailto:[EMAIL PROTECTED

Re: user defined table constraint

Excuse me for top-posting but Outlook Express won't put revision bars in front of your original remarks and I'm too lazy to type them all in myself ;-) Anyway, if you define one of your columns, such as user_id as a primary key, you can be sure that there will never be two rows with the same user_

Re: fetch certain number of matches

Could you possibly describe what you would like to find? Table definitions, sample data, and sample queries (especially ones that you have already tried with explanations of what was wrong with their output) would all help us to answer your question. Shawn Green Database Administrator Unimin Co

Re: user defined table constraint

That depends, can the user have more than 2 records? as in only 1 "yes" record and 1 "no" record? If that were the case you could create a unique index on (user_id, is_primary). However, I suspect that is not the case. If I remember my M$ $QL correctly, User Constraints are evaluated during INS

Re: Foreign key error

:perror 150 MySQL error: 150 = Foreign key constraint is incorrectly formed This is usually due to a missing index. To make a foreign key, both of the involved columns must come first in an index. Do you have indexes on msg_recipients.recipient_member_id and members.id? Michael [EMAIL PROTECT

fetch certain number of matches

Is there a way to ask mysql to return certain number of matches? This is very useful for search functionality.

user defined table constraint

Hi all, I have a table with these fields: user_id dept_id is_primary ('Y' or 'N') I want to make sure that there are never two rows in this table with the same user_id and is_primary='Y'. For any user_id, there can only be one primary record. In MS SQL I would define a user constra

Re: Foreign key error

run the command: SHOW INNODB STATUS; whenever you get those errors and it will give you more details. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Herman Scheepers <[EMAIL PROTECTED]> wrote on 10/13/2004 03:42:34 PM: > I tried to create a foreign key using: > > ALTER TABL

Foreign key error

I tried to create a foreign key using: ALTER TABLE msg_recipients ADD CONSTRAINT msg_recipient_member_fk FOREIGN KEY (recipient_member_id) REFERENCES members (id); and got this error: Can't create table '.\wapmagic\#sql-c50_48.frm' (errno: 150) Any ideas? Thanx Herman _

RE: Insert Delayed

Thanks for verifying that. Dan -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 12:23 PM To: Daniel Cummings Cc: [EMAIL PROTECTED] Subject: Re: Insert Delayed I verified the same error for myself, and then found: http://bugs.mysql.com/b

Re: Creating primary and foreign keys for MySQL

You haven't found the online manual yet, have you? http://dev.mysql.com/doc/mysql/en/index.html Specifically the documentation for the command you are curious about is found here: http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html Shawn Green Database Administrator Unimin Corporation - Spruce

Re: speed issue - inserts slowing down selects

One thing you could do, which may not be the best, is insert one (or some set limit) of rows at a time, then after each sleep for .25 seconds or something, so that your inserts get spread out more over time, and there is idle time between them for the selecting clients to complete. Obviously this m

Creating primary and foreign keys for MySQL

Hi All I am MySQL newby with an Oracle background. Is the following syntax suported? alter table members add primary key (id); alter table members add constraint members_uk unique (name); Thanx Herman __ Do you Yahoo!? Yahoo! Mail Address AutoC

speed issue - inserts slowing down selects

We have what is called a data grabber for quotes that is inserting rows into both InnoDb and MyIsam tables. When the data is being imported ( inserted ), it is dramatically slowing clients down that are running queries against the table. Our first attempt at a solution was to insert one row at

Insert Delayed

Does "Insert Delayed" work on any version of MySql for INNODB tables? The docs state that it does but I keep getting the error that the engine doesn't support this. TIA Dan

Re: Q: outer join w/restriction

"You should generally not have any conditions in the ON part that are used to restrict which rows you want in the result set, but rather specify these conditions in the WHERE clause" Forgive me for following the documentation!! Martin - Original Message - From: [EMAIL PROTECTED]

Re: AW: InnoDB and foreign keys

If you could post your table schema (SHOW CREATE TABLE table_name) and then give an example of the query that is slow on InnoDB that would help us give a better analysis. Right now it sounds like something is wrong, InnoDB is likely to be slightly slower than MyISAM because of transaction overhead

Re: Some basic and advanced replication questions

(a) You are mistaken. You can easily setup both as masters and then as slaves to each other. Simply enable the neccesary binary logging on both sides, then use the CHANGE MASTER TO command to set each to point at the other. Now when you do an insert or delete on either it gets replicated to the

Re: AW: InnoDB and foreign keys

Suggestions? Check your indexes. It sounds as though they aren't what they used to be. When you converted to InnoDB, did you accidentally move the data to a slower drive? If so, you may want to move it back to where you had the MyISAM tables. Shawn Green Database Administrator Unimin Corporatio

Fw: SELECT SUM + 2 JOINS

*Please* send your followups to the list. This enables others to help rather than placing the burden solely on me. It also ensures that the conversation, including the solution, appear in the mailing list archives where they can be of benefit to others down the road. If you want to copy me on your

Some basic and advanced replication questions

Hi i'm using MySQL version 4.0.20d. I was able to set up a simple replication between a master and a slave. To fully understand the replication mechanism of MySQL i would like to ask some questions (the manual does not answer all my questions): (a) in 4.0.20d there is no way for a kind of Mas

Re: mysql_insert_id() for UPDATE?

At 11:36 AM 10/13/2004, Paul DuBois wrote: At 11:10 -0400 10/13/04, Michael Ragsdale wrote: I've been using mysql_insert_id() with great success, but now I've got a problem. I'm using UPDATE to, well, update a record in a database and according to the docs... mysql_insert_id() is updated after

Fw: SELECT SUM + 2 JOINS

*Please* send your followups to the list. This enables others to help rather than placing the burden solely on me. It also ensures that the conversation, including the solution, appear in the mailing list archives where they can be of benefit to others down the road. If you want to copy me on your

AW: InnoDB and foreign keys

HELLO >> does anybody know if Foreign keys increase the performance of select querys? >> example. >> DB1 has only INNODB tables. >> DB2 has the same structure as DB1 with all possible foreign keys. >> > is the same query faster on DB1 or DB2? > >Why should it increase performance? At the moment

Re: mysql_insert_id() for UPDATE?

- Original Message - From: "Michael Ragsdale" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 13, 2004 11:10 AM Subject: mysql_insert_id() for UPDATE? > I've been using mysql_insert_id() with great success, but now I've got a > problem. I'm using UPDATE to, well, u

RE: SELECT SUM + 2 JOINS

I've been asked some questions, so here is some add'l info: Here is a snapshot of the order contents: mysql> SELECT * FROM OrderContents WHERE OrderID = 3116; +---+-+-+---+-+--+-- ---+ | ContentID | OrderID | InventoryID

Re: InnoDB and foreign keys

On Wednesday 13 October 2004 10:33 am, Ulrich Seppi wrote: > Hello people, > does anybody know if Foreign keys increase the performance of select > querys? example. > DB1 has only INNODB tables. > DB2 has the same structure as DB1 with all possible foreign keys. Huh? You might want to read what a

Re: InnoDB and foreign keys

> does anybody know if Foreign keys increase the performance of select querys? > example. > DB1 has only INNODB tables. > DB2 has the same structure as DB1 with all possible foreign keys. > > is the same query faster on DB1 or DB2? Why should it increase performance? With regards, Martijn Tonie

Re: InnoDB and foreign keys

I am not certain that Foreign Keys (FKs) are even considered when evaluating a SELECT statement. I know that the indexes that the FKs point to in either table play a major role in determining the execution plan but I don't think that the FKs actually participate in SELECT queries. Now, does ha

RE: SELECT SUM + 2 JOINS

OK, that makes better sense. Please respond to the list (you should always try keep any list responses CC:ed to the list so that everyone else can help and learn from the exchange, too) with the information that Rhino requested and I am sure someone will be able to help you work out your query

Optimize query help

Hi, We log all sessions on our webserver to a table, and then periodically run a batch process to analyze the visitors ip addresses to determine the city/region/country of origin. we use MySQL 4.1.1-alpha-standard-log on Redhat AS, installed from the mysql RPM. The query below takes about 1 seco

Re: Messure time including milliseconds

At 13:09 +0300 10/13/04, Dobromir Velev wrote: Hi, I think it is better to implement this measurment in your application - thus you'll have an estimate of the time needed to execute your procedures including the mysql calls. Otherwise you can use the BENCHMARK MySQL function http://mysql.online.bg/

mysql dynamic librairie client

Hello, :) I have installed the MySQL binary package .tar.gz version 4.1.5. It runs very good. But in this package there isn't dynamic librairie client in the directory lib/ which terminated with .so. I have just this: carotte:/usr/local/mysql/lib# ls libdbug.a libmysql.imp libmysqlclien

RE: Q: outer join w/restriction

Right you are Shawn; Harald's answer was dead-on. Of course, I missed it the first time, b/c I was distracted by the typo and his answer was subtle--it took your reply to Martin to make me realize that he'd rearranged the clauses. Many thanks to all three of you! --Chris -Original Message-

Re: MySql Configuraton on Redhat

Kamal Ahmed wrote: Hi, This is the only place where I can ask these questions, and would greatly appreciate if anyone can give me some clue on how to resolve these issues. I have MySql running on Redhat 9, and I am trying to create a New Database. When I issue the command: mysqladmin -u root ver Re

Re: SELECT SUM + 2 JOINS

I believe what you want needs two queries, one to total up on the order# the other to total up by customer. I wouldn't recommend trying to do it all at once as they are two different (but similarly asked) questions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Andrew Ku

Re: mysql_insert_id() for UPDATE?

Didn't you have to specify the ID of the record in order to UPDATE it? If not, and you updated several records, and assuming that the function LAST_INSERT_ID worked as you had expected it to work, you would have still only gotten one of the ID values for one of the updated records, right? What

Re: mysql_insert_id() for UPDATE?

At 11:10 -0400 10/13/04, Michael Ragsdale wrote: I've been using mysql_insert_id() with great success, but now I've got a problem. I'm using UPDATE to, well, update a record in a database and according to the docs... mysql_insert_id() is updated after INSERT and UPDATE statements that generate

InnoDB and foreign keys

Hello people, does anybody know if Foreign keys increase the performance of select querys? example. DB1 has only INNODB tables. DB2 has the same structure as DB1 with all possible foreign keys. is the same query faster on DB1 or DB2? regards Uli -- MySQL General Mailing List For list archives:

Re: Select from two tables not returning the expected number of rows

Ted Byrne wrote: you didn't say that (svr,started,ended,volume,who) is a unique key... If not, it would explain the result and the extra rows you get. Well... that combination of fields *should* be unique. Does a unique index need to be specified on those columns? It is possible that (svr,star

Re: my_thread_init

I believe that what you described is perfectly acceptable. The thing to keep in mind is the thread_init allocates thread specific memory for mysql, and the thread_end clears it. As such you should never execute any other mysql commands unless you have executed an init, and you should never init m

Re: SELECT SUM + 2 JOINS

It's awfully hard for anyone to help without seeing the SQL that you are trying to execute. Ideally, the full descriptions of the tables involved and a few rows of sample data would make it a lot easier for us to be able to visualize what you are doing but you sometimes get a useful answer from the

RE: Table creation Time stamp

By Jove! you are right! Sorry! I can't think of an easy way to find those dates. Mr. Tuuri, any ideas? Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Anil Doppalapudi" <[EMAIL PROTECTED]> wrote on 10/13/2004 11:04:53 AM: > i see the time stamp only for Myisam table type

Re: MySql Configuraton on Redhat

Have you gone through all of the troubleshooting options outlined here: http://dev.mysql.com/doc/mysql/en/Access_denied.html There are many links on that page to various other corrective actions you can take in order to restore your access. Shawn Green Database Administrator Unimin Corporation

RE: MySql Configuraton on Redhat

use the command below mysqladmin -u root -p supply the passwd when prompted Anil DBA -Original Message- From: Kamal Ahmed [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 8:35 PM To: Kamal Ahmed; [EMAIL PROTECTED] Subject: MySql Configuraton on Redhat Hi, This is the onl

SELECT SUM + 2 JOINS

I have a MySQL table with Order#'s (Primary Key). I want to total the number of items per order which I do through an inner join to the order contents, but then I want to also get a total on the amounts a customer has been billed to make sure there isn't a customer that has been shipped somethi

mysql_insert_id() for UPDATE?

I've been using mysql_insert_id() with great success, but now I've got a problem. I'm using UPDATE to, well, update a record in a database and according to the docs... mysql_insert_id() is updated after INSERT and UPDATE statements that generate an AUTO_INCREMENT value or that set a column val

RE: Table creation Time stamp

i see the time stamp only for Myisam table type and not for InnoDB table type. where can we find for Innodb Table type Thanks Anil DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 8:18 PM To: Anil Doppalapudi Cc: [EMAIL PROTECTED]

MySql Configuraton on Redhat

Hi, This is the only place where I can ask these questions, and would greatly appreciate if anyone can give me some clue on how to resolve these issues. I have MySql running on Redhat 9, and I am trying to create a New Database. When I issue the command: mysqladmin -u root ver Result: mysqladmi

Re: Select from two tables not returning the expected number of rows

You didn't even say that (svr, started, ended, volume, who, action) was a unique combination of values for either table. If that is the case then you will have a hard time matching rows of one table uniquely to rows of the other table. Shawn, Sorry for the lack of clarity on my part. The combina

Re: Table creation Time stamp

SHOW TABLE STATUS LIKE 'your_table_name'; See the manual for details . Michael Anil Doppalapudi wrote: Hi, where can we find the time stamp of table creation i.e when that table was created Thanks Anil DBA -- MySQL General Mailing List Fo

Re: Table creation Time stamp

use the command: SHOW TABLE STATUS http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Anil Doppalapudi" <[EMAIL PROTECTED]> wrote on 10/13/2004 10:42:11 AM: > Hi, > > > where can we find the time stamp of table creati

RE: Group By Question

Perfect! Thank you. > -Original Message- > From: Harald Fuchs [mailto:[EMAIL PROTECTED] > Subject: Re: Group By Question > SELECT category_fk, >sum(case status when 1 then 1 else 0 end) AS 'status=1', >sum(case status when 2 then 1 else 0 end) AS 'status=2', >sum(c

Table creation Time stamp

Hi, where can we find the time stamp of table creation i.e when that table was created Thanks Anil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Q: outer join w/restriction

Martin, you are correct in how you determine when to use AND and when to use OR, but that's not what the original query was trying to find If you re-read his original post, he wants this query: SELECT Applicants.AppID, Applicants.Name, Applicants.Email FROM ApplicantStatus I

Re: Select from two tables not returning the expected number of rows

Ted, You didn't even say that (svr, started, ended, volume, who, action) was a unique combination of values for either table. If that is the case then you will have a hard time matching rows of one table uniquely to rows of the other table. Try this query and you may be able to see where the

Re: Q: outer join w/restriction

A SQL AND is a restrictive filter In other words Your resultset will yield results based on how you structure your query If you structure your resultset which includes only applicants who have been seen by "Reviewer2" then state SELECT ... FROM WHERE (CONDITION1 AND Applicant.Reviewer = 'Reviewer2'

RE: Q: outer join w/restriction

Sadly, it was a typo in the email (apologies for that), but not in the query: SELECT Applicants.AppID, Applicants.Name, Applicants.Email, Reviews.Quant, Reviews.Qual FROM ApplicantStatus INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID LEFT JOIN Reviews ON Reviews.AppID

Re: keep field names unique across database?

There are two main camps in database design style. You could almost say there are three if you count the newbies who haven't "found" one of the other two yet. One camp derives their style from object oriented development. These people use tables with singular names with simple (non-unique) nam

Re: Select from two tables not returning the expected number of rows

you didn't say that (svr,started,ended,volume,who) is a unique key... If not, it would explain the result and the extra rows you get. Well... that combination of fields *should* be unique. Does a unique index need to be specified on those columns? It is possible that (svr,started,ended,volume,

Re: cross database joins performance hit?

In article <[EMAIL PROTECTED]>, "Jason" <[EMAIL PROTECTED]> writes: > I've tried to find references to if there are any design flaws with using > multiple databases or not however was unable to locate anything (but I was > told by a previous co-worker that there were performance hits). > Are ther

Re: Group By Question

In article <[EMAIL PROTECTED]>, "Fan, Wellington" <[EMAIL PROTECTED]> writes: > Hello Listfolk, > I have a table with a 'category_fk' column and a 'status' column. 'Status' > has but a tiny handful of known values, kinda like an enum. > I'd like to form a query that would give me results like:

Re: Q: outer join w/restriction

In article <[EMAIL PROTECTED]>, "Christopher J. Mackie" <[EMAIL PROTECTED]> writes: > There's something I'm not getting about how to put a SELECT restriction on a query > with an outer join. The following query: > SELECT Applicants.AppID, Applicants.Name, Applicants.Email, > Reviews.Quant, Revi

How to flush process which are in sleep state

Hi, when i issue show processlist command it gives very long list of process most of them are sleep state . is there a way to clear those processs with out killing them Thanks in advace ANil DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:h

Re: Messure time including milliseconds

Hi, I think it is better to implement this measurment in your application - thus you'll have an estimate of the time needed to execute your procedures including the mysql calls. Otherwise you can use the BENCHMARK MySQL function http://mysql.online.bg/doc/mysql/en/Information_functions.html fo

my_thread_init

Hi, I'm using the C api within a multithread environement. Is that allright if I have a function that looks like this : saveParam(){ my_thread_init(); [connexion/query/close] my_thread_end(); } Can I call it several time from the same thread ? Or do I have to do my_thread_init/end only once f

Re: Speed this up?

Alex Pilson wrote: I have this query below than seems to be running really slow. I have tried indexing key JOIN fields but it seems to have not helped. Any pointers would be greatly appreciated. (ver: MySQL 3.23.53) side note: seems to run fairly quick in 4.0.18 SELECT dl.email as download_email

Re: Select from two tables not returning the expected number of rows

mysql>select count(*) from x.table_a,y.table_b WHERE x.table_a.svr = y.table_b.svr and x.table_a.started = y.table_b.started and x.table_a.ended = y.table_b.ended and x.table_a.volume = y.table_b.volume and x.table_a.who = y.table_b.who; you didn't say that (svr,started,ended,volume,who) is a uniq