Named pipe vs TCP/IP speed

Hi all, I just noticed this in the manual yesterday: http://www.mysql.com/doc/en/Windows_running.html MySQL supports TCP/IP on all Windows platforms. The mysqld-nt and mysql-max-nt servers support named pipes on NT, 2000, and XP. The default is to use TCP/IP regardless of the platform, because

Re: Named pipe vs TCP/IP speed

- Original Message - From: Jakob Dölling Hi: This surprised me! I have been connecting with named pipes (and telling others to do the same) on Win2k since I discovered them, because my queries (and time to connect) are most definitely *faster*. The client/server communication is

Lots of FULLTEXT stuff (suggestions)

Hi all, I'm planning to use MySQL's full-text search for my forum system (possibly 5+ million posts). I've been playing with it a lot lately to see the performance and functionality and have some suggestions/questions. First, since a few of you may be wanting to know, here is a thread where I

Re: Upgrading 3.23-4.0 under WinXP ?

- Original Message - From: Thomas Andersson Sent: Saturday, August 23, 2003 8:15 AM Subject: Upgrading 3.23-4.0 under WinXP ? Hi! I'm pretty green at this and I'm a bit clueless here, After reinstalling my system I upgraded from 3.23 to 4.0 of MySQL, then I moved my old dbs in

Re: Lots of FULLTEXT stuff (suggestions)

Hi Steven, Thanks for replying. Your posts that I've found when searching for FULLTEXT information have had great ideas. :-) Searching millions of posts efficiently and effectively isn't easy. :-( Heh. Thinking about ft_min_word_len some more (and how I wish they'd lower the default), I don't

Re: Explanation of multiple-column indexes

Hi Jesse, - Original Message - From: Jesse Sheidlower Sent: Monday, August 25, 2003 8:26 AM Subject: Explanation of multiple-column indexes After some discussion in a separate thread, I've been trying to get a better understanding of the workings of multiple-column indexes, and think

Re: Random Selects

Hi Jay, There seemed to be a problem with the first use of RAND() on new connections (is this in a script?). It was supposed to be fixed in a recent version of MySQL (I think in 3.23.56 and 4.0.10). Anyway, the workaround I discovered to work is to just call RAND() at least once before using it

Re: Newbie Table question

Hi, - Original Message - From: Eternal Designs, Inc Sent: Saturday, August 30, 2003 9:46 PM Subject: Re: Newbie Table question To copy table A to table B(non-existent) use this command: CREATE TABLE B SELECT * FROM A; Note. If your two tables are from different databases the syntax

Re: spaces in index name

Hi Ben, When using column/index names with odd characters (such as spaces), you need to use backticks (SHIFT + the ~ key) around the name: `index id` This ALTER TABLE query should make all the changes you want: ALTER TABLE v2easy0_users DROP INDEX `login connexion`, DROP INDEX `index id`, --

Re: spaces in index name

Hi, - Original Message - From: Benjamin KRIEF Sent: Sunday, August 31, 2003 8:17 PM Subject: Re: spaces in index name thanks for all your answers ! i'd never imagine that mailing-list to be so active and efficient! It's usually not that active on weekends, volume-wise. And I see

Re: [q] can I start MySQL in READONLY mode?

Hi Mark, How about just giving the user(s) only the SELECT privilege temporarily? Matt - Original Message - From: Mark Swanson Sent: Monday, September 01, 2003 7:33 PM Subject: [q] can I start MySQL in READONLY mode? Hello, I have a situation where I'd like to do some debugging

Re: Many Read and Writes...

Hi, If the index file is just 1k (the same size as an EMPTY table!), it sounds like you don't have any indexes. The 8.6MB table is probably at least a few thousand rows, right? Well, if all your queries are scanning the whole table, that would cause a few Table_locks_waited! :-) In order to help

Re: Select from one table where ID not in another table

Hi Marty, Yes, a query like this: SELECT t1.* FROM table1 t1 LEFT JOIN table2 t2 ON (t2.id=t1.id) WHERE t2.id IS NULL This assumes that table2.id is defined as NOT NULL. See also: http://www.mysql.com/doc/en/JOIN.html Hope that helps. Matt - Original Message - From: Martin Moss

Re: Query not returning 0 count records....

Hi Mike, If you use a LEFT JOIN, I think you'll get the results you want. Something like SELECT afs.stat_date, afs.hits, COUNT(v.clientcode) AS signups FROM affiliate_stats AS afs LEFT JOIN vtconlineusers AS v ON (v.creation_date=afs.stat_date AND v.dealercode=afs.affiliate_id) WHERE

Re: Query hung up in Copying to tmp table

Hi Kevin, I imagine the query is examining/returning so many rows that it's going to take a long time to create the needed temporary table (how long have you it go?). To start with, show us the EXPLAIN output for the problem SELECT, along with the SELECT. Also the size of the involved tables

Re: keeping a fulltext index in memory

Hi Mark, I'm no Linux expert, but I think you would look at the difference between the SIZE and RSS values in top (or the equivs. in other progs...). Also IIRC, from your first message, I don't think you're using a full-text index in your query, are you? I think I saw column LIKE '%word%' and

Re: web hosting/PHP MyAdmin

Hi Matthew, Before I started using phpMyAdmin, I didn't want anything to do with it. :-) But now I find it to be nice for quick things like browsing tables, quick edits, table statistics, etc. For queries that return large amounts of text, it's MUCH more legible than the command line. :-) Keep

Re: question about lock tables and unlock table

Hi Steven, Just one UNLOCK TABLES. :-) From http://www.mysql.com/doc/en/LOCK_TABLES.html LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues

