Re: optimizing query

[Steve Meyers]

On 1/18/11 10:22 AM, Simon Wilkinson wrote:

SELECT articles.* FROM articles INNER JOIN newsletters ON
articles.newsletter_id = newsletters.id INNER JOIN users ON users.id =
newsletters.user_id WHERE users.id =12 AND MONTH(articles.created_at) = '12'
AND DAY(articles.created_at) = '5' ORDER BY YEAR(articles.created_at),
LENGTH(articles.body);


Simon -

There are a few issues that are slowing down your query.  First, you're 
running functions to calculate the month and day of each article that is 
looked at.  As an aside, are you sure you don't want the DAYOFMONTH() 
function?


Second, it's ideal to have the where clause in your query filter down 
(using an index) to as few rows as possible of the first table.  Other 
tables you join should ideally be 1 to 1 from the first table.  To 
accomplish this, you would probably need the user_id in your articles table.


Another aside -- I noticed you have index_articles_on_newsletter_id as 
well as index_articles_on_newsletter_id_and_created_at.  The first index 
is redundant, the second index will take care of it.  This will slow 
down your INSERT/UPDATE/DELETE queries to some degree.


Steve

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Rewrite SQL to stop table scan

[Steve Meyers]

On 1/17/11 9:52 AM, Jerry Schwartz wrote:

[JS] I don't understand how an index on a timestamp would help. Theoretically,
each record could have a unique value for the timestamp; so the index would
have an entry for each record. Would MySQL really use that in preference to,
or in combination with, an index on devid?


You are correct.  The ideal index would be across (devid, stamp).

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Group by question

[Steve Meyers]

On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote:

mysql>  select album_id, updated_at, created_at from album_stats group by
album_id order by updated_at desc limit 8;


I believe that your problem is that the group by happens before the 
order by.  Since you're grouping, the updated_at column is not 
deterministic.  If there are multiple rows per album_id, any one of 
those rows could provide the updated_at column that you're then using to 
order by.  What you probably want is to select (and order by) the 
max(updated_at).


Steve

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Rewrite SQL to stop table scan

[Steve Meyers]

On 1/14/11 3:52 AM, Bruce Ferrell wrote:

select count(*) as count
from alerts where (unix_timestamp(stamp)>  (unix_timestamp(now()) -
'300' ) )
and devid = '244';



Bruce -

The problem is that the index is useless, because you're running a 
function on the timestamp.  What you want is this:


SELECT COUNT(*) AS num FROM alerts WHERE stamp > DATE_SUB(NOW(), 
interval 300 second) AND devid=244;


With this query, MySQL will run DATE_SUB() once, and then use the index 
on stamp (which I assume you have) to narrow down the result set.


Steve

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: I/O read performance

[Steve Meyers]

On 1/13/11 3:51 PM, Reindl Harald wrote:

Are you sure that the lags are really the query and not the connection?

I have seen on a windows server with ipv7 large lags because mysql
treid by every connect to make a dns-reverse-lookup first on ipv6
and after fail ipv4

"skip-name-resolve" in the mysql-config did the trick, but make
sure that there are up-addresses instead of hostnames in
the permissions-tables before try this


This is a very good point, I hadn't thought of this.  I always have name 
resolution turned off in my configs.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: I/O read performance

[Steve Meyers]

On 1/13/11 2:13 PM, Steve Staples wrote:

On Thu, 2011-01-13 at 13:51 -0700, Steve Meyers wrote:

On 1/13/11 1:21 PM, Steve Staples wrote:

table type is MyISAM,  it is a "customer_account" table, which holds the
email address, and the customer_id field,  the queries that are
constantly being queried is "select customer_id from customer_account
where customer_email = '' and `status`='1';


Do you have a key on customer_email?  If not, create one ASAP.  What
kind of writes happen on this table?

With MyISAM, serial-like behavior can occur under certain circumstances.
   All queries must obtain either a read lock or a write lock.  A table
can have multiple read locks simultaneously, but a write lock must have
exclusive access.  Write locks have higher priority than read locks.

What this means in practice is that if a query requests a write lock, it
is placed at the front of the queue.  Any currently-running queries are
allowed to finish, but any new lock requests are queued.  If the
currently-running queries take very long to complete (and "very long"
could be fractions of a second, depending on the application), then lots
of queries will get queued up behind the write lock that is pending.
All new read requests would need to wait for the write lock queue to
clear out before they can get back to reading from the table again.

Usually, this results in inconsistent performance under high load.  One
solution is to switch to InnoDB, since it does row-level locking.  This
is not a perfect solution for every situation, and you should do some
research before doing this.  It's probably the best solution in the long
term for you, but I can't guarantee that.

Another possibility would be to queue writes in a separate table (or
memcache, or something like that).  Then do the writes in batches.

Steve Meyers


the only writes that happen, is when a customer has their "status"
changed, password updates, or they change their name, OR when a new
customer is created.   I would say a new customer get created (insert
into) about 30 times per day, and a customer gets updated (update)
prolly about 20 times per day, and customers getting deleted or
suspended about 20 (there are rough guesses), and then customer searches
are about 200-300 times per day, and then the other part that does all
the searching, prolly about 100-200 per minute (or so).  I am really
guessing here, but i am prolly  pretty close.

the query that does the most, is:
mysql>  EXPLAIN  SELECT customer_id FROM customer_account WHERE
`full_login`='' ORDER BY `status` DESC LIMIT 1 \G
*** 1. row ***
id: 1
   select_type: SIMPLE
 table: customer_account
  type: ref
possible_keys: NewIndex5,NewIndex4
   key: NewIndex5
   key_len: 202
   ref: const
  rows: 2
 Extra: Using where
1 row in set (0.00 sec)

NewIndex5 is 'full_login', 'status'
NewIndex4 is 'full_login' (fulltext)

maybe my indexes are out of line here...   maybe i was just doing this
in haste, since the indexes are both kinda the same.

I guess maybe it is just the sheer amount of queries per second/minute
that is the issue?


That's not very many queries, honestly.  I'm not sure why you have a 
fulltext index on there, it's probably not really helping.


From what you've told us, there really shouldn't be a problem.  I'd try 
using mk-query-digest to find out if there are queries running that you 
don't realize.


Steve Meyers

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: I/O read performance

[Steve Meyers]

On 1/13/11 1:21 PM, Steve Staples wrote:

table type is MyISAM,  it is a "customer_account" table, which holds the
email address, and the customer_id field,  the queries that are
constantly being queried is "select customer_id from customer_account
where customer_email = '' and `status`='1';


Do you have a key on customer_email?  If not, create one ASAP.  What 
kind of writes happen on this table?


With MyISAM, serial-like behavior can occur under certain circumstances. 
 All queries must obtain either a read lock or a write lock.  A table 
can have multiple read locks simultaneously, but a write lock must have 
exclusive access.  Write locks have higher priority than read locks.


What this means in practice is that if a query requests a write lock, it 
is placed at the front of the queue.  Any currently-running queries are 
allowed to finish, but any new lock requests are queued.  If the 
currently-running queries take very long to complete (and "very long" 
could be fractions of a second, depending on the application), then lots 
of queries will get queued up behind the write lock that is pending. 
All new read requests would need to wait for the write lock queue to 
clear out before they can get back to reading from the table again.


Usually, this results in inconsistent performance under high load.  One 
solution is to switch to InnoDB, since it does row-level locking.  This 
is not a perfect solution for every situation, and you should do some 
research before doing this.  It's probably the best solution in the long 
term for you, but I can't guarantee that.


Another possibility would be to queue writes in a separate table (or 
memcache, or something like that).  Then do the writes in batches.


Steve Meyers

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: help with query

[Steve Meyers]

On 1/11/11 9:31 AM, Simon Wilkinson wrote:

select users.id from users where users.id in (select newletters.user_id from
newletters left join articles on newletters.id = articles.newsletter_id
where articles.newsletter_id is null);


I think this would do what you require:

SELECT
  u.id AS user_id,
  COUNT(DISTINCT n.id) AS num_newsletters,
  COUNT(DISTINCT a.id) AS num_articles

FROM
  users u
  JOIN newsletters n ON n.user_id=u.id
  LEFT JOIN articles a ON a.newsletter_id=n.id

GROUP BY
  u.id

HAVING
  num_newsletters > 0
  AND num_articles = 0

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Replication - multiple masters

[Steve Meyers]

I've come up with pretty much the same solution to that problem.  Here's 
an alternative solution that requires a lot more work, but is prettier.

Set up a MySQL proxy server (of sorts).  What it will do is act as a 
slave to multiple masters, merge the log files it receives from them, 
and act as a master.  It will not have any databases, or anything like 
that.  The MySQL protocol is fairly simple, so I can't imagine this 
would be TOO hard.

Anyway, we're not actually going to do it that way, but I just thought 
I'd point out that there is an alternative.

Thiago Conde Figueiró - nTime wrote:
   Hello, list.  I have seen this subject come up here more than once, 
but I have never seen a solution I liked.

   The drill is pretty standard: I have several (4 to be more precise) 
MySQL 4.1.2 servers running 13 different databases.  All of them use 
MyISAM tables, except for one, which uses some InnoDB tables.  I didn't 
want to (okay, I don't have the budget to) set up 4 slaves, one for each 
master, so I thought how could I use a single server to be slave for all 
4 master servers.

   I don't like the obvious solution, which is to run 4 instances of 
MySQL on the slave, because this wastes my precious memory.  I also 
wouldn't like to keep dumping and importing tables all day long, because 
mysqldump locks the master servers while doing the dump.

   So I came up with something that made me happy and seems to be 
working.  Well, for at least 4 days now.  It goes like this:

   First, replicate all master databases on the slave server just like 
the replication FAQ specifies (i.e.: lock tables, show master status, 
write down info, mysqldump, unlock tables).  At this point you can 
choose your slave server to be slave for any of your masters.

   Now the tricky part begins.  For each of your master servers, do the 
following on the slave:

1. change master to master #1
2. start slave
3. see that it is replicating ok
4. stop the slave io_thread (stop slave io_thread)
5. wait until the sql_thread has processed all the relay log (you must 
see "Has read all relay log; waiting for the slave I/O thread to update 
it" as the output of mysqladmin processlist | grep "system user")
6. stop the slave (stop slave) and then copy master.info to master.1 
(this is master #1, remember)

   Now repeat steps 1 to 5 for all the masters (#2, #3 etc).  I had to 
do that four times, so I ended up with master.1 to master.4.  Everytime 
you reach step 5 your relay log should have been fully processed by the 
slave sql_thread.  Running change master ensures another relay log is 
created from scratch.

   All you need now is rotate through your master.# files.  This is an 
example for my situation (4 masters):

a. Read line #2 from master.1; this is the master_log_file parameter
b. Read line #3 from master.1; this is the master_log_pos parameter
c. Read line #4 from master.1; this is the master_host parameter
d. Read line #5 from master.1; this is the master_user parameter
e. Read line #6 from master.1; this is the master_pass (shame on me, I'm 
not using passwords, so I don't know if it will work on that situation)
f. On mysql do a "change master" using the values gathered above on the 
parameters
g. Still on mysql do a start slave

   To point the slave to the next master, you repeat steps 4, 5, 6, 1, 
2, 3 (on this order) from the previous instructions (of course, use the 
values your got from a-g on step #1).  I wrote a little script (attached 
to this message) that does all the job of rotating through the masters 
for me.  I have it on the system crontab to run every 10 minutes.  So 
far, so good.

   So... what am I missing here?  If anyone wants more details, just 
ask.  I'd be pleased to help.

   Oh yes.  I don't have to say to try this stuff at your own risk, 
right? :-)

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

Re: Feature Request: UPDATE 'error codes' or mysql_affected_rows() to be more "accurate"

[Steve Meyers]

http://dev.mysql.com/doc/mysql/en/INSERT.html
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 
4.1.0), and a row is inserted that would cause a duplicate value
in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is
performed.
Daevid Vincent wrote:
I'm developing a program where I try an "UPDATE ... LIMIT 1" and if
mysql_affected_rows == 0, then I know nothing was updated and so I do an
INSERT. I find this is much cleaner and the majority of the time, I'm going
to do UPDATES, so I didn't want to waste a SELECT (even though I hear
they're "cheap"). I'm doing these queries several times per second.
however... Of course UPDATE doesn't 'ERROR" if the record doesn't exist, it
just didn't do anything (therefore that's why I use the mysql_num_rows() to
check). The problem is that if I am actually doing an UPDATE to a record
where nothing actually changed in the existing record, I still get
mysql_affected_rows() equal to 0. *grrr*.
It would be extremely useful to somehow get a result of maybe -1 if I tried
to update a record that didn't exist, versus a result of -2 if I tried to
update a record that did exist, but mySQL didn't change anything.
I don't know exactly what I'm asking for other than a way to know the
difference...
At the very least, it seems to me that if I update a record that exists
already (even if no data changed), I should still get mysql_affected_rows()
0 (since in theory I matched something, even if mySQL behind the scenes
didn't change the data).  

Out of curiosity, if I have a TIMESTAMP column, would that solve my problem,
since mySQL should be forced to update that TIMESTAMP right?? [btw, I know I
could try this idea, but I'm home and my code is at work right now and I
just had the idea! ;-]
http://daevid.com

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

Re: Very Strange data corruption

[Steve Meyers]

David Griffiths wrote:
I'm not sure what the sql standard says on the matter, but Oracle, DB2 
and Postgres would through an exception. In fact, there is a page on 
MySQL "gotachs" to document MySQL behaviour when it differs 
significnatly from other databases (like the first datetime field in a 
table getting a value if none is provided during insert).
I assume you mean "timestamp", not "datetime".  Not trying to be picky, 
I just don't want to confuse anyone out there.

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

Re: Replication A->B->C

[Steve Meyers]

From http://dev.mysql.com/doc/mysql/en/Replication_Options.html
--log-slave-updates
Normally, updates received from a master server by a slave are not 
logged to its binary log. This option tells the slave to log the updates 
performed by its SQL thread to the slave's own binary log. For this 
option to have any effect, the slave must also be started with the 
--log-bin option to enable binary logging. --log-slave-updates is used 
when you want to chain replication servers. For example, you might want 
a setup like this:

A -> B -> C
That is, A serves as the master for the slave B, and B serves as 
the master for the slave C. For this to work, B must be both a master 
and a slave. You must start both A and B with --log-bin to enable binary 
logging, and B with the --log-slave-updates option.

MaFai wrote:
Hello all:
I have set the repliaction between A and B server successfully.
While the B server replicate the data from A server,we fould the relay log refreshing.But the master log has no change.
Since we want to add a Server C to replicate the Server B, the master log must existing and refreshing.
Should I set the Server C 's master log to the relay log(Server B's)?
Or an other way to set the Server B updating the master log while it replicate the Server A.
We have make sure that the 'log-bin' parameter in our my.cnf file and the master log file existing.

Any idea apprecated.

Best regards. 

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

MySQL and NPTL

[Steve Meyers]

Has anyone else experienced this bug?
http://bugs.mysql.com/bug.php?id=868
We've been seeing this problem on several of our servers (see the last 
comment to the bug).  MySQL just hangs occasionally, it happens about 
3-4 times per month.  We have 13 database servers, so that unfortunately 
increases our odds.

