Hi Michael,
- Original Message -
From: Michael Stassen
Sent: Wednesday, June 23, 2004 2:30 PM
Subject: Re: INDEX DESC
Jeremy Zawodny wrote:
Why is sorting required at all? Indexes *are* sorted already.
I expect he's referring to mysql's poor performance when doing ORDER BY
Hi Gerald,
- Original Message -
From: gerald_clark
Sent: Wednesday, June 23, 2004 2:28 PM
Subject: Re: INDEX DESC
I suspect he is refering to 3.23's inability to use an index on a ORDER
BY xxx DESC
That's not always true. 3.23 WILL use the index for ORDER BY ... DESC in a
query like
Hi Pieter,
That's because may is a stopword in MySQL's full-text indexing, by
default (like can, the, etc). You can define your own stopword file
with the ft_stopword_file variable. And you can find the default, built-in
list of stopwords in the file myisam/ft_static.c of the source
Hi Andrea,
The ' isn't NOT a stopword, it's simply not a word-boundary character,
which I think is what you want. And there is no way I know of to change
that in MySQL... unless you edit the source of course and compile it
yourself. :-) Is that an option for you? If so, I think you just need
Hi Scott,
No, TEXT and BLOB are the same except for the case-sensitivity differences.
Neither is like VARCHAR (except the with/without BINARY attribute part) in
that TEXT/BLOB columns don't lose trailing spaces when inserted, as VARCHAR
will -- just to clear that up.
That article is wrong, at
Hi Ronan,
Yes, it's fine to mix table types in databases and queries.
Matt
- Original Message -
From: Ronan Lucio
Sent: Thursday, June 03, 2004 2:44 PM
Subject: Table types
Hi,
Is it wise to have a database with hybrid table types?
In other words: if I have a table that
Hi Dan,
- Original Message -
From: Dan Nelson
Sent: Thursday, June 03, 2004 12:34 PM
Subject: Re: Tuning MySQL 4.0.20 for large full table scans
[snip]
Not sure what can be done about making it not go straight to tmpdir
with a BLOB column in the SELECT clause, though. Probably
Hi David,
Great questions:
- Original Message -
From: David Griffiths
Sent: Friday, May 28, 2004 6:05 PM
Subject: Specifying an index length and the default value
The length of indexes on varchar and char indexes can be specified at
index creation.
What is the default length of an
Hi Peter,
- Original Message -
From: Peter Thomas
Sent: Saturday, May 01, 2004 11:24 PM
Subject: Delayed insert record visibility
I'm trying to understand the delayed insert process to see whether I
can use
it to reduce the load on mysql, and have the following question.
Are
Hi John,
- Original Message -
From: John Mistler
Sent: Sunday, May 02, 2004 12:50 AM
Subject: Indexing
I know this is an elementary question, but I am getting two sets of
instructions from different MySQL manuals about setting an index on a
prefix
of a column of a table. One says to
Hi John,
What version do you use? In 4.0.18, they fixed some bugs that were
introduced in 4.0.17 related to trailing spaces on indexed TEXT-family
columns: http://dev.mysql.com/doc/mysql/en/News-4.0.18.html
I see 3 Bugs fixed entries with trailing spaces in them. If you're
not using 4.0.17,
Hi John,
I *think* VARCHAR is *supposed* to work that way, but doesn't in MySQL.
So you'll have to use TINYTEXT. Its storage requirements are the same
as VARCHAR(255) and it behaves the same way, except for, I think, 3
things: 1) the trailing space thing, obviously; 2) it can't have a
DEFAULT
Hi Joyce,
needs is a stopword, that's why it's not indexed or found. You can
use your own ft_stopword_file to define the list without needs. The
default, built-in stopword list is defined in, I think, the
myisam/ft_static.c file of the source distribution, for reference.
Hope that helps.
Ben,
- Original Message -
From: Ben Dinnerville
Sent: Monday, April 19, 2004 1:49 AM
Subject: RE: Slow Query Question - Need help of Gurus.
snip
Then try again:
SELECT `Call Svc Tag ID`,
Count(*) as counter,
`Journal Create Date`
FROM 31909_859552
WHERE
Hi Dan,
(Sending to the General list too, since this isn't a Windows-specific
thing.)
SHOW TABLE STATUS LIKE 'tbl_name'
will show you the current Avg_row_length.
But the specific values for MAX_ROWS and AVG_ROW_LENGTH when you CREATE
or ALTER the table don't matter (except for looking correct
Hi Boyd,
Can I ask why it really matters? :-) I would assume the DEFAULT value
is stored at creation time; but the end result would be the same either
way.
BTW, I hate how MySQL's SHOW CREATE TABLE quotes DEFAULT INT-family
values. :-( It shouldn't do that.
Matt
- Original Message
Hi sascha,
How's the space on your datadir partition (or wherever this table is)?
I believe MySQL creates the temp tables during ALTER in the DB
directory, not the tmpdir.
If the space there is OK, have you checked the error log for anything
related?
Matt
- Original Message -
From:
Hi Steve,
You might want to look at FLUSH TABLES WITH READ LOCK. That's a query
to run from mysql, but I'm sure you can get it to work in your shell
script (you need to maintain the MySQL connection while doing the
backup). I don't know much about that, though. I think you just run
UNLOCK
Hi,
This is what HAVING is for. :-)
Matt
- Original Message -
From: Joe Rhett
Sent: Monday, April 05, 2004 8:59 PM
Subject: Re: Why can't I use an AS value in the WHERE clause.
On Mon, Apr 05, 2004 at 08:03:33PM -0500, Paul DuBois wrote:
At 17:29 -0700 4/5/04, Daevid Vincent
Hi,
GROUP_CONCAT() is in 4.1. :-)
Matt
- Original Message -
From: m.pheasant
Sent: Wednesday, March 31, 2004 5:26 PM
Subject: RE: Is this possible?
You would need an aggregate concat() function I think its in 5.0
m
-Original Message-
From: Chris Boget [mailto:[EMAIL
Hi,
MySQL 4+ can use indexes for ORDER BY ... DESC (3.23 can only in some
cases) in every case that ASC can. However, reading a packed index in
reverse order is slower. I don't think your index is packed, though, if
it's a date-type column, unless you've specified PACK_KEYS in your
CREATE
Hi Joshua,
First thing I'd try is upgrading to 4.1.1! And/or 4.1.2 when it's
released in a couple weeks.
Matt
- Original Message -
From: Joshua Thomas
Sent: Monday, March 29, 2004 10:51 AM
Subject: mysqld keeps crashing
Hello all,
I'm running mysql 4.1.0-alpha-log on FreeBSD
Hi Jim,
Unfortunately you do have to use the CONCAT() function to make sure it
works on all MySQL installations. The operator used in other DBs, and
which can be used in MySQL when running in ANSI mode, is ||, not +:
SELECT firstname || ' ' || lastname AS fullname
FROM customers
But if MySQL
Hi Laphan,
(I'm sending this to the general list too, since this isn't Windows
specific and more people will see it.)
MySQL 5.0, which is an early Alpha, does now support stored procedures.
http://www.mysql.com/doc/en/Stored_Procedures.html
And MySQL also supports transactions with the InnoDB
Hi Michael,
- Original Message -
From: Michael Stassen
Sent: Tuesday, March 16, 2004 9:45 AM
Subject: Re: BETWEEN
Matt W wrote:
The query using 2 BETWEENs with OR is exactly how it should be. It
will
be fast even in MySQL 3.23. OR is not a problem when the OR parts
involve
Hi Michael, Jochem,
- Original Message -
From: Michael Stassen
Sent: Tuesday, March 16, 2004 10:00 AM
Subject: Re: BETWEEN
Jochem van Dieten wrote:
However, I expect that would result in doing 2 rangescans and a
merge. It might be even faster to use:
SELECT * FROM sys
WHERE
Hi Michael,
- Original Message -
From: Michael Stassen
Sent: Saturday, March 13, 2004 10:48 AM
Subject: Re: BETWEEN
Keith wrote:
g'day,
i'm looking for a way to do two BETWEEN ranges. Currently I have
sys.sectorID BETWEEN 1 AND 20 but I want it so that I can search
between
Hi Anthony,
You don't need REGEXP for this; LIKE will do. Try something like this:
... WHERE CONCAT(',', Column, ',') LIKE '%,2,%'
to search for rows that contain 2.
Hope that helps.
Matt
- Original Message -
From: award
Sent: Saturday, March 13, 2004 2:16 PM
Subject: query
for the help
But the problem in the column it can take various form
Just as
1
1,2
12
1,22,4
sometimes I have the comma and sometimes I do not have them.
So if do WHERE column LIKE %2%
would it work??
thank you
anthony
-Original Message-
From: Matt W [mailto:[EMAIL PROTECTED]
Sent
Hi Patrick,
No, you can't get per database statistics in MySQL. :-(
Matt
- Original Message -
From: Patrick Gelin
Sent: Wednesday, March 10, 2004 1:45 AM
Subject: 3 000 000 requests for last 14 days...
Hi,
I've got very astonished to see with phpMyAdmin my MySQL database has
Hi Tom,
You can't. MySQL's own thread ids are sequential. The OS pids are
random. There's no connection between them. Besides, mysqld is really
only running in a single real process, it's just that LinuxThreads
shows each thread as a process.
Matt
- Original Message -
From: Tom
Hi Rhino,
- Original Message -
From: Benoit St-Jean
Sent: Saturday, March 06, 2004 9:00 AM
Subject: Re: Join Definitions
Rhino wrote:
Can anyone point me to documentation describing the concepts behind
MySQL's different join types?
[snip]
http://www.mysql.com/doc/en/JOIN.html
Hi Dave,
- Original Message -
From: [EMAIL PROTECTED]
Sent: Thursday, March 04, 2004 7:43 AM
Subject: Corrupt full text index
Description:
When updating a table with a fulltext index, the fulltext index
becomes corrupted. The Error ERROR 1034 at line 76: Incorrect key file
for table:
Hi Paul,
ALTER TABLE table_name ADD id_column_name INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY FIRST;
The FIRST word at the end just makes it the first column in the table if
that's what you want.
Hope that helps.
Matt
- Original Message -
From: Paul Maine
Sent: Thursday,
Hi Don,
No, full-text search was added in MySQL 3.23.23, I believe (4.0.1 just
added boolean searches along with more speed overall). It doesn't need
to be compiled in or anything, it's there by default. Unless someone
compiled it and actually *removed* the full-text code or something. :-)
Hi Mike,
- Original Message -
From: Mike Mapsnac
Sent: Monday, February 23, 2004 5:49 PM
Subject: run query second time
Hello
Today I run large query. It took more than 1 minute to start printing
the
results. The output was about 5 rows. However, when I run the
query
second
Hi Mark,
- Original Message -
From: Mark Maunder
Sent: Monday, February 23, 2004 4:17 PM
Subject: Re: HEAP tables vs MYISAM on ramdisk
411 is packed with features I'm dying to have on my production server,
but I had it on my dev box, and I got some table corruption which,
admittedly,
Hi,
- Original Message -
From: [EMAIL PROTECTED]
Sent: Sunday, February 22, 2004 3:18 PM
Subject: Re: Encryption Issue
According to documentation there is a query log wich logs
established
connections and executed queries, also there is the binary log wich
stores all statements that
Chris,
The good news is that MySQL 5.0 can finally use multiple indexes per
table. I just noticed this page in the manual a few days ago:
http://www.mysql.com/doc/en/OR_optimizations.html
:-)
Matt
- Original Message -
From: Chris Nolan
Sent: Monday, February 16, 2004 7:13 AM
Hi Eve,
That error is because the LOCAL part of LOAD DATA is disabled. See
here: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html
Since your file is probably on the same system as the MySQL server, it
should work if you remove the LOCAL word.
Hope that helps.
Matt
- Original Message
Hi all,
Can anyone tell me whether or not some kind of RAID will improve the seek/access times
during lots of random reads from, say, MyISAM data files? I *do not care* about
improved [sequential] transfer rates; I want the fastest possible random access.
I'm thinking that RAID won't give an
-
From: [EMAIL PROTECTED]
Sent: Friday, February 20, 2004 7:24 PM
Subject: RE: Improving seek/access times -- does RAID help?
Run everything off a ramdisk ;-)
Ted Gifford
-Original Message-
From: Matt W
Sent: Friday, February 20, 2004 5:21 PM
Subject: Improving seek/access times
Hi James,
Your key_buffer is using tons of memory at 1.5 GB! table_cache is
probably too big, too.
Matt
- Original Message -
From: James Kelty
Sent: Saturday, February 14, 2004 3:03 AM
Subject: Massive memory utiliazation
Hello,
We have currently tuned MySQL for a high rate of
Hi,
Yeah, by default mysqldump buffers the result of the SELECT * FROM
table query in memory before writing the SQL statements (using
mysql_store_result()). If you use the --opt option (or at least -q
or --quick), it dumps the data as it gets it (using mysql_use_result()).
Hope that helps.
Hi,
You're probably right. All the status variables seem to start over
after hitting 4,294,967,295. :-( I don't get why they're only using 32
bit integers for the variables that they know can go WAY over that
amount. :-/
Matt
- Original Message -
From: Mikhail Entaltsev
Sent:
Hi Sergei!
Great news. Thanks very much! :-)
Matt
- Original Message -
From: Sergei Golubchik
Sent: Tuesday, February 03, 2004 1:54 PM
Subject: Re: query the data of a fulltext index directly from index?
Hi!
On Feb 02, Matt W wrote:
Sergei,
Any chance of getting a ft_dump
Hi,
- Original Message -
From: [EMAIL PROTECTED]
Sent: Wednesday, February 04, 2004 12:12 PM
Subject: Server Behavior.
Seeking opinions on this.
Server is Dual Pentium Xeon 2.8, 6 GB RAM, running RedHat Linux 7.2,
MySQL 4.0.17, all installed and tested with no problems.
I had a
Hi David,
ALTER TABLE table ADD ListingID MEDIUMINT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY FIRST;
That will add the column at the beginning of the table (first column).
Remove FIRST from the end if you don't want that (it will then go at
the end) or replace it with: AFTER some_other_column
Sergei,
Any chance of getting a ft_dump Windows binary in the distribution? :-)
Regards,
Matt
- Original Message -
From: Sergei Golubchik
Sent: Monday, February 02, 2004 11:33 AM
Subject: Re: query the data of a fulltext index directly from index?
Hi!
On Feb 02, Alexander Bauer
Hey Roger,
- Original Message -
From: Roger Baklund
Sent: Saturday, January 24, 2004 7:09 PM
Subject: Re: A fun one
You shouldn't use text columns for ip/name... ip addresses fits in an
unsigned int
Yeah, I want to use an INT UNSIGNED column for IPs, which is great for
the space
Hi Balazs,
The likely answer is the one that nobody mentioned: it's an optimizer
bug in 4.0.16. If you look at the EXPLAIN output for the second query,
it's probably using a ref type on the sex column, instead of the more
restrictive id index. If so, that's the bug. From
- Original Message -
From: Peter J Milanese
Sent: Tuesday, January 20, 2004 1:37 PM
Subject: RE: Slow query times
You may also want to try :
count(1)
instead of
count(*)
count(*) pulls back the data while count(1) does not.
Completely untrue...
Matt
--
MySQL
Hi John,
- Original Message -
From: [EMAIL PROTECTED]
Sent: Thursday, January 15, 2004 2:28 AM
Subject: Re: How does key buffer work ?
Matt,
One last question and then I promise to drop the topic ... what would
be
the best way to force a complete load of an index into the key buffer
Hi John,
I'll give my comments. :-)
- Original Message -
From: [EMAIL PROTECTED]
Sent: Wednesday, January 14, 2004 2:04 AM
Subject: How does key buffer work ?
I've been trying to optimise the operation of a MySQL (4.0.13)
installation on a Windows 2000 based web server.
First, I'd
Hi John,
- Original Message -
From: [EMAIL PROTECTED]
Sent: Wednesday, January 14, 2004 6:37 AM
Subject: Re: How does key buffer work ?
Matt,
Many thanks for the answer. It has helped enormously.
First, I have been getting the odd index corruption that has proved to
be
very
Hi,
Sorry, I guess this is yet another question for Sergei! :-)
Since the full-text search TODO in the manual is a little vague (and
hasn't been updated much) and it was kind of a surprise when
multi-byte character-set support was added to 4.1 a couple months ago,
I'm wondering what surprises
Hi,
- Original Message -
From: Sergei Golubchik
To: Steven Roussey
Sent: Wednesday, December 10, 2003 7:44 AM
Subject: Re: 4.1.1 FTS 2-level?
Hi!
On Dec 09, Steven Roussey wrote:
Does Mysql 4.1.1 have the two level index system integrated into it
for full
text searches?
What do
Hi Hassan,
In a case like that where you know the data will always be a certain
length, CHAR is definitely better. VARCHAR will actually waste space (1
byte) when the data is always a certain length.
And yes, if using the CHAR allows your table to have fixed-length rows,
there will be a speed
Hi,
- Original Message -
From: Michael Stassen
Sent: Sunday, January 11, 2004 5:10 PM
Subject: Re: Automatic conversion from `char` TO `varchar`
Martijn Tonies wrote:
Hi,
The manual http://www.mysql.com/doc/en/Open_bugs.html says
The following problems are known and will be
Hi Dan,
Sending this to general list too since it's not a Windows specific
question.
Just run the UPDATE with all the column you want to update. :-) MySQL
won't update the ones that haven't changed (which may have been said).
Don't waste time trying to determine whether you should include a
Hi Keith,
I would assume it's because LEFT JOIN forced a change in the join order
(in EXPLAIN). Does using STRAIGHT JOIN give the same result?
So your query was this?
SELECT a.field
FROM table1 a
LEFT JOIN table2 b USING (field2)
ORDER BY b.field3 DESC
If table1 is read first (which it should
Hi Trevor,
MySQL itself doesn't cache any of the data (.MYD) file. The operating
system uses any free RAM to cache that file data. This is why I don't
think it's that important to have such a huge key_buffer, because some
of that memory would probably be better used for caching the data file.
Hi Ladd,
How about SELECT DISTINCT?
Hope that helps.
Matt
- Original Message -
From: Ladd J. Epp
Sent: Saturday, January 03, 2004 11:39 AM
Subject: FULLTEXT across two tables
Hello,
I would like to do a FULLTEXT search across two tables. I run an
artist
website, so I need to
Hi Fred,
InnoDB does not support AUTO_INCREMENT on secondary columns of a
multi-column index.
`id_registro` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id_formula`,`id_registro`)
There: id_registro is the second column of the index.
Matt
- Original Message -
From: Fred
Sent:
Hi Chris,
Nope, DEFAULT values have to be constants; no functions or anything.
:-/
What are you trying to do? And what's wrong with using TIMESTAMP since
you want a default of NOW()? If it's because you don't want it update
when you UPDATE the row, you can just set it to its current value, if
Hi Fred,
Also, you may be able to swap the order of those columns in the index.
I think that would work, but don't know if it would cause other
problems -- like for the way your app uses the index, etc.
Matt
- Original Message -
From: Fred
Sent: Saturday, January 03, 2004 6:11 PM
Hi Mike,
It's just part of modifying the column to change the DEFAULT value.
e.g. you might use this (changes to NOT NULL and DEFAULT value of
'new'):
ALTER TABLE table MODIFY type ENUM('new','used') NOT NULL DEFAULT 'new';
Hope that helps.
Matt
- Original Message -
From: Mike
Dan,
DATEDIFF() only works in MySQL 4.1.1+.
RTFM! ;-)
Matt
- Original Message -
From: [EMAIL PROTECTED]
Sent: Wednesday, December 31, 2003 2:10 PM
Subject: RE: Subtracting date fields
Kenneth,
try
SELECT id, DATEDIFF(firstdate, postdate) AS diff FROM
calendar
RTFM!
on 1/1/04 5:42 PM, Matt W wrote:
Dan,
DATEDIFF() only works in MySQL 4.1.1+.
RTFM! ;-)
Did you notice how the original poster didn't specify a version
number?
RTFOP,YSSOS.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
Hi Chris,
You're fine with mysql_real_escape_string(). % or _ only need to be
escaped if you're using them in LIKE and want them to match
iterally. -- never needs to be escaped in a string.
BTW, if you're using PHP and the stupid magic_quotes_gpc is on, you
don't want to escape stuff yourself
Hi Jeremy,
- Original Message -
From: Jeremy Zawodny
Sent: Monday, December 22, 2003 2:20 PM
Subject: Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH
On Fri, Dec 19, 2003 at 06:40:17PM -0600, Matt W wrote:
Hi Mark,
I'll tell you what I know. :-)
First, AVG_ROW_LENGTH is only
Hi Mark,
Maybe you intentionally only replied to me (instead of the list too),
but I'm sending this to the list also so others can follow the
discussion. :-)
I never know how much I have to explain things for a person's knowledge
level, but it sounds like you understand what's going on very
, Matt W wrote:
Hi,
Just have a couple more full-text search inquiries here. :-)
I'm not exactly clear on how matching rows are found when searching
for
2 or more required words: '+word1 +word2'. I understand that it
can't
currently know which word occurs less, so that it can be searched
Hi Mark,
I'll tell you what I know. :-)
First, AVG_ROW_LENGTH is only needed for dynamic row-length tables (it's
ignored with fixed-length rows) -- more specifically, those with
TEXT/BLOB columns. Otherwise, if MAX_ROWS is used, MySQL will assume
that each TEXT/BLOB column will be filled
Hi Andrius,
Yes, I've wondered about this before too, but wasn't exactly sure what
it meant either. :-) So I just decided to see where this state is set
in the code, and it's when the make_join_statistics() function is
called. I think that function checks key distribution and things to see
Hi,
Just have a couple more full-text search inquiries here. :-)
I'm not exactly clear on how matching rows are found when searching for
2 or more required words: '+word1 +word2'. I understand that it can't
currently know which word occurs less, so that it can be searched
first -- this
Hi,
Just have a couple more full-text search inquiries here. :-)
I'm not exactly clear on how matching rows are found when searching for
2 or more required words: '+word1 +word2'. I understand that it can't
currently know which word occurs less, so that it can be searched
first -- this
Hi,
I saw the change as soon as it was posted last week or whenever and
didn't think anything of it. But the point Yves brings up seems very
important!
Although, I'm not sure what to do then with bug #1812. Too bad MySQL's
code can't make database/table names case-sensitive like on *nix. e.g.
Hi Jamie,
Yes, when tables are made smaller in MySQL, the file size isn't
actually reduced. I guess it shouldn't usually make a speed difference
unless your SELECTs are doing a table scan. Reads that use an index
shouldn't really be affected, I don't think.
If you want to reclaim the deleted
Hi Vanessa,
I don't think I saw a reply to this...
You can just reconnect to MySQL if you get this error. :-) Trying to
send the query a second or third time may also make the client try to
reconnect again.
Hope that helps.
Matt
- Original Message -
From: Kiky
Sent: Friday,
Hi Peter,
You can probably safely have at least 1000-2000 tables in a single
database.
Hope that helps.
Matt
- Original Message -
From: peter
Sent: Friday, November 28, 2003 12:03 PM
Subject: newbie question
Hi
I am a webdesigner/hosting reseller
my question is this:
I am
Hi Alejandro,
Yeah, this issue has come up before. It's not possible to GRANT DROP on
temp tables without GRANTing DROP on the whole database.
The temp tables will be dropped when the client disconnects you know,
right? And if you want to empty the table or reuse it, you should be
able to
Hi Chris,
I don't know exactly what you mean by ALTER being as good as OPTIMIZE...
But yes, an ALTER that recreates the data file (as ALTER ... ORDER BY
does) will defragment the data file too.
However, OPTIMIZE also analyzes the key distribution (I don't know if
it's remembered after an ALTER
Hi,
Yes, you would have similar results with any query that uses
SQL_CALC_FOUND_ROWS. That's because MySQL has to see how many rows would
be found without the LIMIT. So in your case, it can't just abort the
query after it finds 10 rows. All rows that match the WHERE need to be
found.
You might
Hi Ed,
Yeah, I just installed today's 4.1.1-alpha-nt on Win2k SP3 and get the
same thing. :-( Sucks, 'cause named pipes are a lot faster for me than
TCP/IP. And I was really looking forward to this release. It's just
not the same with TCP/IP. :-(
Matt
- Original Message -
Subject:
Hi Scott,
Those aren't processes. There is 1 process with many threads and your
system is reporting them as separate processes. :-)
Hope that helps.
Matt
- Original Message -
From: Scott Stingel
Sent: Monday, December 01, 2003 4:47 PM
Subject: mysql 'start' spawns 10 instances of
Hi,
Create the indexes right away and then use
ALTER TABLE table DISABLE KEYS;
Load your data and then
ALTER TABLE table ENABLE KEYS;
This will not make a tmp copy of the data file, but will simply start
rebuilding the index.
However, DISABLE KEYS doesn't disable unique indexes, so these
Hi,
For the query that you would need, see this page in the manual:
http://www.mysql.com/doc/en/example-Maximum-column-group-row.html
Also see the comment from March 16, 2003 about the LEFT JOIN trick.
However, in your case, why don't you just add another column in the
tickets table?
Hi,
- Original Message -
From: Chuck Gadd
Sent: Wednesday, November 26, 2003 2:29 PM
Subject: Re: Disorder result with ORDER BY with ENUM, INT
Kriengkrai J. wrote:
-- System: MySQL 4.0.13, 4.0.16 on Linux x86
-- Table type: MyISAM, InnoDB
-- Description / How-To-Repeat:
--
Hi Chris,
It doesn't take MySQL any more or less time to update a unique index
than a non-unique one. :-)
Hope that helps.
Matt
- Original Message -
From: Chris Elsworth
Sent: Wednesday, November 26, 2003 12:14 PM
Subject: Unique Index efficiency query
Hello,
Let me just
Hi Mark,
Keep in mind that the CAST() function doesn't work in MySQL 3.23. If you
want something that will work with 3.23 and 4.0+, you can just add 0.0
to your expression:
SELECT 0 - unsigned_col + 0.0 AS alias FROM ...
The result will have .0 on the end then, but I think you can take care
of
Hi,
!= and are not optimized currently because I think it's assumed that
with
a b
more rows will NOT match b than do match. Therefore it's faster to do a
table scan.
That assumption is not true in all cases of course, which is why I think
it will be optimized in the future to estimate how
Hi,
You can combine those 2 UPDATEs like this:
UPDATE some_table SET some_field=IF(id=some_id, 1, 0);
Or, the standard SQL syntax:
UPDATE some_table
SET some_field=CASE id WHEN some_id THEN 1 ELSE 0 END;
Hope that helps.
Matt
- Original Message -
From: [EMAIL PROTECTED]
Sent:
Hi Jon,
The -log suffix is added when you're running with logging (log or
log-bin in my.cnf/my.ini). log-bin may be being used for replication, so
be careful about removing it. And if one server isn't using logging, you
probably don't need it.
Hope that helps.
Matt
- Original Message
Hi Arnaud,
A disk-based temp table is used if you're SELECTing a column [that can
be] longer than 255 characters (BLOB/TEXT to MySQL). This is because the
in memory HEAP tables don't currently support variable length rows.
Using something like LEFT(text_col, 255), if feasible, will get around
Hi Richard,
As I think Gerald Clark said, you could run DELETEs with LIMITs (like
1000-1, etc. at a time) in a loop until rows all rows are deleted.
This won't make the deletes any faster (probably slightly slower total,
actually), but will allow other clients to use the table in between.
Hi Yves,
http://www.mysql.com/doc/en/JOIN.html
table_reference [INNER | CROSS] JOIN table_reference [join_condition]
The [ ... ] means that INNER is optional -- in MySQL at least, not
sure about the SQL standard.
Hope that helps.
Matt
- Original Message -
From: Yves Goergen
Sent:
Hi Mike,
Those tables aren't that big for what you're doing (which is about how
I'd do it if I wasn't using built-in full-text :-)).
How many results are your searches returning? How long are the queries
taking?
The C table: Do you need the index on content_id? For deletes or
something? Doesn't
Hi Rob,
Since you're using 4.0.16, sounds like you are experiencing its
optimizer bug. From the ChangeLog for 4.0.17 (not released yet):
* Fixed optimizer bug, introduced in 4.0.16, when REF access plan was
preferred to more efficient RANGE on another column.
So hopefully the problem only
Hi Thai,
I think you're just joining wy too many tables! LOL
Matt
- Original Message -
From: Thai Thanh Ha
Sent: Sunday, November 16, 2003 8:10 PM
Subject: Please help DB Error: unknown error
Hi all,
I have a problem with my query on mySQL 4.0.
DB Error: unknown error
I
1 - 100 of 216 matches
Mail list logo