Re: Query hung up in Copying to tmp table

never seen mysql spit out a temporary file while processing. Again, I'd really like someone's reference to a tuning guide, if there's a superior one available. Thanks again, Kevin -Original Message- From: Matt W Sent: Thursday, September 04, 2003 1:37 PM To: [EMAIL PROTECTED

Re: --skip-locking and 'enable locking' in my.cnf

- Original Message - From: Jeremy Zawodny Sent: Monday, September 08, 2003 11:33 PM Subject: Re: --skip-locking and 'enable locking' in my.cnf On Mon, Sep 08, 2003 at 10:42:33PM -0500, Paul DuBois wrote: See: http://www.mysql.com/doc/en/System.html Note the part about Linux.

Re: Lost Connection to MySQL server during query

Hi Donald, Is the script possibly sending a query larger than max_allowed_packet (1MB default)? Have you read http://www.mysql.com/doc/en/Gone_away.html ? Matt - Original Message - From: Donald Tyler [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September

Re: MySQL full text search multiple tables

Hi, Actually, I don't see why you can't have your full-text indexes on seperate tables and use a query like this: SELECT MATCH(t1.col) AGAINST('string') + MATCH(t2.col) AGAINST('string') AS rel FROM table1 t1 INNER JOIN table2 t2 ON (t2.id=t1.id) WHERE MATCH(t1.col) AGAINST('string') AND -- or

Re: FULLTEXT feature requests

Hi Shane, - Original Message - From: Shane Allen Sent: Wednesday, September 10, 2003 7:16 PM Subject: FULLTEXT feature requests The only reason that we are unable to use a stock build of mysql is because 1) we use GWS_FREQ as our GWS_IN_USE, not GWS_PROB Why is that exactly...? To

Re: mysql dump speed

Hi Dan, - Original Message - From: dan orlic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 11, 2003 2:30 PM Subject: mysql dump speed I have a bit of an issue with mysqldumps and inserting it back into mysql. Granted, I have 1 table that has over 1Million

Re: Lots of FULLTEXT stuff (suggestions)

Hi Sergei! Thanks for your reply and taking time to read and consider my suggestions. :-) I didn't reply sooner because I was deciding what to say in this message. ;-) I joined the list specifically for posting these suggestions, and, with your reply, I wanted to say that it's great to have

Re: Initial Table Access Slowness

Hi John, - Original Message - From: John May Sent: Sunday, September 14, 2003 7:52 AM Subject: Initial Table Access Slowness I'm noticing that if a table hasn't been used in a while, it takes MySQL considerably longer than normal to do the first access of it. Once that happens, it's

Re: LOAD Fails on Lost Connection

Hi Randy, See here: http://www.mysql.com/doc/en/Gone_away.html Maybe one of the queries in import_cash.sql is longer than max_allowed_packet? - Original Message - From: Randy Chrismon Sent: Monday, September 15, 2003 4:50 PM Subject: LOAD Fails on Lost Connection I've tried this

Re: (4.0.14) Corrupt table on DELETE FROM ...