I just wondered how many other people have seen it, and if the suggested 
"export LD_ASSUME_KERNEL=2.2.5; mysqld_safe &" has worked for anyone. 
In order to turn that on, I will need to take our site down completely, 
which is (of course) not desirable.

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

Re: Newbie query

[Steve Meyers]

Wait for 4.1, it will have multi-table updates in it.  Until then,
you'll have to use two separate queries.

Steve

On Wed, 2001-11-21 at 07:28, Charles Allen wrote:
> Hi,
> 
> A v. basic question from a mySql newbie:
> 
> I want to update a table based on the contents of another table. The SQL I
> am trying to execute is:
> 
> "update servers set servers.responsecount=servers.responsecount+1 where
> servers.gameid = lastsnapshot.gameid;"
> ie: Increment a responsecount for rows where the gameid in the servers table
> matches the gameid in the lastsnapshot table. I am getting a "Unknown table
> lastsnapshot" error from mySql. Can someone point me in the right direction
> for this.
> 
> Thanks,
> 
> Charles.
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: innodb and use of indices

[Steve Meyers]

On Wed, 2001-11-21 at 05:19, Heikki Tuuri wrote:
> The way to defragment InnoDB tables, or tables in any database, is from time
> to time to dump and reimport them. That can give a significant performance
> boost.
> 

That is actually not entirely true.  For MyISAM tables, one simply needs
to run "OPTIMIZE TABLE table_name" from time to time.  I think the end
result is the same, but it's atomic.

Does OPTIMIZE TABLE work for InnoDB?

Steve


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Beginner SQL Question

[Steve Meyers]

On Sat, 2001-11-10 at 05:53, John Morton wrote:
> Hi,
> 
> Can someone tell me why this will not work, please
> 
> SELECT * FROM tbl1 WHERE column =(SELECT * FROM tbl2 WHERE intcolumn =
> 15);
> 

There are no subselects in MySQL (yet).  However, they're usually a bad
idea anyway, which is why it's never been such a big rush to get them
in.  In this case, you'd be better served by a join.  Try:

SELECT tbl1.* FROM tbl1, tbl2 WHERE tbl1.column = tbl2.column AND
tbl2.intcolumn = 15;

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Need a help

[Steve Meyers]

On Sat, 2001-11-10 at 04:33, Artur Kowal wrote:
> My question is how in MySQL I can get information 
> is  table exists or not ( I don't want some query
> that return
> error but kind of message from serwer !)
> I'm beginner and I have  no idea how to do it
> smart and  properly
> Please  help !!!
> 

Try:

SHOW TABLES LIKE 'tablename'

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: taiwan.com

[Steve Meyers]

On Sat, 2001-11-10 at 04:50, DL Neil wrote:
> > It seems that once again a taiwan.com address has been subscribed
> > to this mailing list. In case you haven't noticed, taiwan.com
> > has a very broken mailer, which sends an error message to
> > whoever posts anything on this list. Apart from this being
> > annoying (especially since the date on taiwan.com's mail server
> > is one day into the future), I have in the past received several
> > spam mails from taiwan.com. I have no reason to believe that
> > my address was not harvested from this list, so I think it would
> > be in the interest of everyone here to have taiwan.com banned.
> > Is this a reasonable request, or should I just ask the admin
> > of my mail server to add a filter?
> >
> > //C
> >
> > database,sql,query,table
> 
> 
> Carl,
> 
> I have just spent over three weeks trying to reestablish email contact with a 
>personal friend in the States.
> Someone at my ISP has apparently been ACCUSED of spamming someone at his ISP. The 
>reaction was to 'bounce' all
> email between the two domains. The fact that I have my own sub-domain completely 
>escaped their
> attention/interest. The baby was thrown out with the bathwater! I have finally 
>managed to get the two ISPs
> talking (not an easy task when one is 'bouncing' the other's domain!) Duh!!!???
> 

While I sympathize with you, I have to agree somewhat with Carl on this
one.  While I haven't gotten any spam from taiwan.com (or at least none
that I've noticed -- I have all the taiwan.com mail filtered out here)
the fact remains that their mailer is busted.  It only looks at the TO
and FROM headers, while it should only look at the RCPT and FROM SMTP
commands.  It's really rather amusing, until you get dozens of them.

Because of the above problem with the mailer, I don't believe that the
MySQL list is making it to whoever the intended recipient is anyway,
since their address is not in the TO field.  If the taiwan.com address
was unsubscribed, I don't think it would actually have any detrimental
affects on anyone...

Just my 2c.

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query optimization

[Steve Meyers]

Jeff,

Let's do some math here first.  Your table has 47 Million rows.  You
have a primary key on an int, and indexes across 22 chars and 10 chars. 
You have 512MB of key buffer.

Now, assuming the indexes just have information on relevant fields
(they're actually bigger than this since they have a row pointer too)
you need 47M*4 + 47M*22 + 47M*10 bytes of memory for your keys.  This
works out to 47M * (4 + 22 + 10) = 47M * (36) = 1.6GB of memory to store
your keys.  You only have 512MB.  Therefore, since MySQL can't keep the
key in memory, it has to swap parts out to disk as it works on your
query, which causes a huge performance decrease.

If you don't need to search on inequalities or partial matches, I would
recommend adding two columns -- an integer hash value for each of the
char columns.  Index those, instead of the char columns, and do your
lookups on those.  In this scenario, you will need 47M * (4 + 8 + 4) =
717MB of memory, which is much closer to what you have.  If you aren't
actively using the primary key, you may be able to squeeze the other two
indexes in your available RAM.

For a table that big, you should probably buy a little bit more RAM, if
it will fit in your machine.  RAM is cheap :)

Steve Meyers

On Fri, 2001-11-09 at 18:08, Jeff Isom wrote:
> I'm trying to figure out how to optimize a query on a fairly large table.
> I've been reading the MySQL documentation and have tried a few of the
> suggestions, but not seem to have much effect on the query.
> 
> The table contains three columns:
> 1) int unsigned
> 2) char(12)
> 3) char(10)
> 
> The table has 47,000,000 + rows.
> 
> I have my key_buffer set to 512M.
> 
> I have a primary key index on column 1, a multicolumn index (col2,col3) and
> another single column index for col3.
> 
> The columns are not unique, in fact, some column 2 has as may as 1,000,000
> rows with the same value.
> 
> I am running MySQL 3.23.37 on RedHat 6.0 with 1 Gig memory and a single 700
> Mhz processor.
> The hard drive is a single 18 Gig SCSI drive.
> 
> I am searching on col 2 only, col 3 only, or col 2 and col 3.
> 
> The queries on single columns can take as long a 6 minutes to execute.
> 
> Any suggestions on how I can further optimize the setup to get better
> results?
> 
> Thanks,
> Jeff



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: New fork of MySQL

[Steve Meyers]

> >> In fact, I already emailed the developers yesterday, and asked if I 
> >> could
> >> pay to have the particular feature I wanted prioritized at this time.
> >
> >Strange how open source developers don't always answer to the almighty 
> >dollar, eh?
> 
> 
> 
> I asked kindly that you not turn this into a personal attack on me.  Can I
> please ask you to stop
> 
> I am showing you respect by not responding on this point, other than to say
> please don't go there.
> 

Maybe you misinterpreted what he said -- I didn't read that as a
personal attack.  I think he was commenting on the MySQL developers, not
you.


> >Adding subselects to MySQL is a feature that many, many people have 
> >requested;
> 
> 
> How many?   And how many users of MySQL are there?  And how many of MySQL
> could there potentially be?
> 

Some of the most common newie questions on this list have to do with
subselects -- "even Access has subselects" :)

> 
> > I haven't searched the list archives, but I've 
> >been subscribed to this list for a long time, and I can't recall even 
> >one other person requesting that the DEFAULT behavior be modified.
> 
> 
> That is my pet issue perhaps.  Obviously one would not fork over one small
> issue like that.  You are dragging the other thread into this one.  I am
> thinking of a much wider issue, which is how can I be sure that my
> investment in and use of MySQL will not be overcome by other forces which
> desire that it be something very different.  When I first authorized the
> use of MySQL, I was told that is was focused on simplicity, speed, and
> every improving SQL compliance (i.e. that the little thorns would not be
> ignored forever).
> 

Some of the biggest complaints about MySQL's SQL non-compliance have to
do with subselects and referential integrity.

> You may very well be correct, that it is alarmist to assume that the little
> SQL mistakes won't be fixed fully soon.  And that other little issues that
> keep a product from being perfect at the fundamental level, won't be
> ignored.  You may be right about that.  Then again, you may not be.  But I
> have investment to worry about.  For others who have investment to worry
> about, they may look at 4.0 and ask themselves what they are getting, and
> whether they feel secure about the improvement that has been made since the
> last major milestone.
> 
> For me, I would have rather seen many issues towards further stability and
> correctness, versus launching into other huge markets (embedded and
> high-end servers).  It is not like MySQL's market was any where near
> saturated before 4.0.  In fact, one of the rules in business is if
> something is working then continue doing that thing.  Don't break it.  And
> take incremental steps away from current success.
> 
> There is no way you launch into new markets without having growing pains.
> 

If you look at the historical record, you will see that MySQL kept on
developing 3.22 until (and even a little beyond) when 3.23 was declared
stable.  Nobody pretends that 4.0 is stable yet, so I would imagine that
active development will continue on 3.23 for some time.  I'm sure the
MySQL developers would appreciate your help in maintaining the current,
stable 3.23 branch.

I understand your concerns -- the company I used to work for took a long
time to finally convert from 3.22 to 3.23.  In fact, there are people on
this mailing list still using 3.22.

Perhaps you could contribute to the main 3.23 branch until 4.0 becomes
stable, and then perhaps make a deal with MySQL to continue the active
development on that branch for them.

There are a lot of MySQL 3.23 users, and I would just like to see them
all benefit from both your changes and the MySQL developers changes. 
Forking the code just makes that more difficult, as then a choice must
be made as to which benefits you'd like to have.

Anyway, as it's GPL'ed, you can pretty much do whatever you want, and my
opinion is just one opinion among many, I'm sure!  Good luck with
whatever you do, but one way or another I hope we can all share the
benefits of your changes.  If you do fork, perhaps it would be
appropriate to post announcements of new versions on this list?

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: New fork of MySQL

[Steve Meyers]

On Thu, 2001-11-08 at 23:42, DownloadFAST.com wrote:
> 
> Just so you know I am not blowing wind on possible speed enchancement,
> please let me add that one of my former talents was assembler code.  Not to
> blow my own horn, but simply to state as fact relevant here, I was able to
> speed up Painter's core paint routines by perhaps 30 - 50%.  So although
> algorithmic changes are usually the largest wins, that is an example one
> possible way to try to get more speed on some crucial indexing routines
> perhaps.
> 
> I'd have to dig into the source before I could say specifically.
> 
> Then again right now, my personal focus is simply to get some refinements
> more quickly and with less politics.
> 

I guess my main concern is that it seems like your main reason for
forking is political disagreement with developers, and making the code
better is only a secondary reason.  If that's the case, I respect your
decision, but I think the best solution for the end users would be to
work out your differences of opinion and try to work together.  If your
goals are too far different from the MySQL project's goals, then of
course maybe a fork is the only good solution.  

However, I haven't seen anything in your reasoning that would be
contrary to the goals of the MySQL developers.  On the other hand, MySQL
is known for being fast, and for being easy to use for beginning users. 
I'd like to see what specific refinements you're talking about -- the
main reason for version 4.0 is to allow a lot of the features that are
on the TODO list.

I'm not trying to be disagreeable, I'm just not quite convinced yet and
would like to hear more from you about your reasoning and justification
for forking the code, as opposed to contributing to the main MySQL code
(even if it is in the 3.23 branch).

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: New fork of MySQL

[Steve Meyers]

> I have not read the MySQL license in detail.
> 
> Does it allow someone or a group to start another fork of the source that
> is independent from the current developers?
> 

The MySQL source is under the GPL.  Any fork must also be under the
GPL.  You may sell your forked MySQL, but you must also provide the
source code.


> If yes, is any one else interested in starting a fork in which the primary
> goal would be to improve the smaller todos and performance for small sites
> (the majority who use MySQL)?
> 
> It seems to me that the direction of MySQL is now heading more towards big
> sites given the features in 4.0.
> 
Even though I run a small site, I very much like the direction MySQL 4.0
is headed.  Features like foreign keys, triggers, and subselects can
help small sites as much as big sites (subselects are definitely the
least useful feature of those three though...).

Features like replication (already in 3.23) are definitely geared more
towards big sites.

> I understand we would have to donate our work back to open source and I
> don't see a conflict with that.  This would remove MySQL core group from
> the annoyance of people like me who just want a solid 3.23 with the little
> refinements done.  For those who outgrow our product, they could easily
> migrate to the full MySQL 4.x and later.
> 
> Let me know if you think my idea has merit.  But please no personal attacks
> and all that other noise.  Just to the point if we can.
> 

I think it would be more useful to work on the main branch and add extra
value to it, such as Heikki has done.  That way all users of MySQL can
benefit from your fixes, etc.

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: String composite key vs auto_increment

[Steve Meyers]

> That's not really true.  You can use an auto_increment field as the primary
> key and create a seperate UNIQUE index that combines both the firstname and
> lastname fields that will be inforced for inserts.
> Usually its easier to work with integers as primary keys, especially when
> you reference them in other tables and such.  I assume its slightly faster
> for MySQL to work with shorter integers than longer strings as primary keys
> but I could be wrong.
> 
> Chris
> 

It's not just slightly faster -- it's WAY faster, especially as your
tables grow larger.

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: String composite key vs auto_increment

[Steve Meyers]

> > Let's say I have a table of authors with columns for last name and first
> > name.  Is there any general guideline re using a separate integer for the
> > primary key as opposed to a combination of the last and first names as the
> > key?  I ask because by using the names, this would prevent duplicate entries
> > into the db, right?  Whereas using a separate integer key would not prevent
> > this--you'd have to search for the author to see if it was already in the db
> > before inserting to avoid dupes, right?
> 
> Yes. Yes.  On the second point, you would want to make a [non-unique] key on
> the first and last name columns.
> 

I think he actually wants a unique key here, since he wants to prevent
duplicate entries.

> 
> 
> > Assume I am not concerned about there being two different "Joe Smith"
> > authors that are different people.  I only want to associate an author name
> > with a book.
> 
> The column you seek is an integer column with the auto_increment flag, e.g.:
> 
> CREATE TABLE authors (
>   last_name char(64) DEFAULT '' NOT NULL,
>   first_name char(64) DEFAULT '' NOT NULL,
>   author_idx integer NOT NULL AUTO_INCREMENT,
>   PRIMARY KEY( author_idx ),
>   KEY( last_name(20), first_name(20)),
>   KEY( first_name(20))
> );
> 
> The key on only first_name is there in case you query on just the first_name.
> 
> The manual will tell you lots more about auto_increment and how keys are used.
> 
> b.

If searches will always be on exact matches, then use a hash value for
the primary key -- it's an integer, will allow quick lookups on the
author name, and will prevent duplicates.  In essence, it gives the best
of both worlds, with one exception: you can't do partial lookups or <>
type searches.

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: varchar in the foodchain

[Steve Meyers]

