All, there were many emails posted about this on the MyODBC list which,
of course, can be viewed via the archive on the mysql.com site.  For the
most part I will neither quote nor repeat the information from those
emails here.


The conclusion is that MySQL + Merge Tables is perfectly capable of
being a data warehouse and is in fact better in most regards when
compared to other RDMBS.  One example: For similar record counts and
identical index definitions, speed wise MySQL and the "other" rdbms are
about the same when the query is disk bound (e.g. the index is not
cached). MySQL is 5-10x faster than the other rdbms in the cached index
case. There are many other benefits as well.  

(I will not name the other commercial RDBMS out of fear of lawyers, the
DCMA, and posting unauthorized benchmarks. You will have to trust me
that it is a major RDBMS, MySQL is /fast/ comparatively, and that I am
not an idiot at setting up and optimizing databases.)

Using MyODBC-3.51.01.01 works fine to access the MySQL database via MS
Access.  Venu (bless him for all of his help) is going to add
information to the FAQ as such: In the large table case one needs to
check off three options "Return Matching Rows", "Allow BIG Results", and
"Enable Dynamic Cursor".  I needed to do one last truly terrible hack to
MyODBC (patch below) so that if someone tries to open a very long table
(>43M rows in my test case) bad things don't happen as MySQL tries to
copy the results to a temporary table/file. Perhaps there could be a
config for "Max Rows When There Is No Criteria" in MyODBC?

In the next month or two I will try to write an article describing what
I did in more detail so that everyone may benefit.

b.

[bill@badams bill]$ cat myodbchack.patch 
--- ../myodbc-3.51.orig/execute.c       Fri Feb 22 10:55:35 2002
+++ execute.c   Fri Feb 22 10:53:48 2002
@@ -72,7 +72,26 @@
   query=tmp_buffer;
       }
     }
-  }
+  } 
+  /* Terrible hack by Bill Adams */
+  else if( 
+         !my_casecmp(query, "select", 6) &&
+         my_casecmp(query, "where", 5)   &&
+         my_casecmp(query, " limit ", 7) 
+         ){
+    /* Limit the number of rows when someone does a query without
+       any criteria */
+    char *tmp_buffer;
+    uint length=strlen(query);
+    if ((tmp_buffer=my_malloc(length+30,MYF(0))))
+      {
+       memcpy(tmp_buffer,query,length);
+       sprintf(tmp_buffer+length," limit %lu", 20000); /* Arbitrary */
+       if (query != stmt->query)
+         my_free((gptr) query,MYF(0));
+       query=tmp_buffer;
+      }
+  }/* End Terrible Hack */
   pthread_mutex_lock(&stmt->dbc->lock);
   if (check_if_server_is_alive(stmt->dbc) ||
       mysql_query(&stmt->dbc->mysql,query))



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

Reply via email to