Re: INDEX DESC

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 l

Re: INDEX DESC

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 >

Re: Full text search problem

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 distri

Re: Help with apostrophe and FTS

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 to

Re: Column's DataType -- TEXT vs BLOB...

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 l

Re: Tuning MySQL 4.0.20 for large full table scans

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

Re: Table types

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 w

Re: Specifying an index length and the default value

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 o

Re: Another Trailing Spaces Issue

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,

Re: Indexing

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 say

Re: Delayed insert record visibility

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 'd

Re: Storing a space

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 val

Re: fulltext index -- word "needs" not found

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.

Re: Slow Query Question - Need help of Gurus.

Ben, - Original Message - From: "Ben Dinnerville" Sent: Monday, April 19, 2004 1:49 AM Subject: RE: Slow Query Question - Need help of Gurus. >>Then try again: >> >> SELECT `Call Svc Tag ID`, >> Count(*) as counter, >> `Journal Create Date` >> FROM 31909_859552

Re: Altering MAX_DATA_LENGTH

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"

Re: backup

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 TABLE

Re: Fulltext index is not being built with large database

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: "s

Re: Question regarding defaults

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 -

Re: Why can't I use an "AS" value in the WHERE clause.

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 Vince

Re: Is this possible?

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:[E

Re: mysqld keeps crashing

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

Re: "ORDER DESC" vs. "ORDER ASC" exec time

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 TABLE.

Re: Stored Procs and Commit/Rollback Transactions

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 (m

Re: String Concatenation Operator?

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 i

Re: BETWEEN

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

Re: BETWEEN

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

Re: query question using REGEXP

EXP > Hi thanks 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

Re: query question using REGEXP

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 questi

Re: BETWEEN

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 searc

Re: 3 000 000 requests for last 14 days...

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

Re: mysqladmin processlist and pid

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: "To

Re: Join Definitions

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/JOI

Re: Corrupt full text index

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 ta

Re: HOWTO add Primary Key to Existing 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, Feb

Re: fulltext search always returns no results

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. :-) Also

Re: HEAP tables vs MYISAM on ramdisk

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, > admitte

Re: run query second time

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 > sec

Re: Encryption Issue

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 stateme

Re: Improving seek/access times -- does RAID help?

lp. Matt - Original Message - 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 > Sen

Improving seek/access times -- does RAID help?

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 i

Re: Transferring comma-delimited list imto mysql table

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 --

Re: Indexed searching with OR ?

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 Subjec

Re: mysqldump via tcp/ip memory problem

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. M

Re: Massive memory utiliazation

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 r

Re: key_reads > key_read_requests

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: Frida

Re: query the data of a fulltext index directly from index?

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 Ba

Re: mySQL autogenerate, update table

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_colum

Re: Server Behavior.

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 h

Re: query the data of a fulltext index directly from index?

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, >

Re: A fun one

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 s

Re: Slow query times

- 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 --

Re: Slow query times

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 http://www.mysql.com/do

Re: How does key buffer work ?

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 b

Re: How does key buffer work ?

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 > ver

Re: How does key buffer work ?

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

Re: Loading the .myd into memory

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. In

Re: JOIN types

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

Re: query efficiency

Hi Dan, 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 column in the UPDATE or not. The only time it could be an issue is if you're se

Re: Automatic conversion from `char` TO `varchar`

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 says > >> > >> > >>>The following problem

Re: Which one is better: CHAR or VARCHAR?

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 im

Re: 4.1.1 FTS 2-level?

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 full-text improvements are next?

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"

Re: ISM/ISD file compatibility fo 64bit myqsl? no?

Hi, IIRC, ISAM files are not portable between platforms, but MyISAM ones are. So I think you'll be fine if they work. :-) Matt - Original Message - From: <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 3:39 PM Subject: ISM/ISD file compatibility fo 64bit myqsl? no? > > Are ISM

Re: Alter table and setup Default value

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 Maps

Re: Converting MyISAM to InnoDB type.

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 Sub

Re: Default DATE field values

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 y

Re: Converting MyISAM to InnoDB type.

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" Se

Re: FULLTEXT across two tables

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 nee

Re: special characters as field values

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 a

Re: Subtracting date fields

ct: Re: Subtracting date fields > 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.

Re: Subtracting date fields

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 > >

Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH

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 well.

Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH

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 kno

Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH

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 complet

Re: How boolean full-text search finds matches?

Hi Sergei! Thanks for replying again. I hope I'm not wasting too much of your time with my questions! :-) - Original Message - From: "Sergei Golubchik" Sent: Thursday, December 18, 2003 7:17 AM Subject: Re: How boolean full-text search finds matches? > Hi! >

Re: show processlist

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 which

Re: high water mark

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 sp

Re: MySQL 4.0.17 has been released

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.

How boolean full-text search finds matches?

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 optimizat

How boolean full-text search finds matches?

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 optimizat

Re: ALTER TABLE .. ORDER BY

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 o

Re: Temporary tables rights

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 TRUNCAT

Re: newbie question

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

Re: Lost connection to MySQL server during query - pls help

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, Decemb

Re: Named Pipe crashes on MySQL (4.1.1 alpha) WinXP

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: N

Re: fulltext search speed issue with SQL_CALC_FOUND_ROWS

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 wa

Re: mysql 'start' spawns 10 instances of mysqld

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 m

Re: Disorder result with ORDER BY with ENUM, INT

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-Repe

Re: list, order and limit data

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? last_respon

Re: Index before or after inserts?

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 stil

Re: Unique Index efficiency query

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 ou

Re: strange difference between a != b and (a < b OR a > b)

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

Re: Why does -1 show up as 18446744073709551613?

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

Re: UPDATE optimization?

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

Re: Optimizing Custom Full Text Index

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

Re: JOIN vs INNER JOIN?

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" Sen

Re: very slow delete queries - never ending

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. Hav

Re: using temporary / using filesort and disk tables

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 this

Re: Standard vs. Log

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 --

Re: php and passing implicit connection identifiers

Hi Stephen, You can always omit the connection id in PHP (unless you have multiple connections open for some reason). Matt - Original Message - From: "Stephen Fromm" Sent: Monday, November 17, 2003 8:26 AM Subject: php and passing implicit connection identifiers > The connection iden

  1   2   3   >