Yep, verified here with .15-nt on Win2k. :-/ I'm sending this to the Bugs and General lists because I'm assuming it affects all platforms; or is it only Windows? Matt - Original Message - From: Fredrick Bartlett Sent: Tuesday, September 16, 2003 7:40 PM Subject: Re: (4.0.14) Corrupt

Re: Lock tables in myisam

Hi, No, ROLLBACK doesn't work with MyISAM. Jeremy meant that you can add logic/code between LOCK/UNLOCK to simulate ROLLBACK. e.g. queries that undo what you did if something goes wrong. Of course this won't cover you if mysqld dies, is killed, or you lose the connection etc. as real transactions

Re: using OR in select statement with distinct results

- Original Message - From: Paul DuBois Sent: Thursday, September 18, 2003 8:17 PM Subject: Re: using OR in select statement with distinct results At 7:47 PM -0500 9/18/03, Sam Folk-Williams wrote: Hi, I keep having this fundamental problem. It seems like this should be easy

Re: Can't Show Warnings

Hi Randy, 4.1.1 hasn't been released yet unfortunately. The devs have said that they've started release testing or whatever it's called. I think they said it will hopefully be released in 4-6 weeks. :-) Matt - Original Message - From: Randy Chrismon Sent: Thursday, September 18, 2003

Re: mysqld consumes 1.3Gb of swap for simple query on solaris

Hi Tom, Sounds odd... Do other queries that behave normally use GROUP BY or DISTINCT? What are your configuration variables? e.g. SHOW VARIABLES or mysqladmin variables. Is sort_buffer_size set to some huge value? Matt - Original Message - From: [EMAIL PROTECTED] Sent: Friday,

Re: explain

Hi, - Original Message - From: Jeremy Zawodny Cc: [EMAIL PROTECTED] Sent: Friday, September 19, 2003 5:12 PM Subject: Re: explain On Fri, Sep 19, 2003 at 02:12:02PM -0700, Hsiu-Hui Tseng wrote: Hello, When I do a explain on a query, I got the following:

Re: Simple Stored Procedure Emulation with PHP/mySQL

Hi, myphp looks like it's pretty slow going by the times reported in the examples. :-/ Probably something to do with all that PHP junk loaded into MySQL. :-) And no, I don't think you can do any stored procs with it; only apply PHP functions etc. to database values. Matt - Original

Re: INSERT/UPDATE number weirdness

Hi James, Well if you used [VAR]CHAR/TEXT and then did the INSERT query *with* the leading 0s, it should work. If the 0s aren't there in PHP, they won't be stored in the CHAR column though. But anyway, it's more efficient to stick with SMALLINT... and use the ZEROFILL attribute on the column.

Re: Just a small newbie question

Hi Charlie, Don't worry, all questions are OK. :-) Instead of trying to double-click mysql.exe, open your Command Prompt and, assuming the mysql\bin directory isn't in your PATH, navigate to to mysql\bin directory and run mysql from there or specify the full path. e.g. C:\cd \mysql\bin

Re: 300 seconds in sending data phase

