RE: Optimizer Index Weirdness

Have you tried using between instead of "<= =>"? We have found that between in some cases works better than <>. Not saying it will make it use the correct index. Donny > -Original Message- > From: David Griffiths [mailto:[EMAIL PROTECTED] > Sent: Friday, July 30, 2004 9:35 PM > To: MySQ

Optimizer Index Weirdness

We have a table with 40 million rows. It has statistics on traffic from our website. Logs are processed once a night, and the data from those logs are added. Our table (traffic_boats, InnoDB) has three columns of interest: day INT yearmonth INT stem_base VARCHAR(100) There is an index on day, an

Re: FreeBSD and MySQL - mysqld eats CPU alive

adp, There are quite a few people with a similar problem with Free BSD. I don't know if they found a solution but here is a place to start looking: http://groups.google.ca/groups?hl=en&lr=&ie=UTF-8&q=freebsd+mysql+heavy+cpu&btnG=Search Mike At 01:58 PM 7/30/2004, you wrote: I have sever

RE: How do you archive db daily?

Shawn: Thank you for your reply. I did not know you could reference a Database in a select clause, I thought you could only reference tables. I do have a followup question. I assume once I have created the dbArchYesterday that mysqld has the database in memory.Is there way to: 1. write the databas

Re: Login question

In mysql, a "user" is a combination of user and hostname. Initially, there are 2 superusers, [EMAIL PROTECTED] and [EMAIL PROTECTED] They have the same privileges, but they are different users. [EMAIL PROTECTED] is for connections via unix socket (from the same machine on which the mysql serv

Re: SELECT difficulties

That won't work. For each resellerid, you'll get the minimum price and an effectively random offerid. Michael Daniel Lahey wrote: You need to use the 'GROUP BY' clause: SELECT offerid, resellerid, MIN(price) FROM A GROUP BY resellerid; Cheers, Dan On Jul 30, 2004, at 2:37 PM, Haitao Jiang wrote:

Re: Login question

> [EMAIL PROTECTED] doesn't have a password yet. You need to >mysqladmin -u root password "newpwd" > where "newpwd" is the password you want for root. > After setting the password for [EMAIL PROTECTED], you can connect with >mysql -u root -p > I'd suggest that [EMAIL PROTECTED] is a bad id

Re: SELECT difficulties

You need to use the 'GROUP BY' clause: SELECT offerid, resellerid, MIN(price) FROM A GROUP BY resellerid; Cheers, Dan On Jul 30, 2004, at 2:37 PM, Haitao Jiang wrote: Hi, there Maybe this question is not MySQL specific, but I just wondering if MySQL has any way to doing this: I have a table A like

Re: SELECT difficulties

There are possibly 3 ways, depending on your version of mysql. The manual has details for selecting the rows with the groupwise maximum. Changing that to groupwise minimum should be trivial. Michael Haitao Jiang wrote:

Re: JOIN/WHERE and index confusion

Why do you expect moving the a.timestamp restriction from the WHERE clause to the JOIN will help? Michael [EMAIL PROTECTED] wrote: Yes, I can think of two things you can try in order to speed up your query. First - try the STRAIGHT JOIN clause with one small but critical change to your statemen

Re: JOIN/WHERE and index confusion

You've yet to show us the output of EXPLAIN. You've summarized, but you haven't showed us. It's difficult to help without all the data. Perhaps we should start with the EXPLAIN for your simpler query: EXPLAIN SELECT * FROM a, b WHERE a.a_id = b.a_id AND a.timestamp BETWEEN 20040101000

SELECT difficulties

Hi, there Maybe this question is not MySQL specific, but I just wondering if MySQL has any way to doing this: I have a table A like following: offerId resellerId price -- 1r1 5 2r1 10 3r2 12 4r2 4 -

RE: soft link mysql socket?

Hello, mysql Gurus, Because of disk space issues, the data dir of my mysql DB is somewhere else other than /var/lib/mysql. I did not link /var/lib/mysql to the real data dir though. It works OK until I want to use perl DBI which complains can't connect to mysql thru '/var/lib/mysql/mysql.sock

Re: Login question

FLUSH PRIVILEGES is not necessary after SET PASSWORD or GRANT. You only need to FLUSH PRIVILEGES when you edit the mysql tables directly (INSERT, UPDATE, DELETE). Michael Michael Dykman wrote: after you ran your command, did you: mysql> flush privileges; ? Per the MySQL documentation I used the

Re: Login question

aspsa wrote: Per the MySQL documentation I used the following commands to establish login both at the local host and remotely. mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd'); Here, you set the password for the anonymous users, ''@loc

Re: JOIN/WHERE and index confusion

Yes, I can think of two things you can try in order to speed up your query. First - try the STRAIGHT JOIN clause with one small but critical change to your statement(http://dev.mysql.com/doc/mysql/en/SELECT.html): SELECT STRAIGHT JOIN * FROM a LEFT JOIN b ON a.a_id = b.a_id AND a.timesta

Re: Login question

after you ran your command, did you: mysql> flush privileges; ? > Per the MySQL documentation I used the following commands to establish login > both at the local host and remotely. > > mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); > mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd

Re: Enum or Int

Michael Dykman wrote: I hope I'm not opening an old can of worms here, but there are some design trade-offs in this decision. ENUM has the strong advantage of being able to constrain the contents to the specific expected values. It is not possible for an application insert an illegal value wherea

Re: Enum or Int

I hope I'm not opening an old can of worms here, but there are some design trade-offs in this decision. ENUM has the strong advantage of being able to constrain the contents to the specific expected values. It is not possible for an application insert an illegal value whereas using INT one would

Re: Login question

Try SET PASSWORD FOR 'root'@'localhost' = PASSWORD( 'newpwd' ); SET PASSWORD FOR 'root'@'%' = PASSWORD( 'newpwd' ); There was no username in your SET PASSWORD command. Wes On Jul 30, 2004, at 4:38 PM, aspsa wrote: Per the MySQL documentation I used the following commands to establish login both at

Re: Login question

aspsa wrote: Per the MySQL documentation I used the following commands to establish login both at the local host and remotely. mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd'); When I attempt the following locally from the Command Pro

FreeBSD and MySQL - mysqld eats CPU alive

I have several MySQL and FreeBSD installs across a few different sites, and I consistently have problems with mysqld. It will begin to eat up all of the CPU and eventually become unresponsive (or the machine will just burn). I can't seem to manually reproduce this, but given enough time a FreeBSD b

RE: Installing MySQL Databases on RAM Drive

Originally I had developed this application for the Berkeley DB but they wanted an outrageous amount of money for licencing since we were using transactions. Memory and disk weren't really constraints when I re-developed it. Time and cost were constraints though and MySQL was the fastest thing I

Login question

Per the MySQL documentation I used the following commands to establish login both at the local host and remotely. mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('newpwd'); mysql> SET PASSWORD FOR ''@'%' = PASSWORD('newpwd'); When I attempt the following locally from the Command Prompt (with Wi

Re: JOIN/WHERE and index confusion

I did not follow the discussion, but I would say you need to have a combined index on a.timestamp, a.a_id and a.c_id. Sorry if you already tried this. Stefan Am Friday 30 July 2004 21:34 schrieb Eamon Daly: > So, to confirm, short of indexing a.timestamp (which I've > done) there's no way to spee

Re: JOIN/WHERE and index confusion

So, to confirm, short of indexing a.timestamp (which I've done) there's no way to speed up the original query? SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GR

Can not compile procedure.

Hi folks, I upgraded to mysql 5.0 on win2K professional. My problem is i can not use 'delimiter' command. It does not reset the delimiter, in fact mysql does not recognize this command. Alternatively I wrote a simple procedure in a file and tried to compile it but could not compile. For both the

RE: ODBC & Stored procedures

MyODBC 3.53 will be the next majour release (not 3.52) and it will support stored procedures so please hang on a bit. At the moment we are focusing on a backlog of MyODBC 3.51 bugs/issues. Peter Harvey, Software Developer MySQL AB Office: +1 619 251-6923 Are you MySQL certified? www.mysql.com

Re: File size limit exceeded, Linux/MySQl-4.0.20-i686-icc

Its accessed over the net by the web servers. I don't think we would have file contentions. Actually, we just replaced the mysql binary with an older 4.0.16 version that we compiled and used before we started to use the 4.0,20-i686-icc. Now the db is working without any problems. I wonder if ot

Re: Self joins with a temporary table

I found this conversation when searching the archive for information about selfjoins on temporary tables. I was primarily wondering what "in one of future releases" mean. What branch and what timeframe are we talking about? stable, beta, development? in a year, a month, or? regards, Gustav Mu

Re: Installing MySQL Databases on RAM Drive

Could you store your BLOBs as files external to the database (in the OS file system) and just store the path name to the file? I don't know what your reasons for having the BLOB is, usually it's just for portability of the application. As for "saving" the HEAP tables, you would just TRUNCATE th

Re: Sum/Join Query Building

Try adding DISTINCT to your query: SELECT DISTINCT I'm not sure if that's going to work in your case, but the problem you are having seems to be duplicate rows caused by joins. A left join will always return one or more rows from the main table (VPN). On Jul 30, 2004, at 9:47 AM, Alex wrote

Re: Installing MySQL Databases on RAM Drive

Just a thought, but perhaps you could set up a master/slave on the box such that the master points to a RAM disk and the slave points to disk. Then if someone pulls the plug or the machine crashes, you'd still have almost all of the data on disk. On startup, copy the slave's files to the RAM disk,

Re: Mysql Updation problem

How are you trying to do the updates (command line, web application, etc.)? Matthew McNicol T UmaShankari wrote: Hello, Actually i am running mysql in my localpc. when i was trying to update some contents during runtime it is not updating. But mysql is running. Can anyone please tell me where

Re: Data loading

infile is a lot faster. Matthew McNicol Michael Gale wrote: Hello, I have a question about data loading using mysql 4.0.20. If you need to load let's say 50,000 items into a database. Now 50,000 is not a lot for a DB. So my question is would it be fast to load the file using the local infile wh

Apache2.0.50 Segmentation fault when using PHP 4.3.8 + MySQL 4.0.20

Hi I'm brand new to this list, so please forgive me if I'm wasting everybodies time :-). I've used a couple of days tracking this one down and just thought I would share it if anyone is strugling with the same problem. Running : FreeBSD 4.9-RELEASE-p5 Apache 2.0.50 PHP 4.3.8 MySQL 4.0.20 ...and

RE: Installing MySQL Databases on RAM Drive

Stephen, At 11:16 AM 7/30/2004, Stephen Rasku wrote: Flash drives have a limited number of writes that can be done before the drive starts to fail: http://www.kingston.com/tools/bits/bit17.asp http://www.diskonkey.com/documents/Performance_reliability.pdf (Look under "Flash

RE: Grant problem

There are precedence rules and these are discussed in the manual. http://dev.mysql.com/doc/mysql/en/Connection_access.html Since you are granting at the database level you will see the changes in the db table and not the user table. -Original Message- From: Cam To: Mysql List Sent: 7/30/04

Installing MySQL Databases on RAM Drive

I just looked into this. It looks like HEAP tables don't support the BLOB field type and we are using it. ...Stephen -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Jul 30, 2004 6:19 AM To: Egor Egorov Cc: [EMAIL PROTECTED] Subject: Re: Installing MySQL Databases

Grant problem

I'm running the scarab problem reporting system and and trying to grant some privileges to a user and don't understand why it isn't working. I have the following user that I can see in the USER table mysql> select user,host,password,Insert_priv from user where user='scarab' and host='localhost.lo

Re: ODBC & Stored procedures

Wow! the list is quiet today, I really expected someone to respond to this by now. Just in case you didn't understand the problem: He is connecting to a 5.0 database using ODBC version 3.51 and running a stored procedure. He is having problems getting return values from his stored procedures t

Re: what os to use for mysql on amd64?

On Thu, Jul 29, 2004 at 06:26:23PM +0300, Egor Egorov wrote: ... > No. I've forgot to tell that the -Max binary is linked dynamically > because it uses SSL. Is there a reason the SSL libraries can't also be linked statically? Do you recommend against running the -Max binary, because it doesn't us

RE: File size limit exceeded, Linux/MySQl-4.0.20-i686-icc

How is this table accessed and updated? Do you have file contention issues? Is the MySQL message 145? -Original Message- From: Nathan Boeger To: [EMAIL PROTECTED] Sent: 7/30/04 9:40 AM Subject: File size limit exceeded, Linux/MySQl-4.0.20-i686-icc Hello everybody, We have a few Linux/MyS

RE: Installing MySQL Databases on RAM Drive

Flash drives have a limited number of writes that can be done before the drive starts to fail: http://www.kingston.com/tools/bits/bit17.asp http://www.diskonkey.com/documents/Performance_reliability.pdf (Look under "Flash Reliability") If I can write the database to a RAM

Data loading

Hello, I have a question about data loading using mysql 4.0.20. If you need to load let's say 50,000 items into a database. Now 50,000 is not a lot for a DB. So my question is would it be fast to load the file using the local infile which is a security concern or should I be using a lo

load data in file with pipe

hello, I was tring to understand the steps to read from pipe and load using LOAD DATA INFILE? I dont understand the "x's" or the cat of tcp, can someone shed some light on this for me?? mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat < /dev/tcp/10.1.1.12/4711 > /mysql/db/x/x mysq

ODBC & Stored procedures

Hi, may be my english is poor, sorry for this. I'll be trying with odbc 3.51 and sp on 5.0 that returns out params and didn't work (at least for me). In parameters works fine. From mysql client command line out parameters works fine. The sp makes about 50 selects, this increase a lot the traffic

Re: Installing MySQL Databases on RAM Drive

At 04:45 PM 7/28/2004, you wrote: Our customers are running MySQL 4.0.17 on QNX 6.2.1. We are currently using a flash drive to store our database but we want to store it in a RAM disk to prolong the life of the drive Flash drives are solid state (no moving parts) so you can't break them. I've neve

File size limit exceeded, Linux/MySQl-4.0.20-i686-icc

Hello everybody, We have a few Linux/MySQL boxes and recently we have been having some problems with one of our databases. We have checked out the usual things (hardware, disk quotas, table limits etc...) and we have spent several days looking over newsgroups and other postings with no luck. We are

Re: Sum/Join Query Building

This has more to do with how JOINing tables work than making the sums work right. When you join tables, a new virtual table containing all possible combinations of rows from each of the tables is created. The ON clauses of each join limits which rows stay in the table (or get put into it in the

Re: MySQL and SSL

Egor Egorov schrieb: Michael Dykman <[EMAIL PROTECTED]> wrote: could someone please tell me which versions of mysql support SSL connections for both clients and replication slaves? The "-Max" build. i'd installed MySQL-Max-4.0.20-0.i386.rpm from download area of www.mysql.org, but the server does

Re: Sum/Join Query Building

Alex wrote: I'm having a problem with summing up joined tables.. can anyone help me? I have 3 tables that I am joining together vpn, dialup, and userinfo vpn has the following columns (This was a premade table so i couldn't change username into user_id to link with userinfo) username session_t

Re: selects with wildcards for field/column names

In article <[EMAIL PROTECTED]>, Jason Joines <[EMAIL PROTECTED]> writes: > Is it possible to use a wildcard for field names in a select? Nope. > For instance if I have a table with a hundred columns... ... then you have probably a broken DB design. What are you trying to do? --

Sum/Join Query Building

I'm having a problem with summing up joined tables.. can anyone help me? I have 3 tables that I am joining together vpn, dialup, and userinfo vpn has the following columns (This was a premade table so i couldn't change username into user_id to link with userinfo) username session_time and oth

Re: selects with wildcards for field/column names

Jason Joines wrote: Is it possible to use a wildcard for field names in a select? No. For instance if I have a table with a hundred columns and I want to retrieve all rows who have a certain value in any column but don't want to type in a hundred column names in my query, how do I do it?

selects with wildcards for field/column names

Is it possible to use a wildcard for field names in a select? For instance if I have a table with a hundred columns and I want to retrieve all rows who have a certain value in any column but don't want to type in a hundred column names in my query, how do I do it? I've tried: select * from db

Re: Installing MySQL Databases on RAM Drive

Why use a RAM disk? Why not just load the tables into HEAP tables? On Jul 29, 2004, at 11:51 AM, Egor Egorov wrote: "Stephen Rasku" <[EMAIL PROTECTED]> wrote: We are currently using a flash drive to store our database but we want to store it in a RAM disk to prolong the life of the drive. We want

Re: How do you archive db daily?

Why not keep your server running and just use scripted SQL to migrate the records from one DB to the other? CREATE DATABASE dbArchYesterday; USE dbArchYesterday; CREATE TABLE table1 like dbActive.table1; CREATE TABLE table2 like dbActive.table2; CREATE TABLE table3 like dbActive.table3; CREATE

RE: Date Conversion Function

Well thanks to everyone that replied to this message, here are the main details. I was looking for this as help for a DBA here that is enslaved by MSSQL and was asking me what the equivalent would be, as I do not know what mm() does in mssql myself I cannot answer that question either, I sent her t

Re: Enum or Int

There was a discussion about this topic at past. Check the previous posts.As a simple answer they are technically the same. Use what ever you want.. Best Regards, Cemal Dalar a.k.a Jimmy System Administrator & Web Developer http://www.dalar.net - Original Message - From: "Salzgeber Oliv

Enum or Int

Hello all I have a table with a field which needs to hold a yes/no or 1/0 value. Which field type is best for this ? Should i create a ENUM field with yes or no values or is it better to create an Int field for this ? What would you recommend ? Best regards Olivier Salzgeber -- MySQL General