> 
> It won`t alway give you better performance. But take a table like this:
> (id int unsigned auto_increment,
>  emailadress char(255)
> )
> 
> Suppose the average emailadress is 30 characters. If you have more than a
> trivial amount of entrys you are wasting a lot of space. Space which all
> has to be cached bij your OS. Space you will all have to read from disk if
> you do a like search or cannot use an index or whatever.
> 

Tables with variable length rows can get ugly if not optimized
frequently.  We had one which was taking 2.5 sec to do an indexed query,
and when we optimized the table it dropped to .2 sec.

But as long as you keep your tables optimized, variable length rows can
be faster.  The main efficiency concern, though, is the size of your
indexes.  MySQL keeps your indexes in memory, so smaller indexes are
better.  That is why it is best to use hash values when indexing long
character strings (or even fairly short ones).  A 32-bit hash value of a
20-character field takes 1/5 the space.  That means five times as much
key can be in memory at once.

Hope that makes sense...

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Setting a DEFAULT value

[Steve Meyers]

On Sun, 2001-11-04 at 13:52, Chris Williams wrote:
> I'm trying to create at table that inserts a default value into a field if
> nothing is supplied. If in interpret the documentation correctly this should
> be accomplished by something like this:
> 
> CREATE TABLE Location (LocationID VARCHAR(10) NOT NULL PRIMARY KEY, Name
> VARCHAR(50),  Address1 VARCHAR(50), Address2 VARCHAR(50), City VARCHAR(50),
> State VARCHAR(50), Zip VARCHAR(10), Phone VARCHAR(20), Email VARCHAR(50),
> URL VARCHAR(75) , Icon INT DEFAULT 1)
> 
> Where the Icon field will get set to 1 if no value is supplied upon insert.
> 
> When I insert a row such as:
> INSERT INTO LocationTEMP VALUES (100,1,2,3,4,5,6,7,8,9,'')
> 
> zero (0) gets inserted in the Icon field.
> 
> It appears DEFAULT doesn't work.
> 

No, you're telling it to insert a value -- namely, '', which becomes a
0.  Try NULL instead, or better, try a query that doesn't set that value
at all, like:

INSERT INTO Location (LocationID, Name, Address1, Address2, City, State,
Zip, Phone, Email, URL) VALUES (100, 1, 2, 3, 4, 5, 6, 7, 8, 9)

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Copying DB to DB

[Steve Meyers]

On Sat, 2001-11-03 at 08:33, [EMAIL PROTECTED] wrote:
> Hi.  I have already written a php / mysql search engine, so i have a 
> vague idea of what i'm doing.  But i do have a query no seems to 
> be able to answer.
> 
> Is it possable to copy a row from one table, to another table?
> Both tables have the same field names, and settings.  And i can 
> easily "insert" data manually.  But i was wondering if i could simply 
> "copy" the data from DB to DB
> 
> TIA
> 
Do you want to copy from table to table, or db to db?  You switch
between the two.

For the first, see the INSERT INTO table SELECT ... syntax.
For the second, extend that by using the db.table syntax for the table
you're selecting from.

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: mysql question

[Steve Meyers]

> This may be very obvious but being a new mysql admin user how do I do what
> you say above, to delete the user from the user table. There doesn't
> appear to be any examples of this in the docs.
> 
You have a database named "mysql".  In there, there is a table named
"user".  It's just a normal table, you can delete rows from it.  For
example "DELETE FROM user WHERE User='fred'" would delete the user named
"fred".

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: UPDATE table1 FROM table2

[Steve Meyers]

On Fri, 2001-11-02 at 09:57, Paul DuBois wrote:
> At 9:47 AM -0700 11/2/01, Steve Meyers wrote:
> >That's a dangerous solution.  If there are more columns in test2 than ID
> >and Value, the REPLACE will delete those values.  As you noted in the
> >manual, the old record is deleted before the new record is inserted.
> >
> >Multi-table updates should happen in 4.1, I think, which is due out
> >fairly soon.
> >
> >If you want to do it Rick's way, try the following:
> >
> >REPLACE INTO test2 (test2.id, test2.value, test2.other)
> >SELECT test1.id, test1.value, test2.other FROM test1, test2 WHERE
> >test1.id=1 and test2.id=test1.id
> 
> What if there's no existing record to replace?
> Won't the SELECT select no rows, with the result that the statement
> does nothing?
> 

That's true.  However, he's trying to emulate a multi-table update, so
that would actually be the desired result.  I think so, anyway.  I avoid
using REPLACE instead of UPDATE like the plague, as it can wreak havoc
if you ever change your table structure...

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: UPDATE table1 FROM table2

[Steve Meyers]

That's a dangerous solution.  If there are more columns in test2 than ID
and Value, the REPLACE will delete those values.  As you noted in the
manual, the old record is deleted before the new record is inserted.

Multi-table updates should happen in 4.1, I think, which is due out
fairly soon.

If you want to do it Rick's way, try the following:

REPLACE INTO test2 (test2.id, test2.value, test2.other)
SELECT test1.id, test1.value, test2.other FROM test1, test2 WHERE
test1.id=1 and test2.id=test1.id

This will do the same thing, but will keep the value of the "other"
column.  Repeat for as many columns as necessary.

Also, Rick, isn't it a bit unnecessary to use LIKE in your example?

Steve Meyers

On Fri, 2001-11-02 at 06:50, Rick Emery wrote:
> Funny you should ask that.  There was a similar questoin answer on the PH-DB
> mailing list (different poster, though)
> Answer is:
> 
> REPLACE INTO test2 (test2.ID, test2.Value)
>   SELECT test1.ID, test1.Value FROM test1 WHERE test1.ID LIKE '1'
> 
> From manual:
> "...REPLACE works exactly like INSERT, except that if an old record in
> the table has the same
> value as a new record on a unique index, the old record is deleted
> before the new record is
> inserted"



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Can I use the concept of Effective Date with MySQL?

[Steve Meyers]

Rich and Alejandro,

Have I got a query for you! :)

Using Alejandro's example, here is a query that would work:

SELECT s.trans_id, b.price FROM sales s, rates a, rates b WHERE
a.item=s.item AND b.item=a.item AND a.date < s.date GROUP BY s.trans_id,
b.item, b.date HAVING b.date=MAX(s.date)

Steve Meyers

> At 07:52 PM 11/1/01, Alejandro Zuzenberg wrote:
> >I need to select the appropriate price for a product in a table, and the
> >product has a compund key with 2 fields: product number and date.
> >For every transaction with a certain product, I need to retrieve the price
> >that was 'current' at the specific time when that transaction took place.
> >
> >In joining two tables, I need to relate the price of a product to every
> >sales transaction where that product was used, not only by product number
> >but also by the effective date for that price.
> >
> >How can  I do this? Is there a 'closest' function (the highest value that is
> >< or = ) to be used in a WHERE statment ?
> >I've been searching for this for hours and could not fnd a solution with
> >MySql.
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Binary Keys?

[Steve Meyers]

Mark,

That does look like a bug, or at least a much-needed feature.  I've
verified that this still doesn't work with the latest 4.0 code.

On Thu, 2001-11-01 at 11:54, Mark Jenison wrote:
> Hi all,
> 
> I created a table with the key defined as binary(15), then inserted a
> record like this:
> 
> insert into table values (0x010203040506070809000102030405,...)
> 
> I can retrieve the row with 
> 
> select * from table 
> 
> and display it via my test program as '123456789012345' (I convert it from
> bytes to a string).
> 
> However...I cannot retrieve the record specifically using:
> 
> select * from table with key=0x010203040506070809000102030405
> 
> How can I retrieve this record via the same data I created it with?
> 
> (For some reason, select * from table with key=0 selects the row. Go
> figure)
> 
> -- 
> Mark Jenison



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Having trouble using LIMIT

[Steve Meyers]

> But when I add in the LIMIT statement it returns no records
> 
> $prep = "SELECT id,name FROM go.objectives WHERE subjects_id='1' AND 
> subjectHeadings_id='2' LIMIT 1,5 ORDER BY displayOrder";
> 
> $sth = $dbh->prepare($prep);
> 
> $sth->execute;
> 
> 

http://www.mysql.com/doc/S/E/SELECT.html

The LIMIT goes after the ORDER BY.  On a related note, maybe you should
check for MySQL error codes when you run a query :)

Steve Meyers


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Upgrading to MyISAM table type

[Steve Meyers]

You might have a corrupt table.  For 3.22 format, you'll need to use
isamchk, I think...  It's been a long time since I've worked with ISAM
tables.

Now that I look closer at your email, though, it is probably the fact
that your id's start with 0, not 1.  The alter table actually dumps the
data into a new table.  It may be inserting the 0 as a 1
(auto_increments tend to do that -- it can be annoying at times).  Then
the 1 fails because of the duplicate key.  Seems rather silly for an
alter table to do, though, so let me know what you find out.

If the problem is with the 0, just change it to a 156, alter, and update
it back to 0.  Update always works fine with auto_increment columns.

On Tue, 2001-10-30 at 15:17, Rich Duzenbury wrote:
> I've recently upgraded from 3.22.32 to 3.23.43 and I'd like to convert my 
> tables to the MyISAM format.
> 
> According to the docs regarding the upgrade 
> (http://www.mysql.com/doc/U/p/Upgrading-from-3.22.html),
> this is the procedure:
> ALTER TABLE table_name TYPE=MyISAM
> 
> On one of my tables, I receive the following error:
> ERROR 1062: Duplicate entry '11' for key 1.
> 
> It sounds reasonable enough, but the table is already defined as uniquely 
> keyed.
> 
> My last mysqldump shows the create statement as:
> 
> CREATE TABLE JOB (
>jobid int(10) unsigned default '0' NOT NULL auto_increment,
> 
>... many fields omitted for brevity ...
> 
>   PRIMARY KEY (jobid)
> );
> 
> There are only 156 rows in this table currently.  The job id's run from 0 
> to 155, and I can't see any duplicates.
> 
> SELECT jobid, count(*)
> FROM job
> GROUP BY jobid
> HAVING count(*) > 1;
> 
> returns no rows.
> 
> Any clues as to how to proceed?
> 
> Regards,
> Rich
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Query help...

[Steve Meyers]

David,

First of all, please post to the list in the future.  I'm not always
available to help with problems, and others may benefit from the
problem/solution.

I would change your query to the following:

SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice
  FROM archive a, archive b
WHERE a.status='s' AND a.addrdsp IS NOT NULL
  AND a.addrdsp = b.addrdsp AND a.solddate < b.solddate
  AND date_add(a.solddate, interval 1 year) > b.solddate
ORDER BY a.addrdsp, a.solddate
LIMIT 200;

There are other ways to do it, but really none of them are all that
pretty :)

On Tue, 2001-10-30 at 12:27, David Wolf wrote:
> Steve,
> 
> I have one other (more complex) query I need some help on.. And you were
> great help in the first query I did. I have a database with ~300,000 rows. I
> want to find all properties that have been sold more than once within a x
> month period (say, 12). I've tried:
> 
> SELECT DISTINCT a.addrdsp,a.listdate,a.solddate,a.lpricea,a.sprice
>   FROM archive a, archive b
> WHERE a.status='s' AND a.addrdsp IS NOT NULL
>   AND a.addrdsp = b.addrdsp AND a.solddate !=b.solddate
>   AND year(a.solddate) = year(b.solddate)   <--- This is obviously a bad
> query line
> ORDER BY a.addrdsp, a.solddate
> LIMIT 200;
> 
> 
> I can get it to work 'sort-of' It doesn't show properties where there are
> more than 1 sale, and it doesn't (obviously) work where the two sold-dates
> are with 12 months of each other.
> 
> Can you help? Thanks in advance,
> 
> David
> 
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: bad practice to have a primary key field whose value changes?

[Steve Meyers]

On Tue, 2001-10-30 at 13:10, Bennett Haselton wrote:
> I'm creating a database where one of the tables stores data about news Web 
> sites, and I'm using the URL of the site as a primary key field.  This 
> field value might change occasionally.  I'm wondering if this is bad 
> practice, especially since foreign keys in other tables might point to the 
> news site table.  MySQL doesn't enforce referential integrity, so if I 
> change the URL of a particular site, then another table in the database 
> might have a reference to a row that no longer exists.
> 

The latest version of the InnoDB handler enforces referential integrity.

> So I assume the "right" way to do this would be to use an auto-incremented 
> number as the primary key field, and then have other tables refer to that 
> as their foreign key.  My problem with this is that I like to be able to 
> dump the contents of the table and see something meaningful without having 
> to refer to other tables.  Say I have an "articles" table in the database, 
> and one of the fields is a foreign key referring to the news site in the 
> "news sites" table where the article was found.  If I dump the contents of 
> the "articles" table, all that I'll see is a number; then I have to go look 
> in the "news sites" table to find out which actual site that number 
> corresponds to.  From this point of view, it's a lot easier just to use the 
> news site URL as the key field in the "news sites" table -- then that's 
> what can be used as the "foreign key" in the articles table.
> 
There are other reasons to use an auto-increment field.  For one,
indexes on integers are faster and much more efficient than indexes on
strings.  Another alternative to auto-incremented fields would be to use
a hash of the url as the primary key.  Then you can effectively look up
based on the url, while using a numerical index for speed and
efficiency.  That would not solve the foreign key problem though.

> What would be ideal would be to use auto-incremented numeric fields as 
> primary key fields, and then have a special field in each table designated 
> as the "user-friendly field".  That way, when you want to view the contents 
> of a table, the "table viewing" algorithm can take each field marked as a 
> foreign key, go to that table, look up the "user-friendly" string for that 
> row, and display that instead.  This would satisfy the requirements in both 
> paragraphs above.
> 
> Is there already a way to do this, and if not, which of the two options 
> above do people usually use?
> 
>   -Bennett
> 
Generally people do not put "user-friendly fields" in tables.  That's
what joins are for :)




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: What are MYSQL_RES limits ? -- update

[Steve Meyers]

Mario,

Can you send a sample of the code that is causing this to happen?  I have a couple 
ideas, but I would need to see the actual code to be sure.

You mention a "probable cause".  Have you done anything to verify this (like get rid 
of the update and see what happens)?  When you run the same query at the MySQL prompt, 
do you get the full result set you're looking for?
 
Steve Meyers


> -Original Message-
> From: M. A. Alves [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, October 30, 2001 11:23 AM
> To: [EMAIL PROTECTED]
> Subject: What are MYSQL_RES limits ? -- update
> 
> 
> On Tue, 30 Oct 2001, M. A. Alves wrote:
> > I seem to be hitting a ceiling in MYSQL_RES (mysql_use_result). 
>  I select
> > 2 or 3 items but only 2295 are returned.  What might be 
> the cause
> > of this?
> 
> The probable cause is I was accessing the same table at the same time (but
> thru different connections).  However I am surprise MySQL cannot cope with
> this kind of concurrent access.  Or can it?  One access was selection and
> the other was updating.
> 
> Thanks a lot,
> 
> -- 
>,
>  M A R I O   data miner, LIACC, room 221   tel 351+226078830, ext 121
>  A M A D O   Rua Campo Alegre, 823 fax 351+226003654
>  A L V E S   P-4150 PORTO, Portugalmob 351+939354002
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: update/join question

[Steve Meyers]

> 
> what i want is:
> 
> UPDATE users SET userinfo_set=1 where (users.userid=userinfo.userid);
> 
> aka, i want to set a flag in every row in users if there is a row 
> in userinfo with the same userid.
> 
> i've been SELECT INTO OUTFILEing (with a join on the two tables) 
> and LOAD DATA INFILEing to deal with this. is there a better way?
> 
> thanks!
> 

There will be!  Version 4.1 should have multi-table updates.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: PLEASE HELP - HowTo: Forign Keys within MySQL?

[Steve Meyers]

Use the latest verson of InnoDB, it supports foreign key constraints.   
See the documentation for more info on how to declare foreign keys.

Steve Meyers

On Fri, 2001-10-26 at 06:13, McGrotty, Charles wrote:
> Hello,
> 
> I have tables in a user administration database and need to link different
> tables with FK's.
> 
> How do I do this?
> 
> for example:
> 
> I have two tables, one called applications, the other called
> forum_moderators.
> 
> Applications Catches all form submissions.
> Forum_Moderators hold the details of active/current moderators
> 
> I want to link these two tables by rsm_id in applications, and fk_rsm_id in
> forum_mod's.
> 
> How would i do this in MySQL and or PHP?
> 
> 
> Table Structure:
> for those that need a clearer picture of my layout, I have supplied the SQL
> statement to create the tables
> 
> #
> #
> # Table structure for table 'applications'
> #
>  
> DROP TABLE IF EXISTS applications;
>  
> CREATE TABLE `applications` (
>   `rsm_id` tinyint(4) NOT NULL auto_increment,
>   `first_name` varchar(20) NOT NULL default '',
>   `last_name` varchar(20) NOT NULL default '',
>   `city` varchar(10) NOT NULL default '',
>   `state` char(2) NOT NULL default '',
>   `dob` varchar(10) NOT NULL default '',
>   `forum_nick` varchar(20) NOT NULL default '',
>   `forum_select1` varchar(30) NOT NULL default '',
>   `forum_select2` varchar(30) NOT NULL default '',
>   `forum_select3` varchar(30) NOT NULL default '',
>   `bio` blob NOT NULL,
>   `email_addr` varchar(100) NOT NULL default '',
>   `processed` set('0','1') NOT NULL default '',
>   `add_date` date NOT NULL default '-00-00',
>   `accept_denied` set('A','D') NOT NULL default '',
>   `acc_dec_date` date NOT NULL default '-00-00',
>   PRIMARY KEY  (`rsm_id`),
>   UNIQUE KEY `email_addr` (`email_addr`),
>   UNIQUE KEY `forum_nick` (`forum_nick`),
>   UNIQUE KEY `rsm_id` (`rsm_id`),
>   KEY `rsm_id_2` (`rsm_id`),
>   KEY `forum_nick_2` (`forum_nick`),
>   KEY `email_addr_2` (`email_addr`)
> #
> 
> #
> # Table structure for table 'forum_moderators'
> #
>  
> DROP TABLE IF EXISTS forum_moderators;
> CREATE TABLE `forum_moderators` (
>   `formod_id` tinyint(4) NOT NULL auto_increment,
>   `name` varchar(40) NOT NULL default '',
>   `city` varchar(10) NOT NULL default '',
>   `state` char(2) NOT NULL default '',
>   `forum_nick` varchar(20) NOT NULL default '',
>   `mod_forum_1` varchar(30) NOT NULL default '',
>   `mod_forum_2` varchar(30) NOT NULL default '',
>   `mod_forum_3` varchar(30) NOT NULL default '',
>   `email_addr` varchar(100) NOT NULL default '',
>   `tremination_date` date default NULL,
>   `fk_rsm_id` tinyint(4) NOT NULL default '0',
>   PRIMARY KEY  (`formod_id`),
>   UNIQUE KEY `formod_id` (`formod_id`,`forum_nick`),
>   KEY `formod_id_2` (`formod_id`)
> ) TYPE=MyISAM;
> #
> #
> 
> Regards,
> Charlie
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Column data type conversion Q

[Steve Meyers]

Yes, and yes.  They're both documented in the manual.

http://www.mysql.com/doc/A/L/ALTER_TABLE.html
http://www.mysql.com/doc/I/N/INSERT.html
 
Steve Meyers


> -Original Message-
> From: Tony [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 25, 2001 12:57 PM
> To: [EMAIL PROTECTED]
> Subject: Column data type conversion Q
> 
> 
> Not sure if this post worked the first time (I never saw it come 
> in), so a 
> thousand pardons if this is a re-post.
> 
> I come from the school of hard knocks, and so it seems my MySQL 
> indoctrination is no exception.  2 million rows of 'varchar' data 
> must be the 
> least efficient type to sort by.
> 
> I need to change a column data type from 'varchar(10)' to 'int' type.
> 
> I created another table, with the changed data type on the one column.  I 
> thought I read that nested selects were not supported by MySQL, but is it 
> possible to do something like this:
> 
>  insert into new_table values (select '__enter-some-sort-of-on-the-fly-
> data-type-conversion-on-a-column-or-two__' from old_table);
> 
> 
> Or, is there an ALTER TABLE command on the existing table.  Surely, this 
> would take a long time on a couple million rows.  I am running 
> MySQL 3.23.36 
> and RedHat 7.1 (2.4.3-12)
> 
> Kind regards,
> Tony
> 
> --botspeak:  sql, query, database
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query help...

[Steve Meyers]

Sure there could be problems if it's large.  In my explanation (in a different 
message) I noted that it wasn't the most efficient query in the world, but he wanted 
one query to get that answer.
 
Steve Meyers


> -Original Message-
> From: Woolsey, Fred [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 25, 2001 9:58 AM
> To: [EMAIL PROTECTED]
> Subject: RE: Query help...
> 
> 
> Quite a neat trick... but might there be problems with cross join
> performance if the table is large?  This is based on the assumption that
> MySQL first creates the cross product, then culls out the rows that don't
> meet the criteria.
> 
> Cheers,
> Fred Woolsey



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL problem with Traffic and Updates

[Steve Meyers]

> Today after my server got a real traffic hit for the first time since I
> installed PhpAdsNew (a MySQL/PHP Ad software), MySQL was crashed. 
> Here's how
> it happened: watching the "top" load levels, I saw it slow creep 
> up about to
> 3, then rockets quickly to 30s, 40s, and on!! MySQL was at this 
> time taking
> up 100% of both of the processors in the server. There were about 40 or so
> MySQL processes spawned at this point. Doing a 'mysqladmin processlist'
> command on the server showed me a VERY LONG LIST of processes that were
> open, a lot of which were PhpAdsNew UPDATE commands.
> 
This can happen with MyISAM tables, where table locking is an issue.  It should only 
be a problem if some ugly queries are happening though...

Here's the rundown (all queries are assumed to be on the same table):

1. Somebody runs a SELECT that takes a while to complete
2. Somebody runs an UPDATE or DELETE (or possibly INSERT, depending on the 
circumstances)
3. The UPDATE has to wait for the SELECT to finish to begin processing
4. All subsequent queries have to wait for the UPDATE to finish
5. Depending on configuration, you may run out of active MySQL threads to handle the 
incoming queries
6. The CPU spends all of it's time spawning new threads to handle the incoming 
connections, instead of finishing the SELECT that's holding up traffic
7. The increase in memory usage is enormous...
8. If things are really bad, the server will eventually crash

Possible solutions:

1. Get rid of all long SELECT statements
2. Add proper indexes so that long SELECT statements become short
3. Switch to InnoDB if #1 and #2 aren't feasible, the multi-versioning and row-level 
locking will eliminate those problems

There are other possible solutions.  I've done some work in similar environments, if 
you'd like I can help you find the best way to get your database working smoothly.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: LEFT JOIN?

[Steve Meyers]

> >I have these 3 tables:
> >T1: T2:
> >Service  Application  |  Application  Machine
> >---
> >network  DHCP   DHCP mach1
> >Database SQLSQL  mach2
> >EmailExchange   SNMP mach2
> >network  SNMP   Exchange mach5
> >=
> >T3:
> >Service  Doc/notes
> >--
> >Network  www.google.com
> >Database Test
> >Traning  test
> >
> >
> >$query= "select distinct T3.service, T1.application,
> >T2.machine from T3,T1 LEFT JOIN T2 ON
> >T1.application=T2.application where T1.service=T3.service";
> >

Yes, you need another left join.

select distinct t3.service, t1.application, t2.machine from t3 left join t1 on 
t1.service=t3.service left join t2 on t1.application=t2.application

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: foreign key

[Steve Meyers]

Alex,

Please reply to the list in the future, so that others can answer your questions and 
also see the answers as well.

You can find info on how to use referential integrity on 
http://www.mysql.com/doc/C/R/CREATE_TABLE.html

For more InnoDB specific information about it, see http://www.innodb.com/
 
Steve Meyers


> -Original Message-
> From: Alex [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 25, 2001 6:56 AM
> To: Steve Meyers
> Subject: RE: foreign key
> 
> 
> Hello Steve
> I was reading this email that you did and Looks like you know this better
> than I do
> therefore I want to ask you How come can I make the relationships between
> tables in mysql max which is the one that I am using now maybe 
> you have any
> place to suggest or maybe you can explain me a little bit more thank you
> very much
> 
> Alex and is better if you replay this to [EMAIL PROTECTED] thank you again
> 
> 
> 
> -Original Message-
> From: Steve Meyers [mailto:[EMAIL PROTECTED]]
> Sent: Sabado, 20 de Octubre de 2001 12:20 a.m.
> To: Sandra Rovena Frigeri; [EMAIL PROTECTED]
> Subject: RE: foreign key
> 
> 
> InnoDB tables support foreign keys with full referential integrity
> constraints.  They do not yet support cascading deletes and 
> updates.  You'll
> want to use the MySQL-Max version of MySQL to get support for 
> InnoDB tables.
> 
> Steve Meyers
> 
> 
> > -Original Message-
> > From: Sandra Rovena Frigeri [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, October 19, 2001 8:25 PM
> > To: [EMAIL PROTECTED]
> > Subject: foreign key
> >
> >
> > hi,
> >
> > which windows mysql version supports foreign key and its
> > "complete" implementation: referential integrity constraint,
> > avoid delete parent key, etc.?
> > or
> > Which foreign key features that has the best windows mysql version?
> >
> > thanks,
> >
> > sandra.
> >



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query help...

[Steve Meyers]

> > select distinct a.username, a.ip from users a, users b where a.ip=b.ip &&
> > a.username != b.username;
> > 
> Thanks!! Worked like a dream! I'm not quite sure why it knew to pull only
> ip's that are in there more than once though?
> 

Here's what it's doing.  It does a join on itself where the ip is the same but the 
usernames are different.  For any ip that only has one username, the join condition 
fails.  But there may be extra data from the join, so I do a distinct to clear that 
up.  Probably not the most efficient query in the world, but it should be fine if it's 
not done frequently, or there isn't much traffic on the table.

Hope that helps!

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query help...

[Steve Meyers]

I think I understand.  This should work...

select distinct a.username, a.ip from users a, users b where a.ip=b.ip && a.username 
!= b.username;
 
Steve Meyers


> -Original Message-
> From: David Wolf [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 24, 2001 3:17 PM
> To: Steve Meyers; [EMAIL PROTECTED]
> Subject: Re: Query help...
> 
> 
> It's still not quite doing what I want. I only want a list of IP's where
> there are more than 1 instance of an ip--and then display each of the
> multiple occurrences of the single ip (and repeat for each ip 
> which has more
> than 1 occurrence)..
> 
> The queries are all so close, but, don't give what I want :(
> 
> David
> 
> - Original Message -
> From: "Steve Meyers" <[EMAIL PROTECTED]>
> To: "David Wolf" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Wednesday, October 24, 2001 2:38 PM
> Subject: RE: Query help...
> 
> 
> I think you're looking for:
> 
> SELECT username, ip, count(*) FROM users GROUP BY 1, 2
> 
> Steve Meyers
> 
> 
> > -Original Message-
> > From: David Wolf [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, October 24, 2001 12:21 PM
> > To: [EMAIL PROTECTED]
> > Subject: Query help...
> >
> >
> > I'm trying to come up with a query to do the following... I'm not having
> > lots of luck :(
> >
> > The table is a user database. The columns I'm interested in 
> are: username,
> > lastip. I'm interested in pulling information out that would show
> > usernames
> > for each IP that appears more than once in the database.. i.e.
> >
> > usernameip
> > persona1.1.1.1
> > personb1.2.3.4
> > personc1.1.1.1
> >
> > I'd be interested in seeing persona and personc (both have 1.1.1.1 ip's)
> >
> > I've tried..
> >
> > SELECT lastip,count(*) FROM users GROUP BY lastip;
> >
> > but that only gives me an unordered list of the # of times an 
> IP is used..
> >
> > SELECT distinct(count(*)) FROM users GROUP BY lastip;
> >
> > but that doesn't join the info -- nor give me the ip's -- just frequency
> > (when I add 'lastip' to the select I get an error)
> >
> > I'm thinking that I'm going about this the wrong way.. But, I 
> can't quite
> > get a clue.
> >
> > Can anyone help me out here?
> >
> > Thanks!
> >
> > David Wolf
> >
> >
> >
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Query help...

[Steve Meyers]

I think you're looking for:

SELECT username, ip, count(*) FROM users GROUP BY 1, 2
 
Steve Meyers


> -Original Message-
> From: David Wolf [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, October 24, 2001 12:21 PM
> To: [EMAIL PROTECTED]
> Subject: Query help...
> 
> 
> I'm trying to come up with a query to do the following... I'm not having
> lots of luck :(
> 
> The table is a user database. The columns I'm interested in are: username,
> lastip. I'm interested in pulling information out that would show 
> usernames
> for each IP that appears more than once in the database.. i.e.
> 
> usernameip
> persona1.1.1.1
> personb1.2.3.4
> personc1.1.1.1
> 
> I'd be interested in seeing persona and personc (both have 1.1.1.1 ip's)
> 
> I've tried..
> 
> SELECT lastip,count(*) FROM users GROUP BY lastip;
> 
> but that only gives me an unordered list of the # of times an IP is used..
> 
> SELECT distinct(count(*)) FROM users GROUP BY lastip;
> 
> but that doesn't join the info -- nor give me the ip's -- just frequency
> (when I add 'lastip' to the select I get an error)
> 
> I'm thinking that I'm going about this the wrong way.. But, I can't quite
> get a clue.
> 
> Can anyone help me out here?
> 
> Thanks!
> 
> David Wolf
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: What is MySQL-max?

[Steve Meyers]

> > http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Da
> > tabase_Administration.html#mysqld-max
> > 
> I just tried that, it gave me a page not found error.
> 

That's because the link is too long for one line -- try copy and pasting, making sure 
to get the entire link.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Sessions

[Steve Meyers]

> > > How to know how many sessions are currently open against a MySQL
> database ?
> >
> > MySQL manual 7.28 SHOW Syntax
> >
> Thanks for your answer, but I need to know the names of the users 
> currently
> logged in the database.
> Regards,
> 

And he told you exactly where to find it.  Try SHOW PROCESSLIST
http://www.mysql.com/doc/S/H/SHOW_PROCESSLIST.html

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Slow multi-column index

[Steve Meyers]

> I'm wondering whether this has something to do with the following words of
> wisdom in the reference manual:
> "For tables that changes a lot you should try to avoid all VARCHAR or BLOB
> columns. You will get dynamic row length as soon as you are using a single
> VARCHAR or BLOB columns."
> 
> but I don't see why this should matter. Why does it matter that 
> row lengths
> are dynamic ? Surely the index doesn't just specify a row "index" that is
> multiplied by the row length ? Surely it contains some sort of row data
> offset to allow for variable row lengths.
> 
> 

I've seen it happen -- we had a table that was taking 2-3 seconds for a keyed lookup, 
and it should have been in the miniscule range.  We OPTIMIZEd the table, and after 
that the queries in question executed in 0.1 or 0.2 sec.  However, this was after 
several months of abuse -- it shouldn't be the case on a newly created table.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: select stmt problem

[Steve Meyers]

> Well, count(*) is not a column, it is a function of a column.
> When it searches, it doesn't know the result of count so you 
> cannot specify 
> it in the where clause.
> 
> This would work, but you have all the records retrieved, not only 
> the ones 
> who have more than 10 in the group:
> 
> select hpnumber,count(*) as counts from Mobile_Ringtone_Manialogs
> where datesent between '2001-09-24' and '2001-10-24'
> and (returncode > 0 and returncode < 10) group by hpnumber order by
> counts DESC
> 
> To do what you want, you would need to do a subquery. That's where 
> PostgresSQL has an advantage over it.
> 
> Right now, you need to do two steps:
> Get all the records with the counts associated to them.
> When processing the data, ignores those having a count under 10.
> 

Usually, when somebody suggests that you need a subquery in a SELECT statement, 
they're just plain wrong.  In my experience, I've never run across a real-world nested 
SELECT that couldn't be expressed a different (and more efficient) way.  For deletes 
and updates, the nested SELECT's are much more useful.

In this case, you need to put your clause "counts > 10" in a HAVING clause.  Try this:

select hpnumber,count(*) as counts from Mobile_Ringtone_Manialogs
where datesent between '2001-09-24' and '2001-10-24' and 
(returncode > 0 and returncode < 10) group by hpnumber HAVING counts > 10 order by 
counts DESC

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Formatting Large amounts of Text into Mysql

[Steve Meyers]

Are you sure it doesn't have the line feeds in the database?  Neither MySQL nor PHP 
will strip them out, unless you do some fancy work to make it do that.  I would guess 
that your real problem is that when you display it in your HTML, it shows up without 
"returns".  This is standard HTML practice -- try running the PHP function nl2br() on 
the data before displaying it.
 
Steve Meyers


> -Original Message-
> From: tim gales [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, October 23, 2001 9:31 PM
> To: [EMAIL PROTECTED]
> Subject: Formatting Large amounts of Text into Mysql
> 
> 
> Hi.  I am trying to have type in a story into a form "textfield" and
> submit it using PHP into "blob" formatted field mysql database.
> 
> When I submit, all the "returns" are removed and all the text runs
> together like on ebig paragraph.  Is there a way to have mysql or PHP
> recognize the "returns" and "empty lines" from the "textarea" field and
> rememeber them so when I call them up from the database the formatting
> looks just like when I typed it in the text field?
> 
> Any help would be very much appreciated.Thank you
> Tim Gales
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Length limit of 500 on primary keys?

[Steve Meyers]

> I am not understanding why having a hash and the full url in the 
> database would not take care of the collisions.  Even if you had 
> 10 collisions for a 16 bit hash (say), if your query was:
> SELECT ... WHERE hash=thehashvalue AND url='theurl' you would get 
> very fast lookups on the hash and the url comparison would not 
> add much to the query at that point.  You could even do a partial 
> index on the url, e.g.  "KEY( hash, url(200))".
> 
> b.
> 

That would work, but it was my understanding that they want the key itself to take 
care of uniqueness.  If not, then you can do it that way.  However, I would not 
include part of the URL in the index -- see my previous message about the key buffer.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Length limit of 500 on primary keys?

[Steve Meyers]

> > At a previous job, we tested a 32-bit hash function by running it
> > against hundreds of thousands of unique URL's stored in our
> > database.  We found one collision.  A 64-bit hash is billions of
> > times better (4 billion, to be exact).
> 
> Good to know.  I wonder how many collisions I'd find if I ran it over
> every URL listed in the directory www.yahoo.com.
> 
> Which 64 bit hash function did you use?  Invent your own, or something
> "off the shelf"?
> 

In a previous message, I failed to mention one of the main reasons you would NOT want 
to use a 500-character primary key.  MySQL uses a key buffer to keep as much index 
information in memory as possible.  The longer the key, the less info it can keep in 
memory, and the more often it will have to swap to disk.  If your key doesn't fit in 
the key buffer, my tests have shown that there is a HUGE performance loss.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Length limit of 500 on primary keys?

[Steve Meyers]

> > At a previous job, we tested a 32-bit hash function by running it
> > against hundreds of thousands of unique URL's stored in our
> > database.  We found one collision.  A 64-bit hash is billions of
> > times better (4 billion, to be exact).
> 
> Good to know.  I wonder how many collisions I'd find if I ran it over
> every URL listed in the directory www.yahoo.com.
> 
> Which 64 bit hash function did you use?  Invent your own, or something
> "off the shelf"?
> 

We found a public domain one on the net see 
http://www.burtleburtle.net/bob/hash/evahash.html for some sample code.  It's only a 
32-bit hash though.  However, that same page appears to have instructions for a 64-bit 
hash function as well, but I haven't tried it at all.  I'd be curious to know how many 
collisions you find hashing all the URL's in yahoo's database :)  I don't know how 
long that would take, but if you do it I'd like to hear the results.

Since the hash function takes a key and an initial value, you could try running it 
with two different initial values and/or keys.  This would give you effectively a 
128-bit hash, which you could store across two fields in MySQL.  I'm guessing that the 
64-bit hash will probably be good enough though.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Tree in SQL

[Steve Meyers]

I understand that you only have one table.  The query I gave you joins the same table 
against itself, and aliases it to a, b, and c.  The only question was whether the 
depth is always the same.
 
Steve Meyers


> Hi
> 
> I have one table and the depth is not always the same.
> 
> Anyway thanks :)
> 
> > If the depth of the tree is always the same, it is actually
> > fairly easy.  For instance, if there are three levels:
> >
> > SELECT c.group_id FROM table a, table b, table c WHERE a.group_id
> > = 12345 AND b.group_id = a.parent_group_id AND c.group_id =
> > b.parent_group_id
> >
> > If you don't know the depth, you'll have to do a recursive search
> > (keep on querying for the next parent until you run out of parents).
> >
> > Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Tree in SQL

[Steve Meyers]

If the depth of the tree is always the same, it is actually fairly easy.  For 
instance, if there are three levels:

SELECT c.group_id FROM table a, table b, table c WHERE a.group_id = 12345 AND 
b.group_id = a.parent_group_id AND c.group_id = b.parent_group_id

If you don't know the depth, you'll have to do a recursive search (keep on querying 
for the next parent until you run out of parents).
 
Steve Meyers


> -Original Message-
> From: Daniel Łaś [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 22, 2001 3:34 PM
> To: Lista MySQL
> Subject: Tree in SQL
> 
> 
> Hi
> 
> I have a table in database which describes tree structure of 
> article groups.
> In this table I have:
> 1. group_id - group identifier
> 2. parent_group_id - identifier of parent group
> I need to select history, from top to wanted group.
> I can't change the database design and ofcourse this is MySQL 
> 3.23 database.
> Can somebody help me ?
> 
> Thanks
> 
> 
> Daniel Łaś
> e-direct Polska sp. z o.o.
> WWW: http://www.e-direct.pl
> E-mail: [EMAIL PROTECTED]
> 45-072 Opole ul. Reymonta 45
> tel. +48 77 44 26 073
> fax. +48 77 44 26 074
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Length limit of 500 on primary keys?

[Steve Meyers]

> > The problem is that he has it as a primary key, so he wants it to be
> 
> > unique as well as indexed.  The best solution (and MUCH MUCH MUCH 
> 
>  > more efficient) would be to hash each of the four columns, and create
>  > a primary key on that.  Integer keys are much faster and memory-
>  > efficient than string keys.
> 
> Granted, but there's still the problem that the hash may not be unique, 
> thus defeating the purpose of the primary key.
> 
> I really need a longer primary key. Why is there a limit in the first 
> place, and if there *is* a limit, why is it not configurable at 
> runtime or 
> database creation time?
> --
> Shankar.

If you use a good 64-bit hash, I doubt you'll run into any uniqueness problems.  MySQL 
will support that as a 64-bit BIGINT.  You especially should not have any problems if 
you hash each column, then do the primary key across the four hashes.

I'm not sure why there is a limit, but I'm also not sure why anybody in their right 
mind would want a unique index that long :)

At a previous job, we tested a 32-bit hash function by running it against hundreds of 
thousands of unique URL's stored in our database.  We found one collision.  A 64-bit 
hash is billions of times better (4 billion, to be exact).

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MyISAM API

[Steve Meyers]

It would actually be nice if the full source were available off the website as is, not 
just in the .tar.gz file.  What I mean is, just untar the source to 
http://www.mysql.com/source/ and let people browse it there if they wish to.  People 
often ask questions on the list that could be answered in the source, but it is 
difficult to point them to it.  For instance, people asking what various error codes 
translate to...  It would be nice to just point them to 
http://www.mysql.com/source/somepath/somefile.h.

Just my 2c.
 
Steve Meyers


> -Original Message-
> From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 22, 2001 10:07 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: MyISAM API
> 
> 
> DL Neil writes:
> > > Wendy Dillon writes:
> > 
> > Sinisa,
> > 
> > On behalf of those of us who take advantage of the 'ready made' 
> versions (eg my Win download) [or who should not be
> > allowed near naked flames, C compilers, or buttons marked "Do 
> NOT press"], could these examples of 'internal
> > documentation' be reproduced and made available on a suitable 
> website please?
> > 
> > =dn
> > 
>  
> 
> It is actually publicly available in our BitKeeper repository. 
> 
> -- 
> Regards,
>__  ___ ___   __
>   /  |/  /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]>
>  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, FullTime Developer
> /_/  /_/\_, /___/\___\_\___/   Larnaca, Cyprus
><___/   www.mysql.com
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Best choice for index

[Steve Meyers]

> What is the best choice for my index on this query?
> 
> SELECT  id_team,
> sum(IF(m.id_visitor = t.id_team,m.visitor_score,
> m.home_score)) AS But_pour,
> sum(IF(m.id_visitor != t.id_team,m.visitor_score,
> m.home_score)) AS But_contre
> FROM tab_teams t, tab_matchs m
> WHERE   t.id_level =4
> AND(m.id_visitor = t.id_team OR m.id_home = t.id_team) AND
> m.season = 2
> GROUP BY id_team;
> 

There's not a whole lot you can do about that query, it's not easy on the database.  
You can have an index on t.id_level and m.season, but I'm not sure that having one on 
t.id_team, m.id_visitor, and m.id_home will help, because of the OR in your joining 
clause.

Maybe the best thing to do is cache the results somewhere else :)  Perhaps in 
tab_teams, keep a running total of points for and points against, and update it every 
once in a while.  That way you won't have to run this query too often.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: faster inserts & updates

[Steve Meyers]

There are a couple ways to do this.  As some others have suggested, you could drop the 
keys first, insert the data, and then restore the keys.  I think this is the second 
fastest method.  Here are my picks, in speed order:

1. LOAD DATA INFILE -- this is very fast, and will not update the keys until you're 
done
2. drop keys, insert, then restore keys -- this is not much of an option if you'll 
have other queries executing on the table at the same time
3. explicitly lock the table, do your inserts, and unlock the table -- again, not an 
option if you'll have other queries executing
4. use the extended insert syntax -- see http://www.mysql.com/doc/I/N/INSERT.html

Options #2 and #3 are probably a tossup in speed, and #3 is actually a little easier 
to do.  If you use #4, you may have to break the inserts up into smaller groups -- you 
may not be able to get all those inserts through in one query very easily, depending 
on the total size.  PHP might choke a little on a 1.5 million byte string, also...  If 
you can get them all in one query successfully, then #4 will probably be faster than 
#2 and #3.  You may also find that some combination of them works best for you -- for 
instance, lock the table, do a few extended inserts, and unlock it.  As well as not 
updating the index until all inserts are done, the extended insert also cuts down on 
query overhead.

Hope that helps!
 
Steve Meyers


> -Original Message-
> From: Priya Ramkumar [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 22, 2001 4:01 AM
> To: [EMAIL PROTECTED]
> Subject: faster inserts & updates
> 
> 
> Hi
> 
> I have a PHP program which adds records to a MySQL table. The number of
> records in the table after the execution of the program is around 
> 28,000. I
> also have select & update queries in the program on the same table. The
> process takes a very long time to complete all insertions to the table. I
> already tried "Insert Delayed" on a sample segment of data but 
> found that it
> does not speed up things. Can anyone suggest a method  so that the
> insertions & updates to the table can be faster?
> 
> Thanks for any help.
> 
> Regards,
> Priya
> 
> 
> 
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: how to get the correct result -- Thrid Time --

[Steve Meyers]

Adrian,

I will suggest once more that you replace all tabs with a space as well, and maybe all 
carriage returns as well (for good measure).

This time I'll write out the queries for you:

UPDATE hotel SET nome_hotel=REPLACE(nome_hotel, "\t", " ");
UPDATE hotel SET nome_hotel=REPLACE(nome_hotel, "\r", " ");

Then, when that's finished, you might have to try replacing all double spaces with 
single spaces again, until the data is correct.
 
Steve Meyers


> -Original Message-
> From: Adrian D'Costa [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 22, 2001 2:29 AM
> To: Clyde Jones
> Cc: Mysql Mailing List
> Subject: Re: how to get the correct result -- Thrid Time --
> 
> 
> On Sat, 20 Oct 2001, Clyde Jones wrote:
> 
> > Did you try that? replace should recursively replace ALL double spaces,
> > if it is a problem just run and rerun the query
> > 
> > UPDATED hotel
> >  set nome_hotel  replace(nome_hotel, "  ", " ")
> > 
> > until all the double spaces are gone.
> 
> Ok.  I tried it but of no use.  It replaced three records and after that
> none even though I tried again and again...
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Max int value of char field? How to find...

[Steve Meyers]

> > How about select max(floor(field)) from table ?
> > 
> > TK
>
> Good idea, but...
> 
> Using floor produces the following error:
> 
> Warning: SQL error: [MERANT][ODBC PROGRESS driver][PROGRESS]Invalid number
> string (7498), SQL state S1000 in SQLExecDirect in c:\program
> files\nusphere\apache\htdocs\mantist\cus\test.php on line 4
> 
> Warning: Supplied argument is not a valid ODBC result resource in 
> c:\program
> files\nusphere\apache\htdocs\mantist\cus\test.php on line 5
> 1
> 

It works on normal MySQL connections, I wonder why the ODBC can't handle it...  One of 
the following queries may work better for you, but I must warn you that finding the 
max integer value of a char field is far from efficient...

SELECT MAX(FLOOR(field)) FROM table WHERE field RLIKE "^[0-9]+$"
SELECT MAX(LPAD(field, 10, "0") FROM table WHERE field RLIKE "^[0-9]+$"

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Database Structures

[Steve Meyers]

> I have a site with a bunch of images/reviews. What i want is for 
> registered 
> users to rate and leave reviews themselves. I'm not sure how I should set 
> up this database though.
> 
> Now, I have a table that holds the user info. Should I create a new table 
> for each image, which holds the user, and his comments/rating? 
> (this seems 
> rather bloated, and inefficient) or should I create a table 
> somehow for all 
> images and keep track of the user comments/ratings another way?

A lot of how you structure your database depends on the expected data that will 
populate it.  How big will the reviews be?  How many reviews per image?  How many 
images?

Creating a new table per image isn't unheard of -- at a company I used to work for, we 
estimated that we had around 25 million tables at one point in time.  There were lots 
of factors that went into why our database was structured like that, but it worked for 
us.

I would guess all you need is a table with the following columns: userid, imageid, 
comment, rating.  The primary key should probably be across (imageid, userid), 
assuming that you're more likely to look them up by image rather than by user.  
Another question, though, is whether you'll be looking at just the rating fairly 
frequently.  If so, you might want to separate the comments and ratings tables, since 
the ratings will probably be most efficient as an int of some sort, and the comments 
will be text fields.  This would keep your most frequently used table having 
fixed-length, very short rows.

If at all possible, I would try out several different structures, and run some test 
scenarios on each of them.

Steve Meyers



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Error codes list

[Steve Meyers]

>From http://www.mysql.com/doc/n/o/node_567.html

For the connection specified by mysql, mysql_errno() returns the error code for the 
most recently invoked API function that can succeed or fail. A return value of zero 
means that no error occurred. Client error message numbers are listed in the MySQL 
`errmsg.h' header file. Server error message numbers are listed in `mysqld_error.h'. 
In the MySQL source distribution you can find a complete list of error messages and 
error numbers in the file `Docs/mysqld_error.txt'. 
 
Steve Meyers


> -Original Message-
> From: Javier Armendáriz [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, October 21, 2001 5:49 AM
> To: DL Neil; [EMAIL PROTECTED]
> Subject: Re: Error codes list
> 
> 
> Thanks a lot, as you know, I need to translate error codes (such as 
> duplicate references) to the future (i hope) program operator. The error 
> codes are for operators (those who write data into the catalog), not for 
> public, I know PHP error reporting functions with MySQL, but i would like 
> to write some functions to analize errors when they happend, break the 
> program execution and tell operators what is happending (in 
> spanish and in 
> a language they could understand).
> 
> What i´m looking for is a complet lits in some way like this :
> 
> Error code numbrer (not DEFCON 5 :-)) > Error descripcion
> 
> 
> Thanks a lot for your interest and help
> 
> Apologize my macarronic english, when i get rich with this progect i´ll 
> travel to great britain in order to learn some good english (and taste 
> english beer and women)
> 
> 
> At 12:02 21/10/01 +0100, DL Neil wrote:
> >Hi
> >
> >Maybe it is in the manual, but i can´t find it in the way i need.
> >Does anybody know where to find a complet list of MySQL error codes with
> >number and descripcion in order to make a library for handling and
> >reporting alerts to users in php
> >
> >
> >Hola Javier,
> >
> >Yes it is in the manual - but try the PHP manual (not MySQL's).
> >The PHP online annotated manual is 'down' at the moment (update 
> >happening?) so I can't give you a reference - apologies.
> >
> >In addition to the familiar PHP-MySQL functions, eg 
> mysql_query($sqlQuery, 
> >$dbLink)
> >There are two more: mysql_errno() and mysql_error() which return 
> an error 
> >number and error text (from the 'latest' MySQL
> >function executed) - or zero and empty-string if the execution 'worked'.
> >
> >It's important to perform error-checking, but some would suggest that it 
> >should not be reported to "users" - under
> >'normal' circumstances!?
> >
> >Regards,
> >=dn
> >
> >
> >
> >-
> >Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> >
> >To request this thread, e-mail <[EMAIL PROTECTED]>
> >To unsubscribe, e-mail 
> ><[EMAIL PROTECTED]>
> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Length limit of 500 on primary keys?

[Steve Meyers]

The problem is that he has it as a primary key, so he wants it to be unique as well as 
indexed.  The best solution (and MUCH MUCH MUCH more efficient) would be to hash each 
of the four columns, and create a primary key on that.  Integer keys are much faster 
and memory-efficient than string keys.
 
Steve Meyers


> -Original Message-
> From: Chris Bolt [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, October 20, 2001 8:31 PM
> To: [EMAIL PROTECTED]
> Subject: RE: Length limit of 500 on primary keys?
> 
> 
> > Is there a way to raise this limit? We have some tables with 
> columns that
> > are VARCHAR(200), and need to make a primary key based on 
> combinations of
> > these columns (in some cases, upto 4 columns), and MySQL 
> complains for all
> > of these table definitions that "Specified key was too long. Max key
> > length is 500".
> 
> Why do you need to index the entire column? You can just index a prefix of
> the column, like the first 125 bytes of each column.
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: UPDATE problem

[Steve Meyers]

The problem is that you're setting all the rows to "%".  The % symbol is only 
recognized as a wildcard in like expressions, as documented in the manual.  Try this 
instead:

UPDATE words_bak1 SET word = LEFT(word, LENGTH(word) - 1) WHERE word LIKE "%s";
 
Steve Meyers


> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, October 20, 2001 4:49 PM
> To: [EMAIL PROTECTED]
> Subject: UPDATE problem
> 
> 
> 
> Hi,
> 
>   I am encountering a problem while I am updating records of a table.
> Basically its a problem of updation of records using pattern matching.
> I want all the recods that end with letter "s" be updated to record
> without the letter "s" at the end. For eg, I want a record "things"
> to be converted to "thing".
> 
>   When I give a query like this I get an error :
> 
>   mysql> UPDATE words_bak1 SET word = "%" WHERE word LIKE "%s";
> ERROR 1062: Duplicate entry '%' for key 1
> 
> my words_bak1 is like this :
> ++--+--+-+-++
> | Field  | Type | Null| Key | Default | Extra |
> ++--+--+-+-++
> | wordid | int(11)  | | PRI | 0   | auto_increment |
> | word   | char(20) | | UNI | |   ||
> | actualword | char(20) | YES | | NULL||
> ++--+--+-+-++
> 
>   Can someone tell me what the problem is and is there some way
> to get around it..
> 
> -Amit
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: how to get the correct result -- Thrid Time --

[Steve Meyers]

That's why I told you to keep on running it until it was all fixed.  Every time there 
will be one less white space, until you're down to just one.
 
Steve Meyers


> -Original Message-
> From: Adrian D'Costa [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 3:44 AM
> To: Steve Meyers
> Cc: Mysql Mailing List
> Subject: RE: how to get the correct result -- Thrid Time --
> 
> 
> On Fri, 19 Oct 2001, Steve Meyers wrote:
> 
> > Well, you still have white space in there.  Try running the 
> following until it's fixed:
> > 
> > UPDATE hotel SET nome_hotel=replace(nome_hotel, '  ', ' ')
> > 
> > That will replace any double spaces to one space.  If there's 
> still a problem, replace all tabs (\t) and carriage returns (\r) 
> with a space, and repeat the above.  And from now on, make sure 
> the data gets put in right :)
> 
> Well that would only get the first two spaces.  I do not know how many
> spaces are there
> 
> Adrian
> 
> 
> >  
> > Steve Meyers
> > 
> > 
> > > -Original Message-
> > > From: Adrian D'Costa [mailto:[EMAIL PROTECTED]]
> > > Sent: Thursday, October 18, 2001 11:22 PM
> > > To: Steve Meyers
> > > Cc: DL Neil; Mysql Mailing List
> > > Subject: RE: how to get the correct result -- Thrid Time --
> > > 
> > > 
> > > On Thu, 18 Oct 2001, Steve Meyers wrote:
> > > 
> > > > Trim won't work because the newline is in the middle of the 
> > > string.  Try using the REPLACE function, ie
> > > > 
> > > > UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '')
> > > 
> > > Ok it replaced it.
> > > 
> > > > 
> > > > For the second question, it actually doesn't matter if it's 
> > > CAPS or not.  The group by will be done in a case insensitive 
> > > manner.  However, if you really need it lowercase, try this:
> > > > 
> > > > SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1
> > > 
> > > The result is
> > > mysql> SELECT id, LOWER(nome_hotel) FROM hotel GROUP BY 2 limit 10;
> > > +--+-+
> > > | id   | LOWER(nome_hotel)   |
> > > +--+-+
> > > | 3825 | |
> > > | 3391 | abi d'oru   |
> > > | 5208 | abou   nawas djerba |
> > > | 3063 | abou nawas djerba   |
> > > | 1252 | adams beach |
> > > | 9757 | aegean village   |
> > > | 7973 | aegean  |
> > > | 8917 | aegean village  |
> > > | 8122 | aegeon  |
> > > | 8284 | aeneas  |
> > > +--+-+
> > > 
> > > Atain 5208 and 3063 are the same so is 9757 and 8917.  I just 
> want only
> > > one of them to appear.
> > > 
> > > Thanks
> > > 
> > > Adrian
> > > 
> > > >  
> > > > Steve Meyers
> > > > 
> > > > 
> > > > > -Original Message-
> > > > > From: DL Neil [mailto:[EMAIL PROTECTED]]
> > > > > Sent: Thursday, October 18, 2001 4:46 AM
> > > > > To: Adrian D'Costa; Mysql Mailing List
> > > > > Subject: Re: how to get the correct result -- Thrid Time --
> > > > > 
> > > > > 
> > > > > You didn't appear to answer last time (grump) - reply 
> repeated below.
> > > > > =dn
> > > > > 
> > > > > - Original Message - 
> > > > > From: "Adrian D'Costa" <[EMAIL PROTECTED]>
> > > > > To: "Mysql Mailing List" <[EMAIL PROTECTED]>
> > > > > Sent: 18 October 2001 05:34
> > > > > Subject: how to get the correct result -- Thrid Time --
> > > > > 
> > > > > 
> > > > > > Hi,
> > > > > > 
> > > > > > I have some records that I need to group by a field.  This 
> > > is easy using
> > > > > > group by in the sql statement.  What I happening is that I 
> > > get some data
> > > > > > in html format that I have written a script that will 
> > > extract the data I
> > > > > > require an dump it into 

RE: XML support

[Steve Meyers]

Since this is an open source product, it could happen a lot sooner if you wrote it :)  
I don't know of any plans to include that, at least in the near future.
 
Steve Meyers


> -Original Message-
> From: can [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 8:14 PM
> To: [EMAIL PROTECTED]
> Subject: XML support
> 
> 
> When will mysql support XML with function same as Oracle XSU?
> 
> Kenneth.
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: foreign key

[Steve Meyers]

InnoDB tables support foreign keys with full referential integrity constraints.  They 
do not yet support cascading deletes and updates.  You'll want to use the MySQL-Max 
version of MySQL to get support for InnoDB tables.
 
Steve Meyers


> -Original Message-
> From: Sandra Rovena Frigeri [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 8:25 PM
> To: [EMAIL PROTECTED]
> Subject: foreign key
> 
> 
> hi,
> 
> which windows mysql version supports foreign key and its 
> "complete" implementation: referential integrity constraint, 
> avoid delete parent key, etc.?
> or
> Which foreign key features that has the best windows mysql version?
> 
> thanks,
> 
> sandra.
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: indexing question

[Steve Meyers]

> So there is no magic bullet that indexes everything so it works well with
> any given query? Does it help to index each field by itself for general
> queries and then I guess you index combinations of fields that will be
> used together in a WHERE clause?
> 

Nope, no magic bullet...  Indexes speed up selects and sometimes updates, but slow 
down inserts, deletes, and sometimes updates (for updates it depends on the query and 
the data, since it can use an index but may also have to update an index).  So don't 
use them where they're not needed.  Most data has only a few ways that it is viewed.  
I probably wouldn't add an index that would only be used once a month :)

> Does ORDER BY use indexes too? Would this need an index by md5 and mime or
> md5, mime, and path?
> 
> SELECT * from files WHERE md5 = 'blah' AND mime = 'blah' ORDER BY path;
> 

In that case, I think the index across all three, in that order, would help (from what 
I know of the how MySQL is supposed to work).  

> Guess I'll hafta work on it. My memory is limited to 32M on this
> development machine but I figure the more the development machine sucks
> then the faster the code should run on the end server if the code runs
> okay on the dev box. :)
> 

Sometimes the biggest difference between dev and live isn't the box it's running on, 
it's the amount and types of concurrent queries.  That can make a huge difference, 
especially on MyISAM tables.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: indexing question

[Steve Meyers]

Okay, then I'll go through it point by point :)


> Thanks. I've read the manual. I guess I was looking for a more direct
> explanation to make sure I had it clear and to learn any tips that might
> be useful that wouldn't be in the manual. As my database will be quite
> large I'm worried about effective optimizations.
> 
> > > Can anyone give me some tips on how indexes work?
> > > 
> > > I noticed that UNIQUE() seems to create a lock on all given fields per
> > > call like UNIQUE (md5, mime) so that no row can have the same 
> combination
> > > of md5 and mime type which is good but assuming I wanted to have each
> > > unique on it's own I'd need UNIQUE (md5), UNIQUE (path) right?
> > > 

Yes, that's correct


> > > Are INDEX's the same as KEY's? When I INDEX() something it 
> seems to dump
> > > as a KEY(). Does it matter if you INDEX ( path, md5, mime ) or
> > > INDEX(path), INDEX(md5), INDEX(mime)? They seem to look 
> different in the
> > > db depending how I do it but both seem to run at about the 
> same speed even
> > > over a large db. Either is a huge speed increase over the 
> original that
> > > had only primary keys it seems.
> > > 

Yes they're the same.  How you index depends on your queries.  Generally, just look at 
what your doing in your where clauses.  If you're looking up rows based just on the 
path, then index path.  If you're looking up rows based on path, md5, and mime at the 
same time, then index across all three.  If you're doing both of the above, then index 
across all three -- any prefix of an index can be used as an index.

> > > Right now in a db with several tables.. some with hundreds of 
> thousands of
> > > rows.. the average query time seems to be about half a second 
> to a second
> > > on a P133. Is that good, bad, or normal?
> > > 

Depends on the query :)  On a well-indexed table, a simple select should report no 
more than a tenth of a second, even on a P133.  Of course, that partly depends on how 
much memory you have...  More memory is always a good investment.



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Problem doing bulk and regular inserts

[Steve Meyers]

You haven't given your table definition, but I'm guessing that your primary key is a 
TINYINT, which only supports -128 to +127.  Figure out how big you need that key to 
be, then change the column as appropriate to a SMALLINT, MEDIUMINT, INT, or BIGINT.
 
Steve Meyers


> -Original Message-
> From: Robert Trembath [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 4:34 PM
> To: [EMAIL PROTECTED]
> Subject: Problem doing bulk and regular inserts
> 
> 
> I have some nice database for a biotech lab running on the windows
> version of mysql 3.23 on a W2K server. For some reason when my ID or
> Auto-increment field get to 127 I can't insert any additional records. I
> get the following error:
> 
> Error 
> SQL-query :  [Edit] 
> 
> insert into curvesdb (Genus, Species, Strain, SampleID, BNID, Primer,
> LaneNo, CurveData, DCD_Name) VALUES('Xanthomonas', ' bromi', '', 'LMG
> 8269', '01-052-0094', 'Uprime-B1', '440-442-46',
> '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
> ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,2,2,1,1,0,0
> ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,2,2,2,2,2,2,2,1,1,1,1,1,1,1,1,1
> ,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10,9,8,6,5,3,1,0,0,0,0
> ,0,0,0,0,0,0,0,0,0,0,0,0,1,2,4,5,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,1,0,0
> ,0,0,0,0,0,0,1,2,2,3,4,5,5,4,2,1,1,1,4,7,13,20,25,31,34,34,33,27,22,15,9
> ,5,1,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,2,4,6,8,9,9,9,7,5,3,1,0,0,0,0,1,1
> ,1,1,0,0,0,0,0,1,5,12,19,28,36,41,46,44,41,34,25,17,9,3,1,0,0,0,0,0,0,1,
> 2,4,6,10,13,17,20,23,25,25,23,22,18,14,10,6,4,2,2,3,4,5,6,6,6,4,3,2,1,1,
> 1,2,2,3,3,3,1,0,0,0,0,1,3,10,18,27,37,43,47,49,45,41,36,31,34,38,47,59,7
> 1,83,93,92,91,80,66,52,36,21,12,4,3,3,4,6,7,7,6,4,3,1,0,0,0,0,0,0,0,0,0,
> 0,1,1,2,3,4,5,6,9,13,19,28,36,46,56,62,68,68,64,58,47,36,24,12,6,1,0,0,0
> ,2,4,4,4,4,4,3,2,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,2,4,8,12,15,19,22,22,
> 22,20,17,14,11,8,6,3,3,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,3,5,7,9,10,10
> ,10,9,8,7,7,6,6,6,6,7,8,10,14,18,29,43,59,81,102,123,144,154,162,159,148
> ,134,110,87,64,41,26,14,5,2,0,1,3,4,6,6,6,6,5,5,5,5,5,5,5,5,5,4,3,2,1,0,
> 0,0,0,0,3,8,15,26,37,47,58,64,67,68,61,53,42,30,20,11,5,3,1,1,2,3,3,3,2,
> 1,1,0,0,0,0,0,0,0,0,1,4,7,9,12,12,13,13,13,13,13,13,15,17,21,25,27,29,29
> ,26,24,19,14,9,5,1,0,0,0,0,0,2,3,4,6,6,7,7,7,6,5,3,2,1,0,0,0,0,0,0,0,1,2
> ,3,3,2,2,2,1,1,1,1,1,3,4,8,12,16,20,24,25,26,25,22,19,14,10,6,2,1,0,0,0,
> 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,2,2,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,
> 0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,2,3,4,4,5,6,7,7,6,5,4,3,2,1,0,0,0,0,
> 0,0,0,0,0,1,1,1,1,1,2,2,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,2,2,3,3,3,4,4,6,
> 9,14,19,26,35,44,53,63,69,74,75,72,68,59,50,39,29,21,15,11,9,8,8,8,9,9,8
> ,7,6,5,3,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
> ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1
> ,1,1,1,1,0,', 'Xanthomonas-B1')
> 
> MySQL said: 
> 
> 
> Duplicate entry '127' for key 1
> 
> I can not insert any more data to this table. This is an important
> issue, please help me to resolve this.
> 
> --
> Robert Trembath
> Internet Technology Manager
> e|[EMAIL PROTECTED] 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Locked Processes Taking MySQL down....

[Steve Meyers]

All of the locked queries are happening because another query has locked them out.  
That is, until a certain query finishes, none of the others can execute.  See the 
manual for more info on table locking.  You can generally fix this simply by fixing 
your bad queries (adding proper indexes, etc), but if this will not work, switching to 
InnoDB may be the most beneficial for you.

http://www.mysql.com/doc/T/a/Table_locking.html
 
Steve Meyers


> -Original Message-
> From: Dan Uyemura [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 3:31 PM
> To: mySQL List
> Subject: Locked Processes Taking MySQL down
> 
> 
> Hello,
> 
> I am seeing rather frequently our mySQL DB Freezes up.  It runs 
> the back end
> of a web site that gets decent traffic.  When I check the process list for
> the DB, there is a bunch of processes all locking that table 
> sitting there.
> I normally disable connections from the Cold Fusion server, 
> meaning I do not
> let the Cold Fusion server leave an open connection to the database.  When
> it is done with it's transaction, it disconnects the connection, 
> so usually
> when I check the process list, there is no more than 5 processes 
> at a time.
> 
> the reason I turned of leaving open connections is because I was seeing a
> problem where idle connections would be left in a sleep state and never
> disconnected... so I would end up using up all of the connections.  once
> this would happen, I would have to restart mySQL to purge off the 
> "zombied"
> threads.  I have a hunch the connections should be allowed to 
> stay, and the
> CF server should be smart enough to continually use the same 
> connection, but
> since that did not seem to be the case, I disabled it.
> 
> Anyhow, does anyone know why processes would stack up, all with a state of
> "locked"?  Is there any countermeasure I can take, or something I am doing
> wrong?
> 
> Dan Uyemura
> IT
> GSociety, Inc.
> 
> 7060 Hollywood Blvd. #800
> Hollywood, CA 90028
> 
> (v) (323) 337-0141
> 
> GSociety family of companies:
> www.gaywired.com
> www.lesbianation.com
> www.qtmagazine.com
> www.dance1.net
> www.ghighway.com
> 
> -Original Message-
> From: Mark Maunder [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 2:01 PM
> To: [EMAIL PROTECTED]
> Subject: MYSQL 4.0 bug with fulltext (case change) updates
> 
> 
> Hi,
> 
> I think this is a bug. The script to recreate the problem is included
> below. This problem appears consistently as long as there's a fulltext
> index and a regular index on the same field and you do an update to
> change the case of a single char. It doesn't matter if the fulltext
> index includes other fields. If the regular index is removed, then after
> the insert (where the case of one char in a field is changed) the table
> is briefly marked as crashed, and then seems to automagically fix itself
> (which is worrying).
> 
> The bug also occurs when you change the case of a single char in a
> varchar field and add a word to the sentence e.g.:
> 'experience with c required' changes to 'experience with C is required'
> also causes the problem.
> 
> Thanks as always,
> 
> ~mark
> ps: If anyone knows of a workaround please let me know. thanks.
> 
> __BEGIN__
> #This works fine on mysql 3 but crashes the table on 4 alpha
> CREATE TABLE tester (
>   id int(11)  auto_increment,
>   title varchar(100)  default '',
>   PRIMARY KEY  (id),
> #If you comment out the following index, then the table is briefly
> marked as crashed
> # but appears to recover.
>   KEY ind5 (title),
> #The fulltext index appears to be the root of this prob.
>   FULLTEXT KEY FT1 (title)
> ) TYPE=MyISAM;
> 
> insert into tester (title) values ('this is a test');
> update tester set title='this is A test' where id=1;
> check table tester;
> __END__
> 
> Gives us:
> Table   Op  Msg_typeMsg_text
> freeusall.testercheck   warning Table is marked as crashed
> freeusall.testercheck   error   Checksum for key: 2 doesn't
> match checksum for records
> freeusall.testercheck   error   Corrupt
> 
> We're running MySQL 4.0 alpha on Linux kernel 2.4.2 intel. Using MyISAM
> tables.
> 
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
&g

RE: indexing question

[Steve Meyers]

See:

http://www.mysql.com/doc/C/R/CREATE_INDEX.html
http://www.mysql.com/doc/M/y/MySQL_indexes.html

Steve Meyers


> -Original Message-
> From: Michael [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 2:07 PM
> To: [EMAIL PROTECTED]
> Subject: indexing question
> 
> 
> Can anyone give me some tips on how indexes work?
> 
> I noticed that UNIQUE() seems to create a lock on all given fields per
> call like UNIQUE (md5, mime) so that no row can have the same combination
> of md5 and mime type which is good but assuming I wanted to have each
> unique on it's own I'd need UNIQUE (md5), UNIQUE (path) right?
> 
> Are INDEX's the same as KEY's? When I INDEX() something it seems to dump
> as a KEY(). Does it matter if you INDEX ( path, md5, mime ) or
> INDEX(path), INDEX(md5), INDEX(mime)? They seem to look different in the
> db depending how I do it but both seem to run at about the same speed even
> over a large db. Either is a huge speed increase over the original that
> had only primary keys it seems.
> 
> Right now in a db with several tables.. some with hundreds of thousands of
> rows.. the average query time seems to be about half a second to a second
> on a P133. Is that good, bad, or normal?
> 
> *^*^*^*
> Michael McGlothlin <[EMAIL PROTECTED]>
> http://mlug.missouri.edu/~mogmios/projects/
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: join tables on UPDATE

[Steve Meyers]

That will be in some release of either 4.0 or 4.1, I'm not sure which.
 
Steve Meyers


> -Original Message-
> From: JohnHomer [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 2:11 AM
> To: [EMAIL PROTECTED]
> Subject: join tables on UPDATE
> 
> 
> hi list,
> 
> can mysql allow table joins when using UPDATE query? like
> 
> update tranfer left join transferdetails
> on transfer.docno = transferdetails.docno
> set transferdetails.docno = concat('SJ',transferdetails.DocNo)
> where
> transferdetails.docno not regexp '^SJ' and
> lower(xfrom) = 'san juan';
> 
> 
> i get an error 
> 
> 
> ERROR 1064 at line 1: You have an error in your SQL syntax near 
> 'left join trans ferdetails on transfer.docno = 
> transferdetails.docno set transfer' at line 1
> 
> im using mysql 3.23.41 on win32
> 
> 
> TIA
> 
> johnhomer
> 
> 
> 
> __
> www.edsamail.com
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: group by clause

[Steve Meyers]

It was my understanding that it should actually work that way, but SQL does not 
guarantee that with GROUP BY clauses -- you would want to use an ORDER BY clause if 
you want to guarantee order.  The problem could be in one of your char fields  -- 
maybe an extra space somewhere that's messing up the ordering?
 
Steve Meyers


> -Original Message-
> From: Sommai Fongnamthip [mailto:[EMAIL PROTECTED]]
> Sent: Friday, October 19, 2001 3:19 AM
> To: [EMAIL PROTECTED]
> Subject: group by clause
> 
> 
> Hi,
>   did someone tell me why group by clause not work?  I'd like 
> to group 
> volume of order from my customer at the same price and my supplier.  here 
> is my code
> 
>   Select custid, symbol, sum(volume) as sumvol, price, mktid, 
> supplier, 
> supplierflag
>   From confirm
>   WHERE mktid=123 or mktid=456
>   Group by custid, symbol, supplier, supplierflag, price
>   I got this:
>   
>   row#custid   symbolsumvol   price   mktid  
> supplier  supplierflag
> (char)   (char)(int)(double) 
> (char)  (char)(char)
>   1   1234 XXX  100  12 123  
> 1C
>   2   1234 XXX  150  12 123  
> 2C
>   3   1234 XXX  200  13 123  
> 1E
> Why row 3 not follow data in row 1 instead of row 2?  How could I 
> correct my SQL statement?
> 
> SF 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: how to get the correct result -- Thrid Time --

[Steve Meyers]

Well, you still have white space in there.  Try running the following until it's fixed:

UPDATE hotel SET nome_hotel=replace(nome_hotel, '  ', ' ')

That will replace any double spaces to one space.  If there's still a problem, replace 
all tabs (\t) and carriage returns (\r) with a space, and repeat the above.  And from 
now on, make sure the data gets put in right :)
 
Steve Meyers


> -Original Message-
> From: Adrian D'Costa [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2001 11:22 PM
> To: Steve Meyers
> Cc: DL Neil; Mysql Mailing List
> Subject: RE: how to get the correct result -- Thrid Time --
> 
> 
> On Thu, 18 Oct 2001, Steve Meyers wrote:
> 
> > Trim won't work because the newline is in the middle of the 
> string.  Try using the REPLACE function, ie
> > 
> > UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '')
> 
> Ok it replaced it.
> 
> > 
> > For the second question, it actually doesn't matter if it's 
> CAPS or not.  The group by will be done in a case insensitive 
> manner.  However, if you really need it lowercase, try this:
> > 
> > SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1
> 
> The result is
> mysql> SELECT id, LOWER(nome_hotel) FROM hotel GROUP BY 2 limit 10;
> +--+-+
> | id   | LOWER(nome_hotel)   |
> +--+-+
> | 3825 | |
> | 3391 | abi d'oru   |
> | 5208 | abou   nawas djerba |
> | 3063 | abou nawas djerba   |
> | 1252 | adams beach |
> | 9757 | aegean village   |
> | 7973 | aegean  |
> | 8917 | aegean village  |
> | 8122 | aegeon  |
> | 8284 | aeneas  |
> +--+-+
> 
> Atain 5208 and 3063 are the same so is 9757 and 8917.  I just want only
> one of them to appear.
> 
> Thanks
> 
> Adrian
> 
> >  
> > Steve Meyers
> > 
> > 
> > > -Original Message-
> > > From: DL Neil [mailto:[EMAIL PROTECTED]]
> > > Sent: Thursday, October 18, 2001 4:46 AM
> > > To: Adrian D'Costa; Mysql Mailing List
> > > Subject: Re: how to get the correct result -- Thrid Time --
> > > 
> > > 
> > > You didn't appear to answer last time (grump) - reply repeated below.
> > > =dn
> > > 
> > > - Original Message - 
> > > From: "Adrian D'Costa" <[EMAIL PROTECTED]>
> > > To: "Mysql Mailing List" <[EMAIL PROTECTED]>
> > > Sent: 18 October 2001 05:34
> > > Subject: how to get the correct result -- Thrid Time --
> > > 
> > > 
> > > > Hi,
> > > > 
> > > > I have some records that I need to group by a field.  This 
> is easy using
> > > > group by in the sql statement.  What I happening is that I 
> get some data
> > > > in html format that I have written a script that will 
> extract the data I
> > > > require an dump it into a table.  Everything working fine.  The 
> > > problem is
> > > > that when the data in entered into the table some fields 
> enter with the
> > > > new line (\n).  So when I use the group by command below is 
> the result.
> > > > 
> > > > mysql> select nome_hotel from hotel group by nome_hotel limit 10;
> > > > | Abou
> > > > Nawas Djerba |
> > > > | Abou Nawas Djerba|
> > > > | Adams Beach  |
> > > > | Aegean
> > > > Village   |
> > > > | Aegean   |
> > > > | Aegean Village   |
> > > > 
> > > > This what I don't want since "Abou
> > > > Nawas Djerba" and "Abou Nawas 
> Djerba" are the
> > > > same.  How do I get rid of the space.  I tried trim, rtrim 
> > > nothing works.
> > > > 
> > > > Second, using the same data I get some of the hotel names 
> in CAPS and I
> > > > need to convert it to lower and then group by.  How do I 
> get these two
> > > > done?
> > > > 
> > > > Thanks
> > > > 
> > > > Adrian
> > > > 
> > > > 
> > > > 
> > > > 
> > > > 
> -
> > > > Before posting, please check:
> > > >http://www.mysql.com/manual.p

RE: MySQL 4.0 - Order By & Limit

[Steve Meyers]

Could you also give a sample of the results you're getting that are incorrect?  You 
don't have to give every field, just the title should do.
 
Steve Meyers


> -Original Message-
> From: Ashwin Kutty [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2001 11:31 AM
> Cc: [EMAIL PROTECTED]
> Subject: RE: MySQL 4.0 - Order By & Limit
> 
> 
> 
> Point well taken.. The query is as follows:-
> 
> SELECT * FROM dddeli WHERE title LIKE "%$keyword%" ORDER BY title 
> desc LIMIT $start,$end;
> 
> Where $keyword is the keyword to search by from the field title, $start
> and $end are to dictate which page of the results, etc..
> 
> title is varchar(128)
> 
> and dddeli has 33 fields..
> 
> On Thu, 18 Oct 2001, Steve Meyers wrote:
> 
> > It would help if you posted the actual query and results you 
> are getting, instead of showing "simulated" results.  You mention 
> the query below in your first post, but you never give the actual 
> results of it.
> >  
> > Steve Meyers
> > 
> > 
> > > -Original Message-
> > > From: Ashwin Kutty [mailto:[EMAIL PROTECTED]]
> > > Sent: Thursday, October 18, 2001 5:58 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: Re: MySQL 4.0 - Order By & Limit
> > > 
> > > 
> > > 
> > > I just saw the mistake I made in my original post.. I want to Order By
> > > letter, but when I do that with the Limit clause the results are as
> > > shown in the original post.. Anyone know what the problem could be?
> > > 
> > > 
> > > Steve Meyers wrote:
> > > 
> > > >That is actually how it is supposed to work -- MySQL gets all 
> > > the rows, sorts them, and then returns up to the limit.  What you 
> > > suggest would make no sense in any application, as far as I can 
> > > tell.  The problem with the SQL queries given in the original 
> > > post is the the ordering is being done by #, not letter.  Of 
> > > course it will be fairly haphazard.  If you want to sort by 
> > > number, then letter, you will need to specify it in your query as 
> > > such (ORDER BY #, letter).
> > > > 
> > > >Steve Meyers
> > > >
> > > >
> > > >>-Original Message-
> > > >>From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]]
> > > >>Sent: Tuesday, October 16, 2001 1:31 PM
> > > >>To: [EMAIL PROTECTED]
> > > >>Cc: [EMAIL PROTECTED]
> > > >>Subject: Re: MySQL 4.0 - Order By & Limit
> > > >>
> > > >>
> > > >>Hi,
> > > >>
> > > >>I don't see the interest of this kind of behaviour : the 
> main benefit of
> > > >>LIMIT is MySQL stops fetching rows as soon as the number of 
> > > rows specified
> > > >>in LIMIT is achieved.
> > > >>The behaviour you describe requires that MySQL fetches all the 
> > > rows, sorts
> > > >>it, and then applies LIMIT. (so no speed optimization !).
> > > >>For your example, just use the SQL query without LIMIT, and 
> > > then get only
> > > >>the rows that interest you :) (I assume you are using 
> language like PHP,
> > > >>C++, etc... to execute this query ?)
> > > >>
> > > >>Regards,
> > > >>
> > > >>Jocelyn Fournier
> > > >>Presence-PC
> > > >>
> > > >>- Original Message -
> > > >>From: "Ashwin Kutty" <[EMAIL PROTECTED]>
> > > >>Cc: <[EMAIL PROTECTED]>
> > > >>Sent: Tuesday, October 16, 2001 8:48 PM
> > > >>Subject: MySQL 4.0 - Order By & Limit
> > > >>
> > > >>
> > > >>>Hi,
> > > >>>
> > > >>>I thought version 4.0 was supposed to do a Order By first 
> and then a
> > > >>>Limit after that on the results returned on a SELECT.. I have just
> > > >>>installed the new version and I hit the same problem I was hitting
> > > >>>before, i.e., the Limit seems to be executing first and 
> then the Order
> > > >>>By causing all the results to be haphazard when they 
> display.. Now if I
> > > >>>go for a wider set of results, i.e. increase the Limit, the 
> > > results come
> > > >>>a lot better then, i.e. more of them are ordered right, but 
> > > then, 

RE: Problem listing enum vars

[Steve Meyers]

Since it's a LIKE, you need to put the table name in quotes:

SHOW COLUMNS FROM test LIKE 'Var'

This also allows you to use things like 'Var%' in you query.
 
Steve Meyers


> -Original Message-
> From: TD - Sales International Holland B.V. [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2001 9:29 AM
> To: [EMAIL PROTECTED]
> Subject: Problem listing enum vars
> 
> 
> Hey there,
> 
> first off all, please CC me any replies, as I'm no longer a member of the 
> list... Sorry about that but I just receive too much... Over 2000 
> emails a 
> day is no exception and I can no longer keep track of them.
> 
> Ok, for the problem, I want to list the possible values of a enum 
> variable.
> 
> In the manual it says:
> 
> If you want to get all possible values for an ENUM column, you 
> should use: 
> SHOW COLUMNS FROM table_name LIKE enum_column_name and parse the ENUM 
> definition in the second column.
> 
> I have a database called RMA with a table called test with a enum 
> in there 
> called Var.
> 
> Here comes a paste from my mysql client.
> 
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 8 to server version: 3.23.40
>  
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>  
> mysql> use RMA;
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>  
> Database changed
> mysql> describe test;
> +---+-
> --+--+-+-+---+
> | Field | Type
> | Null | Key | Default | Extra |
> +---+-
> --+--+-+-+---+
> | Var   | enum('Omgeruild - RMA Stock','Omgeruild - DOA','Credit 
> Nota','Defect Gevonden','Out Of Warranty') | YES  | | NULL
> |   |
> +---+-
> --+--+-+-+---+
> 1 row in set (0.00 sec)
>  
> mysql> SHOW COLUMNS FROM test LIKE Var;
> ERROR 1064: You have an error in your SQL syntax near 'Var' at line 1
> 
> This manual is from 3.23.39 and I'm using 3.23.40, that shouldn't 
> be an issue 
> right? So what am I doing wrong here?? :-(
> I need it for PHP, so it can create a html dropdown box with 
> those values
> 
> Kind regards,
> 
> Ferry van Steen
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL 4.0 - Order By & Limit

[Steve Meyers]

It would help if you posted the actual query and results you are getting, instead of 
showing "simulated" results.  You mention the query below in your first post, but you 
never give the actual results of it.
 
Steve Meyers


> -Original Message-
> From: Ashwin Kutty [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2001 5:58 AM
> To: [EMAIL PROTECTED]
> Subject: Re: MySQL 4.0 - Order By & Limit
> 
> 
> 
> I just saw the mistake I made in my original post.. I want to Order By
> letter, but when I do that with the Limit clause the results are as
> shown in the original post.. Anyone know what the problem could be?
> 
> 
> Steve Meyers wrote:
> 
> >That is actually how it is supposed to work -- MySQL gets all 
> the rows, sorts them, and then returns up to the limit.  What you 
> suggest would make no sense in any application, as far as I can 
> tell.  The problem with the SQL queries given in the original 
> post is the the ordering is being done by #, not letter.  Of 
> course it will be fairly haphazard.  If you want to sort by 
> number, then letter, you will need to specify it in your query as 
> such (ORDER BY #, letter).
> > 
> >Steve Meyers
> >
> >
> >>-Original Message-
> >>From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]]
> >>Sent: Tuesday, October 16, 2001 1:31 PM
> >>To: [EMAIL PROTECTED]
> >>Cc: [EMAIL PROTECTED]
> >>Subject: Re: MySQL 4.0 - Order By & Limit
> >>
> >>
> >>Hi,
> >>
> >>I don't see the interest of this kind of behaviour : the main benefit of
> >>LIMIT is MySQL stops fetching rows as soon as the number of 
> rows specified
> >>in LIMIT is achieved.
> >>The behaviour you describe requires that MySQL fetches all the 
> rows, sorts
> >>it, and then applies LIMIT. (so no speed optimization !).
> >>For your example, just use the SQL query without LIMIT, and 
> then get only
> >>the rows that interest you :) (I assume you are using language like PHP,
> >>C++, etc... to execute this query ?)
> >>
> >>Regards,
> >>
> >>Jocelyn Fournier
> >>Presence-PC
> >>
> >>- Original Message -
> >>From: "Ashwin Kutty" <[EMAIL PROTECTED]>
> >>Cc: <[EMAIL PROTECTED]>
> >>Sent: Tuesday, October 16, 2001 8:48 PM
> >>Subject: MySQL 4.0 - Order By & Limit
> >>
> >>
> >>>Hi,
> >>>
> >>>I thought version 4.0 was supposed to do a Order By first and then a
> >>>Limit after that on the results returned on a SELECT.. I have just
> >>>installed the new version and I hit the same problem I was hitting
> >>>before, i.e., the Limit seems to be executing first and then the Order
> >>>By causing all the results to be haphazard when they display.. Now if I
> >>>go for a wider set of results, i.e. increase the Limit, the 
> results come
> >>>a lot better then, i.e. more of them are ordered right, but 
> then, again,
> >>>it still skips a lot more.. As an example:-
> >>>
> >>>Results Set
> >>># letter
> >>>1 f
> >>>1 a
> >>>2 b
> >>>1 c
> >>>3 d
> >>>1 e
> >>>1 h
> >>>1 g
> >>>
> >>>
> >>>Limit 2
> >>>
> >>>Order By #
> >>>
> >>>1 a
> >>>1 f
> >>>
> >>>Then when I go to the next set, I get
> >>>
> >>>1 c
> >>>1 e
> >>>
> >>>Instead I would like it to be as the following:-
> >>>First Set:-
> >>>1 a
> >>>1 c
> >>>Second Set:-
> >>>1 e
> >>>1 f
> >>>
> >>>etc..
> >>>
> >>>Any ideas?
> >>>
> >>>These are a few specs of the server & stuff..
> >>>Linux kernel 2.4.7-2 on a Redhat 8 server running Mysql 4.0.0-alpha
> >>>The query I have is as follows:-
> >>>SELECT * from tablename WHERE fieldname LIKE "%keyword%" ORDER BY
> >>>fieldname DESC LIMIT 0,10;
> >>>
> >>>Thanks..
> >>>
> >>>
> >>>-
> >>>Before posting, please check:
> >>>   http://www.mysql.com/manual.php   (the manual)
> >>>   http://lists.mysql.com/   (the list archive)
> >>>
> >>>To request this thread, e-mail <[EMAIL PROTECTED]>
> >>>To unsubscribe, e-mail
> >>>
> >><[EMAIL PROTECTED]>
> >>
> >>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >>>
> >>>
> >>
> >>-
> >>Before posting, please check:
> >>   http://www.mysql.com/manual.php   (the manual)
> >>   http://lists.mysql.com/   (the list archive)
> >>
> >>To request this thread, e-mail <[EMAIL PROTECTED]>
> >>To unsubscribe, e-mail 
> >><[EMAIL PROTECTED]>
> >>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >>
> >>
> >
> >
> 
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: how to get the correct result -- Thrid Time --

[Steve Meyers]

Trim won't work because the newline is in the middle of the string.  Try using the 
REPLACE function, ie

UPDATE hotel SET nome_hotel=replace(nome_hotel, '\n', '')

For the second question, it actually doesn't matter if it's CAPS or not.  The group by 
will be done in a case insensitive manner.  However, if you really need it lowercase, 
try this:

SELECT LOWER(nome_hotel) FROM hotel GROUP BY 1
 
Steve Meyers


> -Original Message-
> From: DL Neil [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2001 4:46 AM
> To: Adrian D'Costa; Mysql Mailing List
> Subject: Re: how to get the correct result -- Thrid Time --
> 
> 
> You didn't appear to answer last time (grump) - reply repeated below.
> =dn
> 
> - Original Message - 
> From: "Adrian D'Costa" <[EMAIL PROTECTED]>
> To: "Mysql Mailing List" <[EMAIL PROTECTED]>
> Sent: 18 October 2001 05:34
> Subject: how to get the correct result -- Thrid Time --
> 
> 
> > Hi,
> > 
> > I have some records that I need to group by a field.  This is easy using
> > group by in the sql statement.  What I happening is that I get some data
> > in html format that I have written a script that will extract the data I
> > require an dump it into a table.  Everything working fine.  The 
> problem is
> > that when the data in entered into the table some fields enter with the
> > new line (\n).  So when I use the group by command below is the result.
> > 
> > mysql> select nome_hotel from hotel group by nome_hotel limit 10;
> > | Abou
> > Nawas Djerba |
> > | Abou Nawas Djerba|
> > | Adams Beach  |
> > | Aegean
> > Village   |
> > | Aegean   |
> > | Aegean Village   |
> > 
> > This what I don't want since "Abou
> > Nawas Djerba" and "Abou Nawas Djerba" are the
> > same.  How do I get rid of the space.  I tried trim, rtrim 
> nothing works.
> > 
> > Second, using the same data I get some of the hotel names in CAPS and I
> > need to convert it to lower and then group by.  How do I get these two
> > done?
> > 
> > Thanks
> > 
> > Adrian
> > 
> > 
> > 
> > 
> > -
> > Before posting, please check:
> >http://www.mysql.com/manual.php   (the manual)
> >http://lists.mysql.com/   (the list archive)
> > 
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> > 
> > 
> > I have some records that I need to group by a field.  This is easy using
> ...
> > that when the data in entered into the table some fields enter with the
> > new line (\n).  So when I use the group by command below is the result.
> ...
> > Second, using the same data I get some of the hotel names in CAPS and I
> > need to convert it to lower and then group by.  How do I get these two
> > done?
> 
> Adrian,
> Can you do these (both) at the time the data is first entered into the db?
> In other words adjust your business rules at the front end, 
> instead of attempting something that's
> very difficult at the back?
> =dn
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Frequently corrupt tables

[Steve Meyers]

Well, for one, I believe that Slashdot uses InnoDB tables, which tend to handle a 
little better under very high load.
 
Steve Meyers


> -Original Message-
> From: Matthew Bloch [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2001 3:34 AM
> To: [EMAIL PROTECTED]
> Cc: Peter Taphouse; Alec O'Donnell
> Subject: Frequently corrupt tables
> 
> 
> Hello all;
> 
> I'm running several MySQL installation (all version 3.23.37 under Linux)
> under what I presume are some fairly harsh conditions, and wondered what
> circumstances cause tables to be corrupted and need fixing with myisamchk.
> This is happening once every few days and it's becoming a pain.  I have a
> multithreaded process which is constantly opening and closing connections
> to the database and trying to increase its concurrency until the load
> average reaches something comfortable like 15, and the network connection
> is saturated.  I've had to throttle it back to stop it opening more than
> 32 simultaenous DB connections but otherwise it works fine.  Until I start
> getting errors from the table handler, that is, and the whole thing grinds
> to a halt until I fix the table manually.
> 
> Can anybody shed some light on this?  I can't believe I'm putting it under
> more load than something like Slashdot would, and they don't (appear to)
> have half the troubles I've had.
> 
> cheers,
> 
> -- 
> Matthew   > http://www.soup-kitchen.net/
>   > ICQ 19482073
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail 
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> 
> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php