*** Is everyone else getting ~50% virus e-mails on the list?? :-( *** Hi Kevin, Don't know what's causing the hang-up on those queries, as they seem like they should be fast as you said (unless it's some mod_perl/DBI interaction -- no idea). :-/ Just responding to 2 other things... -

Re: Query Cache not being used...

Hi Daniel, Yes, query_cache_type is 1 by default if you don't set it. :-) It's not used by default, however, because query_cache_size is 0. You need to set query_cache_size to 16M, 32M, etc. Hope that helps. Matt - Original Message - From: Daniel Kasak Sent: Thursday, September 25,

Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR

Hi, - Original Message - From: Roman Neuhauser Sent: Friday, September 26, 2003 6:05 PM Subject: Re: FOREIGN KEY Weirdness in mySQL 4.1 with VARCHAR # [EMAIL PROTECTED] / 2003-09-26 15:12:03 -0700: --- Harald Fuchs [EMAIL PROTECTED] wrote: Ed Smith [EMAIL PROTECTED] writes:

Re: Can someone explain the difference between these two queries?

Hi, Table order can definitely make a difference in the order that MySQL actually reads them. Like if MySQL *thinks* the cost to join 2 different tables is equal, it will use the one that's listed first, first. But if you know the cost is different and change the table order, it can help the

Re: slow 'sending data' phase

Hi Kevin, - Original Message - From: Kevin Sent: Monday, September 29, 2003 2:21 PM Subject: slow 'sending data' phase I've fixed my swapping issues, but the system continues to get stuck in a 'sending data' phase from time to time. With mod_perl + mysql, this phase SHOULD be when

Re: insert into x select * from x doesn't work

Hi, 4.1.0 was released before 4.0.14 so it wouldn't have the new functionality in it. It should be in 4.1.1 but hasn't been added to the change-log yet. Matt - Original Message - From: Director General: NEFACOMP Sent: Monday, September 29, 2003 4:51 AM Subject: Re: insert into x

Re: Newbye speed question - which setup to use for indexing

Hi, No, MySQL never uses multiple CPUs for the same query at the same time (the OS could switch the thread between CPUs over time, but that doesn't count :-)). Each connection gets one thread and one thread can only run on one CPU at a time. It would be pretty hard (if not impossible) to have

Re: ip range lookup

Hi Willem, I don't *think* MySQL optimizes BETWEEN like that to use an index. Have you tried this?: SELECT * FROM ipcountry WHERE ip1 = 123456789 AND ip2 = 123456789; Matt - Original Message - From: Willem Bison Sent: Saturday, October 04, 2003 6:08 PM Subject: ip range lookup I

Re: slow performance with large or list in where

Hi, - Original Message - From: Marc Slemko Sent: Sunday, October 05, 2003 2:27 PM Subject: Re: slow performance with large or list in where On Sun, 5 Oct 2003, Santino wrote: Have You test in operator? select * from table where id in (10,20,30,50,60,90, ) Yes, IN does

Re: MySQL: Ordering Random Records

Hi Stuart, Good solution! I didn't even think of that. :-) Interestingly, and FYI, it doesn't work correctly on 4.1.0 (ignores the LIMIT). Hopefully it's a known bug that's been fixed in 4.1.1. 4.1.0 does work correctly though using derived tables as John Bonnett suggested: SELECT * FROM

Re: Innodb won't recognize index when optimizing query

Hi Heath, MySQL cannot use the trans_team query because you're using !=, for which an index is never used (currently anyway). Do you think that trans_team is the best index that will find the least rows and produce the fastest result? If so, you can try using the following, which can be

Re: storing tables in ram / compressed myisam tables

Hi Gabriel, No, you can't currently modify compressed tables. For in memory tables, are you aware of the HEAP type for non-critical data? You're probably talking regular permanent tables, though. For those, the OS will use all free memory (that programs aren't using) to cache file data after

Re: unexpected index behaviour...

Hi Peter, I just tried it on a table with 10 rows and it works fine on 4.0.15. Have you already tried on a small table? How many rows is your WHERE clause matching when it's not using the index? Do you know that MySQL won't use an index if it thinks it will find more than about 30% of all rows?

Re: Fulltext index, stopword list changes

Hi Peter, I don't know what your multiple languages are, but remember that full-text indexing doesn't currently work with multi-byte character sets. Anyway, the built in stopword list is in the myisam/ft_static.c file of the source code. This is mentioned in the manual for SHOW VARIABLES:

Re: Disable/Enable Keys and Duplicate Records

Hi Randy, Unique keys are not disabled with DISABLE KEYS -- so the unique constraint isn't violated. From http://www.mysql.com/doc/en/ALTER_TABLE.html ALTER TABLE ... DISABLE KEYS makes MySQL to stop updating *non-unique* indexes for MyISAM table. Matt - Original Message - From:

Re: ALTER TABLE z ADD INDEX... Speed: Follow up to LEFT JOIN question

Hi Dan, 133MHz huh? :-) Well, how large is the table? Huge rows? How many other indexes are on the table and on how many columns? Are those columns large? If you had a full-text index on a large column, for example, it could take very long on that system, especially if you're using 3.23. Hard

Re: network interfaces

Hi Dan, Instead of skip-networking, use bind-address in my.cnf: bind-address=192.168.0.1 I think you can just specify 1 IP like that. So... you either have 1) listening on no IP (skip-networking), 2) listening on 1 IP (bind-address), or 3) listening on all IPs (the default). BTW, I think even

Re: How to speed up query?

Hi Ganbold, It's taking 35 seconds because it has to look at 2000+ [large] rows in the data file to get the value of size. If the index is just on uid, remove it and make a composite index on (uid, size): ALTER TABLE message DROP INDEX uid, ADD INDEX uid_size (uid, size); It should then be

