What about:
"ALTER TABLE
ORDER BY ;"
HTH, Cor
- Original Message -
From: "Rob Wultsch" <[EMAIL PROTECTED]>
To: "afan pasalic" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, May 15, 2008 8:23 PM
Subject: Re: reorder records in database
On Thu, May 15, 2008 at 11:38 AM, afan pasalic <[EMA
- Original Message -
From: "Rob Wultsch" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, April 29, 2008 2:44 PM
Subject: Re: why is explain.key-len not size of key field ?
On Tue, Apr 29, 2008 at 7:39 AM, C.R.V
- Original Message -
From: "Rob Wultsch" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, April 29, 2008 2:24 PM
Subject: Re: why is explain.key-len not size of key field ?
On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin &l
Hi Charles,
to get max . # characters:
SELECT MAX(CHAR_LENGTH()) AS `MaxChars`;
to get max. # bytes:
SELECT MAX(LENGTH()) AS `MaxBytes`;
HTH, Cor
- Original Message -
From: "Charles Lambach" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, April 29, 2008 1:21 PM
Subject: How to know the maxim
Hi All,
I have a dictionary table like:
CREATE TABLE IF NOT EXISTS `mydictionary`
(
`EN` varchar(36) default NULL,
`DE` varchar(36) default NULL,
`ES` varchar(36) default NULL,
`FR` varchar(36) default NULL,
`IT` varchar(36) default NULL,
`NL` varchar(36) default NULL,
`PT` varchar
ubject: Re: data truncation warnings by special characters
I have some php code I use to import data that is a bit more flexible and
robust than the load data statement in MySQL If you use php I can share
the code with you.
C.R.Vegelin wrote:
Hi Jerry,
Sorry, I should have mentioned that I
t; <[EMAIL PROTECTED]>
To: "'C.R.Vegelin'" <[EMAIL PROTECTED]>;
Sent: Friday, April 18, 2008 2:30 PM
Subject: RE: data truncation warnings by special characters
>-Original Message-
From: C.R.Vegelin [mailto:[EMAIL PROTECTED]
Sent: Friday, April 18, 2
Hi List,
I get strange "Data truncated for column Description" warnings
when loading a tab separated file with special characters.
The definition of the target table is:
CREATE TEMPORARY TABLE tmp
( Code CHAR(8) NOT NULL,
Description TEXT NOT NULL,
KEY Code (Code)
) ENGINE = MyISAM CHARSET=
Hi Waynn,
Try:
SET @row := 0;
SELECT @row := @row + 1 AS Rank,
UserId, count(*) as NumActions from Actions group
by UserId order by NumActions desc limit 10;
HTH, Cor
- Original Message -
From: "Waynn Lue" <[EMAIL PROTECTED]>
To:
Sent: Friday, February 22, 2008 10:38 AM
Subject
- Original Message -
From: "mos" <[EMAIL PROTECTED]>
To:
Sent: Monday, February 18, 2008 6:48 PM
Subject: Re: Best way to combine MYISAM to MERGE tables ...
At 11:33 AM 2/18/2008, you wrote:
Hi All,
I am working with MYISAM tables split by year, like:
data2003, data2004, data2005,
Hi All,
I am working with MYISAM tables split by year, like:
data2003, data2004, data2005, data2006, data2007, data2008, all having the same
definitions.
To speed up the query process, I also defined MERGE tables, like:
CREATE TABLE data20032004 ...
ENGINE=MERGE UNION (data2003, data2004)
Sebastian,
MySQL uses only one index for searching.
As far as I know this applies to all MySQL versions, right ?
Regards, Cor
- Original Message -
From: "Sebastian Mendel" <[EMAIL PROTECTED]>
To: "Michael Stearne" <[EMAIL PROTECTED]>; "MySQL List"
Sent: Thursday, January 10, 2008 6:
I get "Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'UNION'.
in a query like (SELECT ...) UNION (SELECT * INTO OUTFILE ...) to build a CSV
file.
The database has default charset UTF8 and collation utf8_general_ci
and all query tables have d
Hi,
Try the format below:
ALTER DATABASE mybase DEFAULT CHARACTER SET = latin1;
ALTER DATABASE mybase DEFAULT COLLATE = latin1_swedish_ci;
HTH ...
- Original Message -
From: "Mário Gamito" <[EMAIL PROTECTED]>
To: "MySQL List"
Sent: Monday, April 09, 2007 4:19 PM
Subject: charset
Thanks Christophe,
Question rephrased:
how do you explain 0.05 q/s = 20 s/q
with an immediate response ?
Cor
- Original Message -
From: "Christophe Gregoir" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, April 06, 20
Hi List,
Using printf(" System status: %s\n", mysqli_stat($link));
in a PHP script, says: "Queries per second avg: 0.051".
This means that a query takes about 20 seconds ?
But the query result is given immediately.
How should I interpret "Queries per second avg" ?
I'm using version 5.0.15 NT.
Reg
Have a look at the HAVING clause ...
- Original Message -
From: "js " <[EMAIL PROTECTED]>
To:
Sent: Monday, February 12, 2007 2:48 PM
Subject: NOT EMPTY, like NOT NULL
Hi list,
A silly question.
Is it possible to prevent empty value('') from appearing in a field?
I can solve this
stripped
down version of this works for me (tried without the left join)
On 2/6/07, Lars Schwarz <[EMAIL PROTECTED]> wrote:
i suppose this to be working when you leave the group by?
On 2/6/07, C.R.Vegelin <[EMAIL PROTECTED]> wrote:
> I want to calc a running sum with @variables
I want to calc a running sum with @variables.
Using the command line client, I enter:
SET @row := 0, @runsum := 0;
followed by:
SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country`
, ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1`
, @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum`
FRO
an idea of my chart needs:
http://www.ecb.int/stats/exchange/eurofxref/html/eurofxref-graph-usd.en.html
Regards, Cor
- Original Message -
From: "El Cuy Volador" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>;
Sent: Saturday, January 13, 200
Dear List,
I have a MySQL database (V5.0.x) and I need to make graphs.
Does anyone know about good utilities to make graphs ?
I would appreciate your expertise or links.
TIA, Cor
to get the query runtime.
But a format like 0.05 sec is also right for me.
Maybe any other ideas ?
- Original Message -
From: "Chris White" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, January 05, 2007 3:5
Hi List,
I need the lapse time in microseconds.
I have tried various things, like:
SELECT TIME_FORMAT(CURTIME(), '%f');
SELECT TIME_FORMAT(NOW(), '%f');
SELECT MICROSECOND(CURTIME());
but all I get is 0.
What am I doing wrong ?
TIA, Cor
Thanks Visolve, Peter,
This is a serious matter, because:
- within a row: 1 + NULL = NULL
- across rows with SUM(): 1 + NULL = 1
I know the manual says that group functions ignore NULL values (12.10.1),
but it also says: Conceptually, NULL means "a missing unknown value" (3.3.4.6).
IMHO a NULL w
Hi List,
I need to SUM() on months from a table like:
CREATE TABLE `data`
( `Jan` float default NULL,
...
) ENGINE=MyISAM; # V 5.0.15
Months may have NULL values, like:
INSERT INTO data (Jan) VALUES (1), (NULL);
However, when I use SELECT SUM(Jan) AS Jan,
the returned value is 1 in stead of NULL
I have some base tables, called data2004, data2005 etc.
They all have the following structure:
CREATE TABLE IF NOT EXISTS `data200X`
( F1 int unsigned NOT NULL default '0',
F2 smallint unsigned NOT NULL default '0',
F3 smallint unsigned NOT NULL default '0',
F4 tinyint unsigned NOT NULL defau
nd "olive oils",
and excludes products with "fatty oils ... (excl. olive)".
Unfortunately, it is slower than MATCH ... AGAINST ...,
probably because it's not using the fulltext key on the Description field.
Thanks, Cor
- Original Message -
From: "Brigitte Sili
;[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>;
Sent: Thursday, October 12, 2006 11:05 AM
Subject: Re: boolean search on phrase*
Hi,
Try with + and * fulltext boolean operators.
For instance,
MATCH (Description) AGAINST('+olive oil*' IN BOOLEAN MO
' IN BOOLEAN MODE)
giving "olive oil" and "olive oils" BUT also "fatty oils ... (excl. olive)"
c) ... MATCH (Description) AGAINST("olive oil" IN BOOLEAN MODE)
giving the same results as b)
Any more ideas ?
TIA, Cor
- Original Message -
F
Hi All,
I want a boolean search on a phrase.
For example on "olive oil", but it should return also "olive oils" etc.
Now I use the following:
SELECT Description FROM products
WHERE MATCH (Description ) AGAINST('"olive oil"' IN BOOLEAN MODE);
This works fine, but it does NOT return rows with "olive
Hi List,
I have a table with a FULLTEXT KEY column,
and I would like to get a list of all the FULLTEXT KEY words, eg:
acetic
acid
acidified
acrylic
...
Any idea how to make such a list ?
TIA, Cor
Hi Mike,
Try the following:
ALTER TABLE tblname DISABLE KEYS;
LOAD DATA INFILE ...
ALTER TABLE tblname ENABLE KEYS;
hth, Cor
- Original Message -
From: "mos" <[EMAIL PROTECTED]>
To:
Sent: Saturday, July 29, 2006 4:50 AM
Subject: Re: Way too slow Load Data Infile
I ran a file monit
Hi List,
I have the 2 MyISAM tables using mySQL version 5.0.15-NT:
Table countries:
`ID` smallint unsigned NOT NULL default '0',
`Code` char(2) default NULL,
`Name` char(30) default NULL, ...
PRIMARY KEY (`ID`)
Table data
`Country1` smallint unsigned NOT NULL default '0',
`Country2` smallint uns
Hi CPK,
I suppose you've looked at the manual in:
http://dev.mysql.com/doc/refman/5.0/en/myodbc-examples.html
It you still have problems, try just the linked table, without forms.
If you can view the linked MySQL tables in MS Access,
then the problem has to do with the forms, and not with the con
AIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, June 30, 2006 4:36 PM
Subject: Re: number of rows in EXPLAIN for unpacked vs packed tables
Cor -
Those numbers are an estimate, not a hard number, of how many rows
MySQL thinks it will have to exmaine
Hi All,
I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK.
Both tables (MyISAM + Compressed ) have exactly the same number of rows with
Count(*).
But when I give a SELECT query with EXPLAIN on both tables, I get different
number of rows.
For example: EXPLAIN SELECT
Hi Karl,
Your question: can I add a $ when you select a view.
I suggest to include $ sign in the field alias, like:
Select title_id, ytd_sales * price AS `Turnover $` From titles;
HTH, Cor
- Original Message -
From: "Karl Larsen" <[EMAIL PROTECTED]>
To: "Chris W" <[EMAIL PROTECTED]>
Hi Eugene,
I suppose you have read:
http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html
Especially the paragraph starting with:
The order of indexes in the MERGE table and its underlying tables should be
the same.
HTH, Cor
- Original Message -
From: "Eugene Kosov" <[EMAI
Hi Gabriel,
Can you tell the benefits of a composite index,
compared to 4 individual indices in this case ?
Suppose I need to select on the fields b, c or d.
Then I also need also indices on fields b, c and d.
Together with the composite index on (a,b,c,d),
there is a lot of redundancy in the ind
quot;.
The reason for my question was to avoid using MS Access.
I will certainly take a closer look at your suggestions !
Regards, Cor
- Original Message -
From: "Rhino" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>;
Sent: Tuesday, May 09, 2
Hi List,
I want to convert strings to proper-case,
where only the 1st char of each word is uppercase,
such as: "This Is An Example."
Any idea how to do this with MySQL 5.0.15 ?
Thanks, Cor
Hi Manuel,
I had a similar problem a few months ago and I found 'somewhere' in the
documentation
a post by Matthew van Os on May 27 2005, saying:
The CursorLocation should be of the type "adUseClient" instead of
"adUseServer".
The adUseServer returns a -1 as recordcount.
The adUseClient retur
]>
To: "Shawn Green" <[EMAIL PROTECTED]>; "C.R.Vegelin"
<[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, April 25, 2006 2:17 PM
Subject: Re: Top N selections + rest row
Hi Shawn, Cor, all!
Shawn Green wrote:
--- "C.R.Vegelin" <[EMAIL PROTECTED]> wro
Thanks Shawn,
According: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html:
When you use ROLLUP, you cannot also use an ORDER BY clause to sort the
results. ...
Regards, Cor
- Original Message -
From: "Shawn Green" <[EMAIL PROTECTED]>
To: "C.R.Vegel
Thanks Martijn, Barry,
I was wondering whether it could be done in a single query.
I want users to decide how many countries they want,
and show world sales on top of report followed by the N countries.
This to enable relative country shares, both for reporting and graphs.
For example, Top-10 coun
Hi All,
Anybody with smart ideas to get Top-N rows plus the rest row ?
Suppose I have ranked sales values descending for 2005, like:
Select Country, Sum(Sales) AS Sales From myTable
Where Year=2005 Group By Country
Order By Sales DESC LIMIT 25;
Then I get Top-25 sales rows, but I also want a res
Hi everyone,
I use many scripts that are started from the CLI (MySQL 5.0.15).
And if errors occur, I want to quit a script. I looked at:
http://dev.mysql.com/doc/refman/5.0/en/show-errors.html
and found Show Count(*) Errors; and for Select @@error_count;
But how to quit a CLI script, if @@error_co
SELECT ... INTO OUTFILE ?
Cor,
You can set the enclosing character with the ENCLOSED BY parameter.
So something like ENCLOSED BY '' will remove those quotes.
- Jorrit
C.R.Vegelin wrote:
Thanks Paul,
Yes, I've tried IFNULL() to map NULL values to empty strings.
But then I get "
o extend the FIELDS options to enable this ...
Regards, Cor
- Original Message -
From: "Paul DuBois" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>;
Sent: Saturday, April 01, 2006 6:11 PM
Subject: Re: how to suppress NULL values in SELECT ... IN
Hi everyone,
I am struggling to make a CSV file, with rows like: 1;2;;4;;2;9
where NULL values are suppressed in the CSV file.
I tried the following alternatives:
a) Select ... Into Outfile ... Fields Terminated By ';' Escaped By ''
Lines Terminated By '\r\n' ...
but this generates output
Thanks Adrian, Dilipkumar, Dhandapani,
I changed my.ini file, restarted the server and now it's okay.
Regards, Cor
- Original Message -
From: "Adrian Bruce" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, March 30
Hi List,
I would appreciate your help on the following.
When using LOAD DATA INFILE 'inputfile.txt' into a MyISAM table,
it creates mysql-bin.nn files under my database directory
with the size of 'inputfile.txt' (about 200 MB).
Since I have to load 12 inputfiles, I get about 2.5 GB of mysql-b
Hi Ariel,
Maybe this example helps you to create CSV output from MySQL.
The first SELECT generates the headerline; the second the data.
( SELECT 'FieldA','FieldB','FieldC', ... )
UNION
( SELECT `FieldA`, `FieldB`, `FieldC`, ...
INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv'
FIELDS TERMINATED BY '
r) AS sq
ON (db.myKey=sq.myKey AND db.Year=sq.Year)
SET db.Jan = sq.Jan, ..., db.Dec = sq.Dec;
This works fine and fast. Thanks again !
Regards, Cor
- Original Message -
From: Peter Brawley
To: C.R.Vegelin
Cc: mysql@lists.mysql.com
Sent: Tuesday, February 28, 2006 8:
Hi List,
Please help me with the following problem in MySQL 5.0.15.
I have 2 MyISAM tables like:
- table Updates with fields myKey, Year, Month, Value
where Month has the values 1 .. 12
- table Data with fields myKey, Year, Jan, Feb, ... Dec
I need to put all available monthly Values from U
Hello All,
More than once I read on this list that problems may occur,
because of unequal types and/or sizes of join fields.
Suppose a Countries table with primary key ID SmallInt(5),
and a Accounts table with CountryID SmallInt(4).
Does this have any negative affect, eg. on performance ?
By the w
Hi Grant,
I suggest to change both key fields to Integers.
Numeric keys take less space and are faster.
Your Product_Feature table then may have:
- product_id INT unsigned
- feature INT unsigned
having a Primary Key of 8 bytes i.s.o. 141 bytes.
Thus a smaller index, less disk reads and more speed
Hi James,
I have found similar - slowdown - effects for queries.
However, it is not always clear what causes the lack of speed.
For example, I have a table with more than 9 million rows,
including a non-unique indexed item myKey (tinyint).
The query "Select myKey, count(*) from myTable Group By my
Hi James,
I saw your email about "are primary keys always essential ?".
And that your app is essentially creating summary reports
from large amounts of summary data.
May I ask what reporting tool you use for summary data ?
TIA, Cor Vegelin
--
MySQL General Mailing List
For list archives: http
last sort_time.
I am afraid I have to simulate an IsSorted property myself.
Regards, Cor Vegelin
- Original Message -
From: "James Harvard" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc:
Sent: Monday, December 19, 2005 10:48 AM
Subjec
Thanks Harrison !!!
SELECT char(7); works excellent to simulate a BEEP
with MySQL version 5.0.15 under Windows XP Pro.
It is a simple but effective feature for running scripts.
Regards, Cor
- Original Message -
From: "Harrison Fisk" <[EMAIL PROTECTED]>
To: "
export
your
results to a CSV file. Then open the CSV file in Excel (using the Excel
File
> Open). See ODBC connection above for another option using the HTML
flag.
HTH,
J.R.
-Original Message-
From: C.R.Vegelin [mailto:[EMAIL PROTECTED]
Sent: Saturday, December 10, 2005 9:35 AM
To: m
Hi everybody,
I defined a field Date_End with default NULL, but am missing it.
CREATE TABLE Regions
( Country CHAR(4) NOT NULL,
Date_Start CHAR(4) NOT NULL,
Date_End CHAR(4) default NULL, # this one ...
Description CHAR(50)
) Engine = MyISAM;
Regions table is filled with a tab-delimited in
Hi everybody,
I looked for a function to test whether a string is numeric (having characters
0..9 only).
I found in the 5.0 manual Cast() and Convert(), but I don't think these do what
I need.
Any suggestion what function to use ?
Thanks in advance, Cor
Hi Friends,
I have a MyISAM table with about 10 million rows.
When I drop the (multi-column) Primary Key (PK) it takes more than 10 minutes.
I thought that any key or index was a kind of table in itself.
And if so, dropping the PK could be as fast as a Truncate statement (a second).
Am I missing so
(Updates.Month = 3,Updates.Cell,Data.Mar), etc.
And it works much faster than 12 separate queries.
Regards, Cor Vegelin
- Original Message -
From: "Roger Baklund" <[EMAIL PROTECTED]>
To:
Cc: "C.R.Vegelin" <[EMAIL PROTECTED]>
Sent: Thursday, November 17, 20
Hi All,
I have a "simple" problem and hope you can help me.
I have an input table Updates with various fields, incl. Month and MonthlyValue.
The field Updates.Month ranges from 1 to 12.
I also have a target Data table with 12 fields, called Jan, Feb, Mar, ... `Dec`.
Depending on Updates.Month the M
67 matches
Mail list logo
