Re: Export of text fields from Access2000
Intelligent Converters has a product that's pretty inexpensive, about $35 I believe. It pretty much works as advertised, it was much cheaper than spending any time trying to roll my own script. We recently got an Excel spreadsheet that we needed to import, and after a few minutes of playing around trying to import it, I just bought their Excel-to-MySQL program. Including the time it took to buy the software, it took less than 15 minutes to import. The hardest part was finding their website, www.convert-in.com Tac - Original Message - From: "Greg Knaddison" <[EMAIL PROTECTED]> To: "Ivo Dvorak" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, October 14, 2002 11:34 AM Subject: RE: Export of text fields from Access2000 If you create the tables beforehand in MySQL and then export the data into the already created table, that should allow you finer control of your data types. To save time you could export the table from Access, then truncate it to clear out the data, and then change the data types as necessary. I don't have experience with your scenario specifically, but I have had similar problems with Excel, Access, SQLServer, and Oracle. Data types between applications are a big problem and in some of my problems I found Microsoft Knowledge Base articles stating "this is a known bug, we're not going to do anything about it." See Troubleshooting in Microsoft KB article Q321686. Greg -Original Message- From: Ivo Dvorak [mailto:[EMAIL PROTECTED]] Sent: Friday, October 11, 2002 6:18 AM To: [EMAIL PROTECTED] Subject: Export of text fields from Access2000 Hello, I export a table from Access 2000 on WinXP to MySQL using the MyODBC 3.51.04 and text fields are exported as text fields from Access instaed of varchar as it should logically be (MEMO is the Access equivalent for Mysql text type). I have Jet SP6 and latest MDAC installed. Is there a workaround from this? PLEASE send message to [EMAIL PROTECTED] if you know what to do. Thanks. Ivo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.03 and merge tables: bug with BETWEEN
We're having a problem with a huge merge table and BETWEEN. The same query in MySQL 3, using an identical (binary) copy of the database, takes a fraction of a second, in MySQL 4.03 it runs without stopping. The query is a simple "Select * from my_merge_table where my_code BETWEEN '2026676653' AND '2026676655'" If I run a simple script that loops through the invidiual tables of the merge table, using the same query (with each table), the response is very fast. And running the query when the operator is '=' instead of BETWEEN, everything is fine, e.g. Select * from my_merge_table where my_code = '2026676653' Expanding out the BETWEEN still causes the query to run forever: Select * from my_merge_table where my_code >= '2026676653' AND my_code <= '2026676655' Another bug that's in MySQL 3 (that I haven't checked in MySQL 4) is trying to use a merge table where one of the tables has no rows. When we create a merge table, we go through each table first and check to make sure it has something in it, otherwise, the merge table is created ok but queries against it return no rows. Finally, does anyone know if you can now use fulltext indexes with a merge table? We're not yet (since it's not supported), but I did see that on the to-do list. Thanks! Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: slow query in MySQL 4
30.328 1 select count(*) as C from articles_category c inner join db_news.articles a using (article_id) inner join media m using (media_id) where 1 Query Explanation table type possible_keys key key_len ref rows Extra c index PRIMARY PRIMARY 841551 Using index a eq_ref PRIMARY,media_id PRIMARY 4 db_news.c.article_id 1 m eq_ref PRIMARY PRIMARY 4 db_news.a.media_id 1 Using index Here are the tables. I think the problem present with just 2 tables as well, just a simple inner join on two tables with a primary key and an indexed foreign key. This seems like a pretty basic query, but I must be doing something wrong because 30 seconds to do a count can't be right. Thx, Tac --- # # Table structure for table `articles_category` # CREATE TABLE articles_category ( article_id int(11) NOT NULL default '0', category_id int(11) NOT NULL default '0', PRIMARY KEY (article_id,category_id), KEY category_id (category_id) ) TYPE=MyISAM PACK_KEYS=1; # # Table structure for table `articles` # CREATE TABLE articles ( article_id int(11) NOT NULL auto_increment, : (other stuff) site_id int(11) NOT NULL default '0', PRIMARY KEY (article_id), KEY edition (edition_id), KEY source_date (source_date), KEY site_id (site_id), KEY media_id (media_id), FULLTEXT KEY headline (headline,subhead) ) TYPE=MyISAM PACK_KEYS=1; # # Table structure for table `media` # CREATE TABLE media ( media_id int(11) NOT NULL auto_increment, : (other stuff) PRIMARY KEY (media_id) ) TYPE=MyISAM PACK_KEYS=1; - Original Message - From: "Egor Egorov" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, August 17, 2002 11:13 AM Subject: Re: slow query in MySQL 4 > Tac, > Saturday, August 17, 2002, 12:57:24 AM, you wrote: > > T> A simple query in MySQL 4 that gets a count from 3 inner joined tables: > > T> select count(*) as C from quotes q inner join articles a using (article_id) > T> inner join media m using (media_id) > > T> article_id and media_id are int(11), and are the primary keys for their > T> respective tables. When used as foreign keys, they are also int(11), and > T> are indexed. It's taking about 10 seconds to return the count, ~3000, from > T> tables that aren't all that big (quotes: 3000, articles: 100,000; media: > T> 1500). > > T> I plan to use the new MySQL 4 row count feature soon, but the code I'm > T> working on needs to work on both MySQL 3 and 4 for now. > > T> I believe that the query works significantly faster on MySQL 3. > > T> Any ideas? > > Could you show the output of EXPLAIN SELECT? > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow query in MySQL 4
A simple query in MySQL 4 that gets a count from 3 inner joined tables: select count(*) as C from quotes q inner join articles a using (article_id) inner join media m using (media_id) article_id and media_id are int(11), and are the primary keys for their respective tables. When used as foreign keys, they are also int(11), and are indexed. It's taking about 10 seconds to return the count, ~3000, from tables that aren't all that big (quotes: 3000, articles: 100,000; media: 1500). I plan to use the new MySQL 4 row count feature soon, but the code I'm working on needs to work on both MySQL 3 and 4 for now. I believe that the query works significantly faster on MySQL 3. Any ideas? Thx, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Read-Only MySQL
Microweb provides exactly that. I haven't used it, but I did research on comparing it with running Apache from a CD-ROM. Currently I'm leaning toward the Apache implementation, because PHP will run as a dll, rather than through CGI. But if we need a database, I'll probably go with the Microweb solution (though the Apache version is free). Microweb is pretty clever with MySQL -- if it's read-only, it'll run from the CD, otherwise, it'll copy the tables to a temporarly location on the user's harddrive. I'm not sure how licensing is handled, though. Tac >From http://www.indigostar.com/microweb.htm: MicroWeb allows you to create a working web site on a CD-ROM. Using a web browser, a user can run CGI programs as well as view html files on the CD-ROM. To view the website the end user only has to run the microweb.exe program on the CD-ROM, there is nothing to configure. This will automatically start a web browser for viewing the website. MicroWeb provides support for MySQL, it will automatically start a MySQL server if one is included on the CD. - Original Message - From: "Colin Faber" <[EMAIL PROTECTED]> To: "Gerald R. Jensen" <[EMAIL PROTECTED]> Cc: "Kristian Koehntopp" <[EMAIL PROTECTED]>; "MySQL List" <[EMAIL PROTECTED]> Sent: Tuesday, July 09, 2002 8:20 PM Subject: Re: Read-Only MySQL > Like Kristian says, > > I don't see why you couldn't do something like that, Though you may > want to setup mysql to have a very large amount of cache to pool from > this way you can speed performance of it. > > > > "Gerald R. Jensen" wrote: > > > > Kristian: > > > > Possible? I suppose, but not very practical. It would be > > ssllooww, you'd need to supply an application to permit > > access to the data, and how would you handle licensing? > > > > Gerald Jensen > > > > - Original Message - > > From: "Kristian Koehntopp" <[EMAIL PROTECTED]> > > To: "MySQL List" <[EMAIL PROTECTED]> > > Sent: Tuesday, July 09, 2002 6:48 AM > > Subject: Read-Only MySQL > > > > I want to create a CD-ROM which contains a copy of MySQL (for > > Windows) and a set of MySQL databases. Is it possible to set up > > a read-only MySQL, that is, a MySQL running from a r/o media? > > > > Kristian > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- > Colin Faber > (303) 736-5160 > fpsn.net, Inc. > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Slow Query: matching criteria AND ordering by primary key
I have a table with a "state" field, and often I want to get only records matching those states, ordered. This simple query Select * from my_table where state='NJ' order by table_id DESC is relatively slow because of the order by clause (where there are a lot of matching records). I've tried everything I can think of to speed this up -- indexing state and ID together, extracting the records (just the table_id's) matching the state into a temporary table and then inner joining it back with the original table, etc. EXPLAIN shows that it has to scan through the entire result set to order it, but in the case where there are lots of matching records (and the records themselves are large, with text blobs), it's very slow. It's fast without the order by. Basically, I want a super-fast way to say "Get me the most recently stored x records matching this criteria". Any suggestions on speeding this up? (Every day I look on mysql.com to see if 4.02 is out, because this issue will largely go away when query caching is available, and I don't want to implement that logic in my code now.) TIA, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Recreating an MS-SQL database in MySQL: redux
There are commercial packages that do the conversion quickly and easily. The best one is obscenely expensive (the price isn't on the website, you have to write and request a quote, and a sales rep get back. I think it was around $3000. I don't remember the name of the software, because it was so expensive. But the demo worked perfectly). The solution I used, which was a bit more work, was to use an Access-to-MySQL program, which was about $39 ($75, I think for both directions). I export the SQL-Server to Access, then use Access-to-MySQL. We need to do this monthly for a newsletter (ODBC over the network drags the newsletter delivery out from 24 hours to 56 hours!), and it works fine. Unfortunately, it doesn't preserve indices, so they need to be recreated by hand. The company with the low-cost converters is Intelligent Converters. Of course, you're going to lose some of the constraints just using MySQL, but the converter may make your life a bit simpler. Or, if you have the $3k, do a google search for SQL MySQL converter Tac - Original Message - From: "Christopher Bort" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Tuesday, July 02, 2002 1:59 PM Subject: Recreating an MS-SQL database in MySQL: redux > No response from the first post, so here goes for one more try... > > My employer recently acquired an existing web site that uses a MicroSoft > SQL database and I've been directed to get it running on an in-house > server. As we don't have any Windows servers, I need to recreate the > database in MySQL. The site's developer has supplied me with the schema of > the MS-SQL database and I'm hoping to be able to turn it into a file that I > can run in batch mode through the mysql client to create the database > structure. I'm relatively new to SQL in general and MySQL specifically, so > I'm a little over my head at this point. I will, I'm sure, be learning all > I need to know over time, but I need to get this one project going now and > I would appreciate some guidance to at least tell me if I'm headed in the > right direction. > > Anyway, the MS-SQL schema file consists of CREATE TABLE commands for each > table, followed by a series of ALTER TABLE commands. I've been able to get > the CREATE TABLE commands to run by changing, e.g.: > > CREATE TABLE [dbo].[clients] ( > [login] [varchar] (50) NOT NULL , > [password] [varchar] (50) NULL > ) ON [PRIMARY] > GO > > to: > > CREATE TABLE clients ( > login VARCHAR(50) NOT NULL , > password VARCHAR(50) NULL > ); > > So far, so good. There are a couple of column types that I've had to guess > at, but I think I've found reasonable equivalents ([money] becomes > DECIMAL(6,2) and [phone] becomes VARCHAR(20) ). > > I'm getting stuck on the ALTER TABLE commands, though. I'm afraid I don't > know quite enough MySQL (or SQL in general) to confidently figure out which > MS-SQL bits are server-specific and can be ignored, and which need to be > changed into equivalent MySQL commands. If I change: > > ALTER TABLE [dbo].[dining_cat] WITH NOCHECK ADD > CONSTRAINT [PK__dining_cat__07C12930] PRIMARY KEY CLUSTERED > ( > [cat_id] > ) WITH FILLFACTOR = 90 ON [PRIMARY] > GO > > to: > > ALTER TABLE dining_cat ADD > CONSTRAINT PK__dining_cat__07C12930 PRIMARY KEY CLUSTERED > ( > cat_id > ); > > it works, although I'm not sure what, if anything, is being lost by getting > rid of 'WITH NOCHECK' and 'WITH FILLFACTOR = 90'. Then there are commands > that look like: > > ALTER TABLE [dbo].[dining_loc] WITH NOCHECK ADD > CONSTRAINT [DF_dining_loc_loc_venue] DEFAULT (0) FOR [loc_venue], > CONSTRAINT [PK_dining_loc] PRIMARY KEY NONCLUSTERED > ( > [loc_id] > ) WITH FILLFACTOR = 90 ON [PRIMARY] > GO > > MySQL gives an error 1064 at 'DEFAULT (0) FOR loc_venue' and I'm not sure > what to do with it. If I knew what the intended effect is, I'm sure I could > figure out the equivalent MySQL syntax. At this point, though, it's still a > bit opaque to me. Any gentle nudge in the right direction would be greatly > appreciated. > > TIA! > > -- >Christopher Bort | [EMAIL PROTECTED] > Webmaster, Global Homes | [EMAIL PROTECTED] > <http://www.globalhomes.com/> > > - > Before posting, please check: >http://www.mysql.com/manual.ph
large table with text field -- performance?
I have a large (>40 million rows) table that currently consists of a few integers and a varchar. I need to add a memo (text > 255 chars) field to about 2% of the rows. Originally I was going to create another table with a key and the memo field, but this method is complicating the programming logic. The easiest solution would be to add the memo field right to this table, and use it only when needed. Does the presence of a memo fields affect the performance a lot? Are memos stored with the records themselves, or in a separate part of the table data (I believe Access and SQL-Server stores the memos separately, which can kill performance). Finally, in this case should I allow Null's and use them for the 98% empty fields? Thx, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Seaching Keywords column
MySQL 3.23 has full-text search built-in, and it's made for exactly this situation. You'll need to create a index of type FULLTEXT: http://www.mysql.com/doc/M/y/MySQL_full-text_search.html Of course, the other way to do this is to create another table with the keysword in it, and a pointer back to the original record, then do a simple join. This also gets rid of the 3-term limit. To answer your question, though, when I have to create flat tables like that, I use a memo field and tell people that the terms need to be entered one per line. This gets rid of the punctation and multiple word problems, it looks very clear, and it's fairly easy to convert to a more properly designed structure. Anyway, check out the full-text search. It's not as good a full-blown search engine, but it beats the heck out of LIKE %$searchstring% queries! Tac - Original Message - From: "MikeBlezien" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, March 10, 2001 1:23 PM Subject: Seaching Keywords column Hello All, We have a table where keywords will be entered into a column (searchwords), up to 3 unique keywords. Then this column will be used in a keywords search tied to a Category(a Category has to be selected). The table will have a category column: category. Basically when a search is done, it will be something like WHERE category = '$CatSelected' AND searchwords IN '$keyword1' OR searchwords IN '$keyword2'... etc.. or WHERE category = '$CatSelected' AND searchwords LIKE "$keyword1%" OR searchwords LIKE "$keyword1%" etc... Now the problems we seem to have is how we should enter the keywords into the searchwords column. there can be up to 3. Should they be entered as: keyword1,keyword2,keyword3 or keyword1 keyword2 keyword3(as a string). What would be the best way to do this so that we can do a search on the all the keywords entered into the searchwords column. Any suggestion/advise much appreciated. Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
execution time in PHP v. perl
I'm porting some code from perl to php, and have experienced a tremedous slowdown in one particular query, and I'm perplexed as to why. The query is simply "Select * From my_table order by ID desc LIMIT 0,20", to get the last 20 records that were entered. ID is the Primary Key, and it is auto-incremented. The "Order by" is clearly where the slow-down is -- both scripts are very fast without it. I would expect the php version to be faster, because it's compiled into apache, and perl is being run as a CGI script. Regardless, I'm not timing the whole script, just the time it takes to execute the query. It takes several seconds in PHP. Does anyone have any insights as to why? I thought the problem was related to MyISAM (before I realized it was fast in perl), so I've been upgrading the tables (btw, thanks to those that answered my question as to how). I'm using mysql_pconnect to connect to the database in PHP, and using the DBI module to connect in perl. Ideas? I know this sounds very bizarre -- I've been playing around with it for hours, trying to figure out how PHP could possible be tied to slowing MySQL down. TIA, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: GETTING ROWS
$query="Select DISTINCT RECEIVER from TRANS_TBL order by TRANS_ID DESC LIMIT 10" ; - Original Message - From: "Randy Johnson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, March 10, 2001 10:41 AM Subject: GETTING ROWS > Here is a query: > > > $query="Select RECEIVER from TRANS_TBL order by TRANS_ID DESC LIMIT 10" ; > > > How do I change this query to skip rows if it already selected a row with > that particular receiver in it? > > Thanks > > randy > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Converting tables to MyISAM
I've searched the MySQL manual and can't figure out how to convert one (or all) tables to MyISAM. (I'm upgrading a very large database from 3.22 to 3.23). I think there's a way to do it from the ALTER TABLE command, but I can't seem to find any examples. Thx, Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