Re: Failed updates

Hi Jo, Well, that UPDATE query doesn't look like it's *changing* the value of the columns with the UNIQUE index. Are you sure there's no unique index on the columns that ARE being updated? e.g. is id_token_data_01 key 1? (See if it's the first row returned by SHOW INDEX FROM dspam_token_data.)

Re: Pronunciation of ISAM table name

Hi Jim, As far as I know: eye-sam my-eye-sam. At least that's how I pronounce them. :-) Matt - Original Message - From: Jim Mathews Sent: Saturday, October 18, 2003 3:44 PM Subject: Pronunciation of ISAM table name I have a very basic newbie question that, despite multiple

Re: MySQL's 'myslq' database

Hi Dan, If you manually modify the mysql database without using GRANT, then you need to FLUSH PRIVILEGES afterwards to get MySQL to reload the permissions. Of course, restarting the server has the same effect. Using GRANT takes care of everything automatically, though. Hope that helps. Matt

Re: Mysql Performance Question

Hi Rainer, You might get more improvement by optimizing your application and queries than by tuning hardware or MySQL. :-) About switching to InnoDB, are you doing lots of writes that are causing locked tables? e.g. What's the ratio of Table_locks_immediate to Table_locks_waited in SHOW STATUS?

Re: Mysql Performance Question

Hi, I really doubt putting PHP files (or any site files) on a RAM disk will make any performance difference; and certainly not 30-50%. When the files/scripts are accessed, they are cached by the OS. So, in effect, they are automatically put in a sort of RAM disk. The disk is definitely NOT

MATCH ... AGAINST('...' WITH QUERY EXPANSION) syntax?

Hi Sergei, More full-text questions from me since I just noticed your code and doc changes. :-) What does this new WITH QUERY EXPANSION syntax do? More relevant results? More flexible? Faster? Is it for NLQ, boolean, or both (since both ft_[nlq | boolean]_search.c are changed)? Does it have

Re: Syntax for SQL Query - used to work with Access

Hi Trevor, I think that query might work as-is in MySQL 4.1. However, the current production version doesn't support subqueries. Your query can be rewritten with a join like this I think: SELECT C.*, COUNT(*) AS LINK_COUNT FROM Categories C INNER JOIN Links L ON (L.CAT_ID=C.CAT_ID AND

Re: Really slow query (compared with Visual FoxPro)

Hi Hector, Umm, it looks like you're simply doing a SELECT in the first query and *populating a new table* in the second. Of course inserting 3.3 million rows is going to take extra time! How can you even compare the 2 when they're doing different things? Matt - Original Message -

Re: selecting from two different severs

Hi Mike, Simple; you don't. :-) Someone please correct me if I'm wrong. BTW, this isn't a Windows specific question, so I'm sending it to the General list too. Regards, Matt - Original Message - From: Mike Karplus Sent: Friday, October 24, 2003 5:04 PM Subject: selecting from two

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

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

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

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

Re: DB not restoring from dump file

Hi, Well, UNIQUE is a reserved word in all versions of MySQL... As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump, by default, does not put backticks

Re: I can't figure out what I thought would be a simple query..

Hi guys, Have you seen the manual page for The Rows Holding the Group-wise Maximum of a Certain Field: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html I think that's what you want to do. You can either use another temporay table, the MAX-CONCAT trick, or the LEFT JOIN ... IS

Re: Corruption and weird service terminations

Hi Mike, For the corruption, upgrade to 4.0.16, since it may be caused by a corruption bug in versions before 4.0.15. Matt - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, October 27, 2003 12:07 PM Subject: RE: Corruption and weird service terminations

Re: CREATE FUNCTION problem

Hi George, I think the MySQL-Max RPM is dynamically linked (all -max binaries actually) if you want to give it a try. Hope that helps. Matt - Original Message - From: George Chelidze Sent: Monday, October 27, 2003 9:18 AM Subject: CREATE FUNCTION problem Hello, I have created new

Re: Limit Optimization??

Hi, Yes, MySQL stops searching for rows once the LIMIT is satisfied, as long as filesort isn't used for an ORDER BY. But your LIMIT 150, 20 will take much longer (assuming filesort isn't used) than, say, LIMIT 1000, 20. This is because it has to scan over 1.5M rows first. It's not really

