Hi,
- 10 products in both cases. One time the column is a MediumInt, the
other time a BigInt. I know there is a difference in disk space usage, but
is there also one in performance at all ?
I'm not sure, this apply to your case. I had set a unique index on a
char(50) and it was 2x slower than
Sign the list
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Jerry Swanson wrote:
[...]
I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where
Is it true that I can only store 65535 bytes (2^16-1) in a field of type
BLOB? I thought that a BLOB was supposed to be able to hold 2^16 bytes
of data.
If it is indeed only possible to store 2^16-1 bytes I would like to know
why this has been implemented in that way (yes, I am always this
Hello,
I'm using 4.1.8-standard on linux and get this:
CREATE TABLE test (field1 CHAR(20) BINARY NOT NULL, field2 CHAR(20)
NOT NULL) TYPE=MyISAM;
INSERT INTO test VALUES('xxx','yyy')
SELECT * FROM test WHERE field1=field2
ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT)
and
On Thursday 13 January 2005 12:42, Mads Kristensen might have typed:
If it is indeed only possible to store 2^16-1 bytes I would like to know
why this has been implemented in that way (yes, I am always this curious
;-)
http://dev.mysql.com/doc/mysql/en/String_type_overview.html
--
MySQL
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from
Ok. I am replying to my own post here ;-)
I guess that the reason is the fact that only 2 bytes are reserved for
storing the size of the BLOB, and since an unsigned short's maximum
value is 2^16-1 this is also the maximum size of the BLOB.
So, mystery solved :-)
- Mads
Mads Kristensen wrote:
Is
This is what I was looking for. Why the query is call cross-tab?
TH
On Thu, 13 Jan 2005 02:09:45 +, Ian Sales [EMAIL PROTECTED] wrote:
Jerry Swanson wrote:
| Field| Type
| Null | Key | Default | Extra |
Mads Kristensen [EMAIL PROTECTED] wrote on 13/01/2005 12:42:13:
Is it true that I can only store 65535 bytes (2^16-1) in a field of type
BLOB? I thought that a BLOB was supposed to be able to hold 2^16 bytes
of data.
If it is indeed only possible to store 2^16-1 bytes I would like to
What query suppose to be faster? Jan or Dave?
Jan Solution:
SELECT
date,
SUM(IF(CONCAT(status,id) LIKE 'received%',1,0)) as received,
SUM(IF(CONCAT(status,id) LIKE 'send%',1,0)) as send,
SUM(IF(CONCAT(status,id) LIKE 'cancelled%',1,0)) as cancelled
FROM
account
GROUP BY date;
On Wed, 12 Jan
[snip]
This is what I was looking for. Why the query is call cross-tab?
[/snip]
It is called a crosstab because of the way the reults resemble a results
table. Here is an article that shows the crosstab in larger use
http://www.evolt.org/article/Using_MySQL_and_PHP_to_Present_Excel_Spread
[snip]
What query suppose to be faster? Jan or Dave?
[/snip]
Queries using equalities are generally faster than LIKE. YMMV.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm a MySQL newbie, though I've worked with SQL Server and other RDBMS for a
long time. That said, I'm *guessing* that the stored proc solution would be
the fastest, since its execution plan is precomputed, but a quick look seems
to say that stored procs are only available in v5, still in alpha.
A varchar will take up less disk space than a char. A char is padded to
fill it's length, so a index on char will be much larger than a
varchar, depending on content.
Numbers work differently. An index on a number column should be faster
than the same sized char or varchar column. First a
I would think that the Jan solution should execute in a single pass
through the table. The Dave (subquery) version will probably need to make
3 passes through the table get the same results.
As was mentioned in another response, the inequality comparisons
(=,,,etc.) are much faster than a
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to exclude those congressional districts in TX, PA and ME. My coding
that brings up data for all congressional districts is shown below.
What is the difference between '='' and ''?
TH
On Thu, 13 Jan 2005 09:13:07 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I would think that the Jan solution should execute in a single pass through
the table. The Dave (subquery) version will probably need to make 3 passes
through the
[snip]
What is the difference between '='' and ''?
[/snip]
= EQUAL TO
NOT EQUAL TO
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
We are pleased to announce the release of the DBManager Professional 3.0.2.
This version is a major bug fix with a small number of new features:
Fixes
a.. Fixed Load Dump which affected most of the engines supported
b.. Closing Workspace with other Views opened will close all views
c..
[snip]
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to exclude those congressional districts in TX, PA and ME. My
coding
that brings up data for all congressional districts is shown below.
Something like:
select
fh1109.state,
fh1109.cd,
fh1109.party,
fh1109.representative,
ssa1202.total,
((total-children*percentunder18)/vapall)*100,
ssa1202.retired_workers,
ssa1202.disabled_workers,
ssa1202.widow,
ssa1202.wives_and_husbands,
ssa1202.children
from ssa1202, fh1109,
Hi,
I guess you juat should add on a AND fh1109.state NOT IN
('TX','PA','ME')
Also I think your AND conditions are a bit overdetermining, you can
probably do just :
WHERE fh1109.state = ssa1202.state and
fh1109.cd = ssa1202.cd and
fh1109.state = vapall.state and
fh1109.cd = vapall.cd and
AND
[EMAIL PROTECTED] wrote:
I would think that the Jan solution should execute in a single pass
through the table. The Dave (subquery) version will probably need to make 3 passes through the table get the same results.
- er, it's Ian, not Jan :-)
As was mentioned in another response, the
With conditions in the WHERE clause. Add something like
AND fh1109.state NOT IN ('TX', 'PA', 'ME')
to yours.
Michael
[EMAIL PROTECTED] wrote:
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want
Jay Blanchard wrote:
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3.
[snip]
What is the purpose of DISTINCT here?
[/snip]
It's a hold over from other RDBMSs that I have worked with in the past.
The syntax is still valid and I pretty much automatically type it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
hope this isn't considered too off-topic but...
I have been working on standardizing the directory trees on our MySQL
servers (a la OFA for those who speak Oracle) but when I repoint the
socket parameter in my.cnf all my perl scripts barf w/:
Can't connect to local MySQL server through socket
Earlier today we tried upgrading one of our MySQL servers from 4.0.15 to
4.1.8 using RPMs. This resulted in corrupted InnoDB tables seemingly
randomly selected across the different databases (about 40 databases with
similar schemas plus an addition 5 with different tables).
The error message
On 13 Jan 2005 at 10:47, Sid Lane wrote:
hope this isn't considered too off-topic but...
I have been working on standardizing the directory trees on our MySQL
servers (a la OFA for those who speak Oracle) but when I repoint the
socket parameter in my.cnf all my perl scripts barf w/:
Ian Sales wrote:
- I always concatenate the wanted criteria (status, in this case) with a
unique ID field in order to be absolutely certain I'm counting every
instance of each value of status. I've not investigated to see whether I
*really* need to do this, but it feels safer...
- ian
Then
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from
Hi, Can anyone help please, I am trying to install 4.1 onto this machine as
a windows service but installation will not proceed and I get the message:-
Resource messagedlg-confirmation not found
So:-
I unchecked the 'install as windows service' and tried to start it
standalone from a command
If you don't have any data in innodb, delete and recreate the
tablespace including the frm files. This will give you a fresh 4.1
table space to import into.
-Eric
On Thu, 13 Jan 2005 16:37:39 +0100, Mattias J [EMAIL PROTECTED] wrote:
Earlier today we tried upgrading one of our MySQL servers
Whoops - every c/c++ program must have a main function.
That's what the cryptic message was...
Andy
On Wed, 2005-01-12 at 17:35 +, Andy Ford wrote:
Hi everyone.
I have been putting together a c++ wrapper for the mysql c libraries and
have stumbled across the following error...
g++
There are mostly just 2 or 3 corrupted tables out of about 100 in each
database/schema/namespace and if possible we would like to keep the
information in the other tables.
At 2005-01-13 17:47, you wrote:
If you don't have any data in innodb, delete and recreate the
tablespace including the frm
OK, just for yucks, I tried two versions of this in the SQL Server Query
Analyzer. The group by version had 1/3 the cost of the subquery version. The
last_name field had a clustered index.
I then did the same thing in MySQL, and lacking any other cost measurement,
looked at execution time. Before
If I make a connection as follows:
$getcnnctd = @mysql_connect('this', 'that', 'bigsecret');
then select my db, then
$res = mysql_query(select * from thedb);
when finished using the db etc., am I assuming correctly that I should:
mysql_free_result($res); to free the memory up
and
Hello,
I would like to create a sequence object in mysql that I could use in multiple
tables through a application I am developing.
Could someone please send my the syntax of how to create a simple sequence that
starts at 1 increments by 1.
thanks,
[snip]
I would like to create a sequence object in mysql that I could use in
multiple tables through a application I am developing.
Could someone please send my the syntax of how to create a simple
sequence that starts at 1 increments by 1.
[/snip]
Are you talking about auto-increment?
You want AUTO_INCREMENT,
http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html.
PB
-
Scott Purcell wrote:
Hello,
I would like to create a sequence object in mysql that I could use in multiple
tables through a application I am developing.
Could someone please send my the syntax of
On Thu, 13 Jan 2005 11:21:31 -0600, Scott Purcell wrote:
I would like to create a sequence object in mysql that I could use in
multiple tables through a application I am developing.
Sequences are currently not supported in MySQL.
Jochem
--
MySQL General Mailing List
For list archives:
How do I exclude some rows in a table? I am merging columns from three
tables all of which show all congressional districts in all states. I
want to exclude those congressional districts in TX, PA and ME. My coding
that brings up data for all congressional districts is shown below.
Is there an equivalent to Oracle's INSERT ... RETURNING? Note that
there is a related discussion in the archives about getting the
auto-incremented id, but that doesn't cover the more general case of
wanting to retrieve a value that has been altered by a trigger into a
variable.
thanks in
I understand the auto-increment capability, but how would one share it amount
three different tables?
Would one have to do a rig, and create an extra table with increment in it,
then get that value to update table 1, then repeat process again to get new
increment and put it into table 2, etc.?
Your first fix failure (command prompt start) sounds like you started the
CLIENT not the SERVER.
CLIENT = mysql.exe
SERVER = mysql-nt.exe or mysqld-nt.exe or... (there are several versions
that come with each distribution)
Your second fix failure matches with your installation error. There is
Scott Purcell [EMAIL PROTECTED] wrote on 01/13/2005 01:05:01 PM:
I understand the auto-increment capability, but how would one share
it amount three different tables?
Would one have to do a rig, and create an extra table with increment
in it, then get that value to update table 1, then
You obviously do not understand auto-increment capability. You wouldn't
get that value of the sequence, because you would always retrieve the
same value. You would have to add a row to the 'extra' table for the
auto-increment field to work as designed, then use the highest value of
I have these 2 queries.
SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that
SELECT count(*) events
FROM events e
WHERE e.this and e.the other thing
is there a way to put these into one query.
SELECT count(g.*) gifts, count(e.*)
FROM gift g, event e
WHERE . . . .
so far nothing seems
Can subquery return more than one column in Mysql 4.1.7?
SELECT (select user.last_name, user.first_name from actor where
user.id=6) as last_name, first_name
(select count(*) from account where account.status = 'progress') as
progress_count,
ERROR 1241 (21000): Operand should contain 1
How about ...
select @a:=count(*) from ... where ...
union
select @b:=count(*) from ... where ...
union
select @[EMAIL PROTECTED];
PB
---
2wsxdr5 wrote:
I have these 2 queries.
SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that
SELECT count(*) events
FROM events e
WHERE e.this and e.the
Mattias,
have you observed these:
http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
Do you have table names containing accent characters like ?
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for
Hello.
I unchecked the 'install as windows service' and tried to start it
standalone from a command prompt, but I got ERROR 2003 hY000: CAN'T
CONNECT TO SERVER ON 'LOCALHOST' 10061
What program have you started? It looks like it was mysql client program.
Start mysqld instead. Have you
Hello.
Use CAST(). See:
http://dev.mysql.com/doc/mysql/en/Charset-CAST.html
Jens Schreiber [EMAIL PROTECTED] wrote:
Hello,
I'm using 4.1.8-standard on linux and get this:
CREATE TABLE test (field1 CHAR(20) BINARY NOT NULL, field2 CHAR(20)
NOT NULL) TYPE=MyISAM;
Hello.
Changing the basedir variable doesn't automatically changes the datadir
variable.
This can produce the problems. I've reported a bug:
http://bugs.mysql.com/bug.php?id=7873
You can use my suggested patch or specify the options in the option file. See:
There may be other ways to get at the information you want. What is the
purpose of your query?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
2wsxdr5 [EMAIL PROTECTED] wrote on 01/13/2005 01:57:31 PM:
I have these 2 queries.
SELECT count(*) gifts
FROM gift g
Can anyone tell me a good way to import individual column data into a
table? Is there a tool to assist with this?
Thanks,
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NaviCat
Thanks
Donny Lairson
President
29 GunMuse Lane
P.O. box 166
Lakewood NM 88254
http://www.gunmuse.com
469 228 2183
-Original Message-
From: Steve Grosz [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 13, 2005 2:56 PM
To: mysql@lists.mysql.com
Subject: Import Excel data into
I think you mean that you want to import .txt or .csv data into an mysql
table...
http://phpmyadmin.net can do that via a web form to upload plus has many other
good
admin features.
-Original Message-
From: Steve Grosz [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 13, 2005 1:56
Hi,
Does anyone now the maximum number of records and fields in MySQL 4.0.22 Win 32?
Regards,
--
Saiful Akbar
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[snip]
Does anyone now the maximum number of records and fields in MySQL 4.0.22
Win 32?
[/snip]
You keep putting records in the system until you run out of disk space.
I have one database now that is
20 table(s) 335,411,461 Records 149.6 GB
--
MySQL General Mailing List
For list
Steve Grosz [EMAIL PROTECTED] writes:
Can anyone tell me a good way to import individual column data into a
table? Is there a tool to assist with this?
If your data is a .csv file [a.csv] similar to:
1,Steve,Grosz
2,Brad,Eacker
Consistent with the output from Excel. You could use
LOAD DATA
I forgot one element
LOAD DATA LOCAL INFILE 'a.csv' INTO TABLE Users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
(id,firstName,lastName);
Brad ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Thanks for your suggestions, everyone, but I have followed the manual and
repeated the installation process (with it's very nice GUI, by the way) more
than once, and I still have a problem.
Perhaps it isn't that simple after all.
I had similar problem on this laptop when I installed version 4,
Under 4.0.18 we were loading about 800M of data into a memory table to
get better performance from some of our main queries.
Via crontab we would DELETE older links in the memory table and then
INSERT links from a myisam table.
This process under 4.1.7 is MUCH slower. Specifically the DELETE
Raphael Matthias Krug wrote:
Sasha
P.S. I have a theory that a habit of printing computer documentation
is a road block to becoming a guru. At least, I have not yet
encountered a guru that printed much, while at the same time it
seems like a struggling user prints a lot. You cannot be 100% sure
Hmm that's a range, that should do a table scan in 4.0.18, since a
memory table type is just a hash table. In 4.1 I believe it supports
ranges since the table is more of a myISAM type.
Is there an index on TIMESTAMP?
Does the range cover more then 30% of the table?
-Original Message-
Dathan Pattishall wrote:
Hmm that's a range, that should do a table scan in 4.0.18,
Yes... I believe it did but since its a memory table it went by really
quick.
since a
memory table type is just a hash table. In 4.1 I believe it supports
ranges since the table is more of a myISAM type.
I am importing data into a large table, 100 million rows, and I want to use
Alter table disable keys prior to executing a Load Data...,. But after
loading the data, if I execute a Alter Table ... enable keys will MySQL
create a second table, load the data into the second table, and then
Hi,
MySQL 4.1.9, a new version of the popular Open Source/Free Software
Database Management System has been released. It is now available in source
and binary form for a number of platforms from our download pages at
http://dev.mysql.com/downloads/ and mirror sites.
Note that not all mirror
DISABLE / ENABLE keys is very fast even for your dataset. Basically when
disabled it only respects UNIQUE type keys when loading data and
rebalances the binary tree when enabled.
Dathan
-Original Message-
From: mos [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 13, 2005 5:24 PM
At 07:42 PM 1/13/2005, Dathan Pattishall wrote:
DISABLE / ENABLE keys is very fast even for your dataset. Basically when
disabled it only respects UNIQUE type keys when loading data and
rebalances the binary tree when enabled.
Dathan
Dathan,
So you're saying it will not create a temporary
Hi Guys
I am trying to display 1 random record taken from 2 tables.
I have tried the following;
SELECT table1.record1, table1.record2, table2.record1 FROM table1, table2
ORDER BY RAND() Limit 1
With no luck... So now I am trying to use CREATE VIEW but also with no
luck... Can anybody help out?
HI,
the following insert/update produced error. I m using MySQL 5.0.
$insert_sql = qq {insert into inventory
(prodcode,qty,lastupdatedate,prodname,basename,vendorname,cost)
values (?,?,?,?,?,?,?) ON DUPLICATE KEY
UPDATE
74 matches
Mail list logo