Re: cause of table crash

Hi, What version of MySQL are you using? Maybe it's because of a corruption bug in versions 4.0.3 - 4.0.14. Try upgrading to the latest version. Hope that helps. Matt - Original Message - From: Datatal AB - Gauffin, Jonas Sent: Monday, October 27, 2003 6:40 AM Subject: cause of

Re: interpreting extended statistics

Hi Mark, I say PHP below because I'm just assuming that's what's connecting to MySQL. :-) - Original Message - From: Mark Teehan Sent: Monday, October 27, 2003 2:42 AM Subject: interpreting extended statistics Hi I am a new MySQL dba tuning a busy Apache/MySQL installation. I could

Re: Limit Optimization??

the 'where' 'order by' clause in the SELECT . can i need more indexs ? thx Matt. Matt W [EMAIL PROTECTED] wrote [EMAIL PROTECTED] Hi, Yes, MySQL stops searching for rows once the LIMIT is satisfied, as long as filesort isn't used for an ORDER BY. But your LIMIT 150, 20 will take much

Re: Limit Optimization??

), KEY CreatedTime (CreatedTime), ) TYPE=MyISAM ; My query: SELECT ArticleID FROM article WHERE ClassID = 101 AND Auditing = 1 ORDER BY CreatedTime DESC LIMIT x , y I allreday created the key1 and the CreatedTime key. And it's even slowly now ... :( Avenger Matt W [EMAIL PROTECTED

Re: Limit Optimization??

, CreatedTime, ArticleID) could matt explain why? Thx matt.. On Tue, 28 Oct 2003 02:06:16 -0600 Matt W wrote: Hi, OK, did you just create key1 and CreatedTime? If you didn't have them before and don't need them, you can remove them and just create this index for your query (you can

Re: Need help on WHERE ... LIKE Query

Hi, No, col BETWEEN 'A' AND 'D' is not the same as col = 'A' AND col 'D'. BETWEEN is equivalent to col = 'A' AND col = 'D'. One will include cols that equal 'D' and the other won't. :-) Matt - Original Message - From: Brent Baisley Sent: Thursday, October 30, 2003 3:12 PM Subject:

Re: WHERE IN performance

Hi Jim, It's probably the time taken to parse the query with so many IN () values than it is to actually look them up. You can get an idea of how long it takes to parse the query by comparing the time with 1 IN value vs. thousands without the IN values actually matching any rows (e.g. dummy

Re: Column Types Changing

Hi, I don't think MySQL is doing what's best. *I* am the one who knows best and I don't want MySQL changing column types. An example is a table I have where I'd like to have a TEXT column with a value that is basically NEVER changed after being inserted. I can run OPTIMIZE on this dynamic row

Re: Column Types Changing

Hi Brent, - Original Message - From: Brent Baisley Sent: Friday, October 31, 2003 7:39 AM Subject: Re: Column Types Changing Unless your TEXT field has the exact same length in every record, it doesn't really matter whether it changes or not. You have variable length records. Thus,

Re: Too Many Columns ERROR 175..

Hi Mike, From the last paragraph at http://www.mysql.com/doc/en/Storage_requirements.html The maximum size of a row in a MyISAM table is 65534 bytes. Each BLOB and TEXT column accounts for only 5-9 bytes toward this size. I assume from this that a VARCHAR(255) column (or even VARCHAR(10)) would

Re: mySQL with a quad processor system

Hi Richard, Nope, since your OS can run the threads on different CPUs (unlike FreeBSD w/o LinuxThreads for example). You just need 4+ threads (clients) running queries at once. :-) BTW, what kind of system? How fast are those 4 CPUs? ;-) Matt - Original Message - From: Richard

Re: theoretical row/record limit of mysql?

Hi, I believe the row limit is 4,294,967,295... or is it 4 billion even? Hmm. Hope that helps. Matt - Original Message - From: joffrey leevy Sent: Sunday, November 02, 2003 7:41 PM Subject: theoretical row/record limit of mysql? Hi all: Does anyone know the maximum number of

Re: query time in ~3M row table

Hi, Yes, make a composite index by adding ApacheDate as the second column in the urlIndex index. As a side note, Brent said that BETWEEN is not inclusive of the second parameter. But it IS inclusive. However, since you have a DATETIME column, there is no row with an ApacheDate of *exactly*

Re: RAND ()

Hi Payne, This is probably because of a bug in MySQL versions before 3.23.56 and 4.0.10 -- RAND() didn't work right the first time it was used in a *new* connection. After the first run, though, it was OK. To work around the problem, I've told people to just call RAND() a few times before doing

Re: query time in ~3M row table

a date column is generally search for an exact or range of values, and the urlIndex appears to get searched on as a begins. regards, sean peters [EMAIL PROTECTED] On Tuesday 04 November 2003 17:29, Matt W wrote: Hi, Yes, make a composite index by adding ApacheDate as the second column

Re: mySQL with a quad processor system

Thanks for the response. The CPU's are 450MHz each, and the system has 3 GB RAM. It works pretty nicely. But, they would have to be separate queries to span the 4 CPU's? I guess one thread can't span across them... Thanks, Richard -Original Message- From: Matt W [mailto:[EMAIL

Re: how see list of a table's indexes?

Hi Holly, SHOW INDEX FROM table; or SHOW CREATE TABLE table; are 2 different ways to see indexes -- in different formats. For reference: http://www.mysql.com/doc/en/Show_database_info.html Hope that helps. Matt - Original Message - From: Holly Chamberlain To: [EMAIL PROTECTED]

Re: Problem with LIKE/REGEXP

Hi George, What are you actually trying to match with \017? As far as I know, it's treating the \0 part as a NUL byte and trying to match that. Are you trying to match a NUL byte? Or are you trying to match ASCII 17 or something? http://www.mysql.com/doc/en/String_syntax.html Matt -

Re: mysql memory usage

Hi, In every instance I've seen, MySQL always allocates the amount you set for key_buffer at server startup even if it never comes close to being *used*. (It shouldn't be doing malloc()s or whatever for that on the fly. :-)) Same thing for query_cache_size. Now about join, read, and sort

Re: Table in Memory

Hi, HEAP tables don't currently support TEXT/BLOB columns. My answer about storing the table in memory: don't bother. If you have enough free RAM to use to put the table in memory, the OS will already do it for you after it's accessed. Thus, reading the table (after the first access) should be

Re: MySQL process increasing problem..

Hi, Yes, in top, those are threads not processes, as Gerald already said. Not that it really matters. :-) Your connections in PROCESSLIST are sleeping (e.g. idle). What are the clients? It looks like you may be using persistent connections with a Web app. If you want to get rid of the sleeping

Re: INNODB flush holdup

Hi, I don't know what your previous problem was, but I don't think it affects my answer. :-) This isn't specific to InnoDB. Yes, when you FLUSH TABLES, all new queries wait for that to complete (as indicated by Waiting for table in PROCESSLIST). And the tables can't all be flushed (closed) until

Re: LOAD DATA hangs

Hi Eric, I think for ALTER TABLE ... ENABLE KEYS, myisam_sort_buffer_size is all that matters. Not sure about key_buffer... But myisam_sort_buffer is, AFAIK, only used during Repair by sort (in PROCESSLIST), not Repair with keycache. What did yours say during the 21 hours? It may change from

Re: so long I keep longing

Hi Jon, I know, it's been sooo long! :-( But from what's been said on this list, and since it says To be released soon in the ChangeLog, I would expect it within the next 2 weeks. I'm hoping 4.1.2 or 4.1.3 will be upgraded to Beta status. :-) Matt - Original Message - From: Jon

Re: This is confusing..?

Hi Eric, The Cardinality of the datestamp column is only 76. That means it thinks there's only 76 unique values in all of your rows. In other words, there's a good chance that your WHERE matches more than ~30% of the rows, in which case the index won't be used. If you haven't run OPTIMIZE in

Re: keep leading 000 -solved

Hi Bernd, I think you should be able to use a column type such as field FLOAT(10, 4) ZEROFILL NOT NULL You might want to change the numbers 10 and 4 depending on how many leading/trailing 0s you want. Matt - Original Message - From: Bernd Tannenbaum To: [EMAIL PROTECTED] Sent:

Re: Problem with LIKE/REGEXP

Hi George, - Original Message - From: George Moschovitis To: [EMAIL PROTECTED] Sent: Thursday, November 06, 2003 9:03 AM Subject: RE: Problem with LIKE/REGEXP What are you actually trying to match with \017? As far as I know, it's treating the \0 part as a NUL byte and trying to

  1   2   3   >