MYSQL :: Tuning MySQL To Beat MS Access

MySQL Slow With 4GB Memory - Tuning Required?
I am using MySQL 4.1 on Linux (IBM Open Power Server). The Server has 4GB memory, 380GB hard drive.

I have a Java based application that runs in Tomcat in a Windows environment using SQL Server. As part of a customer project I have got the application to work on Tomcat on Linux with MySQL.

Both databases have a large amount of data in some of the tables, e.g. 500,000+ records in some of the tables. Some of the tables have indexes on them.

There is a specifc part of the application that is slow when running against MySQL (10 seconds to write a record to a table with 500,000+ records in it.) The same bit of functionality running against SQL Server is instance, e.g. 2 secomds max. The code is doing an insert statement. Once the insert has taken place we need to get the ID for the inserted record. This is using SELECT MAX....... The ID column is set as the Primary Key and is indexed. Code:

Optimize And Tuning
i was reading a post from long ago, and suggest for better performance at the mysql server to check the queries adn then make an optimization. i have this db that's eating lots of resources, and usually reaches 90% for the cpu and almost everything is used by the mysql.

i supposse itÂ´s a matter of tuning vars, wich one would you suggest to tackle first, and what others next in an myisam engine

Perfomance Tuning
I have just installed redhat linux 9 which ships with mysql
3.23.56. Mysql has to be setup so that it can use innodb tables,
and data inserts (blobs) should be able to handle at least 8M
at a time. The machine has two P III 933MHz CPU's,
1.128G RAM (512M*2 + 128M), and a 36 Gig hd with 1 Gig swap and
3 equal size ext3 partitions. What would be the recomended
setup for good performance considering that the db will have
about 15 users for 9 hours in a day, and about 10 or so users
throughout the day who wont be conistenly using the db.

My configuration looks like this so far:

++++++++++++++++/etc/mysql.cnf++++++++++++++++++++++++++++++++
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=16M
#InnoDB
innodb_data_file_path = ibdata/ibdata1:2000M:autoextend
innodb_data_home_dir = /var/lib/mysql

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
++++++++++++++++/etc/mysql.cnf++++++++++++++++++++++++++++++++

Server Tuning
I run MySQL version 3.23.41 with RedHat v7.2 ... lately I noticed that our server with 768MB is running constantly at between 95% - 98% memory usage ... my question is, are there any generic tuning tips I can apply to reduce this ... e.g. can I direct MySQL to use disk area instead of main memory.
Although I do not notice the actual database applications running slowly, other applications do not appear to be running as quickly or as stable as previously.

Performance Tuning For Inserts
i am inserting, usually, about 1-5 million records into a 50-350 million (depends on
which DB i'm using) DB. The table has about 75 columns in it. I am getting, on average,
processing time of about 6 seconds per thousand which is unacceptable. I need to
speed this process up. Right now i am building a string in Java with "insert into
<tableName> values(.......);" but I'm sure there's gotta be a faster way. Would it
be faster to write all the additions to a seperate file and then load it using 'LOAD
DATA LOCAL INFILE...'?

InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure.

This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row.

It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query.

Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day.

So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions?

My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).

The Problem Is About Access Denied For User...how Can I Access The Mysql??
you can find some problems below. the problem is about access denied for user...
how can I access the mysql? ......

Get Access Denied Msg..how Can I "reset" Mysql Access
I downloaded the windows installation last night -
mysql-4.0.16-win.zip - unzipped it to c:ms, double clicked on setup.exe and
nothing.... nothing happened at all....

Bad download I though, so tried the same thing with the .zip of
version 3.25.55 I had - the downlaod that I used once before, before my
recent re-format - so I knew that it did work once, but this time, same
problem. The setup.exe just doesn't do anything...

I really want to learn mySQL, but if I can't even install the thing,
it doesn't look good!

Can Access Remotely, Cna't Access Locally (seems Backwards)
I have a database that I am trying to connect to a schema called lportal4_1_2 in mysql 4.1.13a (provided by my hosting company) runnning in fedora. I am trying to get a container to connect to it and was having problems. Since I didn't want to pollute the problem with the container, I wrote a small (hack) program that does the connection.

Here is the problem:
I can connect from my desktop (winxp) using a Java command line application, Mysql Administrator and mysql query browser and everything works fine. When I run the same Java command line app (using the exact same driver) I get the Access denied for user error that follows:
----
java.sql.SQLException: Access denied for user 'lportal'@'inkbrains.com' (using password: YES)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3700)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1203)...........................

Can't Access Created DB's Via PHP, Though Can Access Them Via PhpMyAdmin
I can create databases via mysql-shell and edit them. These then do show
in phpMyAdmin and are editable.

BUT, if I try to access these db's via a normal php-script (i.e.
$ergebnis=mysql__list_dbs($server)), it only shows me one database, the
test-db.
(To show them, I use a for-loop which counts the elements in $ergebnis
from 0 to the number smaller than mysql_num_rows($server) and then prints
the string.)

Warning: Mysql() [function.mysql]: Access Denied For User 'apache'@'localhost' (using
my script is not connecting to my database, i get this error

Warning: mysql() [function.mysql]: Access denied for user 'apache'@'localhost' (using password: NO)

MS Access -> MySQL
I am converting some queries from stored procedures in MS Access to MySQL,
and have hit my first problem. The Access query references another stored
procedure, which is where the problem lies.

I have the following table data: (apologies if the columns aren't aligned
properly... it looks ok in a fixed-width font) ......

MS Access => MySQL
A friend of mine mailed me a access database (.mdb), but I only use Linux
(RH 9.0). Is there anyway that I can open the database in mysql?

Access To Mysql Db
I've just installed Mysql and PHP on IIS 6.0

On IIS I've created a website, where I have imported a php based website which include a folder with a mysql table.
I can't figure how to establish connection between the installed Mysql and the table on the we website.

When I try to access the index.php on the website I get an HTTP-error 403

How do I set the rights in order to establish mysql connection?

MS Access Or VB With Mysql?
Currently my organization is undergoing restructuring of existing databases. Previously, certain applications were made using FoxPro and other applications were using MS Access. What i am planning to do is migrate everything to mysql, using it as backend. Now my problem is regarding the Front End? Which application to use? Visual Basic or MS Access? What would be more advantageous in terms of performance and future support and consume less time for modifications?

I tried to search online but really could not find anything to justify any particular choice. Can anybody plz suggest some idea here or give some reference, which will help me finalize the decision regarding the appropriate front end. Any sort of comparision between MS Access and VB as a front end would help tremendously.

MS Access And MySql
IÂ´m working in an application using Access as FE and MySql as BE.

When I add some registers to the linked table, OK.

Problem: When I try to add a lot of registers (700.000!) it doesÂ´nt work and the msg is that the computer doesÂ´nt have memory resources to do the job.

MS Access To MySQL
I am upgrading a websites database from Access to MySQL. the tables work and so do many of the sql statements all be it with a bit of tweaking but I can't find what is wrong with this one:

SELECT First(B.ID) AS FirstOfID, I.Title, Count(*) AS qty FROM B LEFT JOIN I ON B.IID = I.ID WHERE B.UID=1 GROUP BY I.Title ORDER BY I.Title;

Mysql And Access
Is there any type of free or cheap database tool out there that will allow me to create forms/applications, that are stand alone and does not need a browers. Sort of like Oracle/Access.

MS Access -> Mysql
A couple of years ago a guy posted his tool in this newsgroup which was able to import access databases into oracle, mysql,etc. Does anybody still know how the tools name was?

Access To Mysql
I have been using MS Access for some time and want to convert/move to mysql I should like to import the tables rather than re entering all the data can anyone point me in the right direction or where I can find a good intro to MYSQL?

MS Access To MySQL
I am currently building a website and have been storing my data in an MS Access database. However in the forum I have built the page is struggling to open more than 5 records (posts), and can take around 10 - 20 seconds to open a thread with 15 posts.

Ive heard that MySQL is much faster than MS Access and should resolve my problem, is this true? If so how can I go about setting up a secure MySQL database. I'm totally new to MySQL so if its too long to explain do you know any useful tutorials I can use?

Access And MySQL
I can link MS Access (front) with MySQL (back end). However, when I add a new record, the previous added record is signed as Deleted record (view in Access) even I can view this record in MySQL....

Access To MySQL
I have MySQL 5.0. Trying to duplicate a memo field from Access. VBScript/Asp. The line that gives me an error is rsSSLogs.Fields("Comments") = "test" however it errors on the update line. Function not implemented.

It works if the data type is VARCHAR(50), but not LONGTEXT or TEXT and I do not understand why. I want it to be a field that we can type in whatever comments we need to - which appears the appropriate choice would be LONGTEXT.

Can someone help me out? I've spent a day on this freaking single issue. Thanks.

Dim adoCon 'Holds the Database Connection Object
Dim rsSSLogs 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query to query the database

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost;DATABASE=mydb; UID=root; PASSWORD=mypw; OPTION=3;"

Set rsSSLogs = Server.CreateObject("ADODB.Recordset")
Set rsSSLogs.ActiveConnection = adoCon

strSQL = "SELECT Date, Shift, Comments FROM Comments;"

rsSSLogs.CursorType = 2

rsSSLogs.LockType = 3

rsSSLogs.Open strSQL, adoCon

rsSSLogs.AddNew
rsSSLogs.Fields("Date") = "test"
rsSSLogs.Fields("Shift") = 2
rsSSLogs.Fields("Comments") = "test"

rsSSLogs.Update

Set rsSSLogs = Nothing
Set adoCon = Nothing

Mysql Access
i have a problem similar to others in this group but this is a little bit
different. im running mysql on my yellowdog linux server, but for some
reason whenever i try to connect, it gives this error:
[root@powerpig root]# telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
NHost 'localhost.localdomain' is not allowed to connect to this MySQL
serverConnection closed by foreign host.
[root@powerpig root]# .

MS Access Db To MySQL Db
I tried converting a MS Access table (made from an Excel spreadsheet) to a MySQL table in the built-in 'mysql' database, but when I look at the table in MySQL, it only shows the first 1000 rows of my table. There are supposed to be 2800+ rows. I can't believe I'm exceeding any memory issues here - is there a better way to turn my Excel spreadsheet into a MySQL table without losing rows? Or is there an easy way to just add the rows to the table in mysql? I tried the 'insert' button, but it places the new row at the top and I need to tack it all at the bottom.

Access To Mysql
I have been using MS Access for some time and want to convert/move to
mysql

I should like to import the tables rather than re entering all the data

can anyone point me in the right direction or where I can find a good
intro to MYSQL?

MS Access SQL To MYSQL
here's the situation... i used to use access but now i use php with mysql...
i need to 'convert' this to php... its too difficult for me...
here it is:

SELECT Emissor, Base, Sum base*retorno+kms*0,3 as total, Sum base as total_base, Sum kms as total_kms, Sum kms*0,3 as value_kms, count NGuia as number, sum Tespera as Total_espera FROM baterist_guias GROUP BY Emissor, Tservico;

and by the way can you tell me how to retrieve the values that are calculated in this command? like total_kms or total_base...

Access To MySQL
I just spoke with a friend that says he always designs databases in access because it is easy and then converts to MYSQL. Is this common practice? Advantages? Disadvantages?

MySQL And Access
anyone got any ideas how i can bridge a link between a mySQL database and Access so that the user can easily access it

Access Mysql
I've been having weird issues with one of my tables - some records are disappearing - how can I check if my db has been violated or accessed by someone else

Access MySQL
I just installed MySQL to my windows 2003 server. How do I connect to this MySQL server from another machine that is on the same network?

MySQL, Access
i have a central mysql database that is accesed by multiple clients
running MS Access with ODBC.
This works fine if Access has a permanent connection to mysql.
But now, i want MS Access to operate offline (e.g. on notebook in the
field).
I tried to create a local replica of the ODBC database, but the local
replica still requires an ODBC connection to the running mysql server.
I do not know why.
What is teh best way to realize synchronization between a local and an
odbc database using MS Access ?

Mysql Access
My computer crashed yesterday (of course I backed my data) So I'm in the process of getting things back up and running. The problem I'm having is with mysql. My site is currently offline. When I try to access phpmyadmin I get wrong username/password. When I access a survey program that I wrap in mambo I get: Error
Error connecting to database: Client does not support authentication protocol requested by server; consider upgrading MySQL client
How do I get access to change my username and passwd in mysql?

MySQL Access
I know this problem has risen before but I can't seem to figure this out.
Ok i have a service provider where my mySQL database is being held. Everything is uploaded and ready to go. When i try to access the database via my ASP code i get the following error message:

Client does not support authentication protocol requested by server; consider upgrading MySQL client

I've heard to use the Password/ Old password functions, but who has to do that, me or the service provider? When i try to use the Command prompt from the MySQL administrator it won't let me access the command prompt.

MySQL And ACCESS
I have a database I set up using MySQL. In my main table I have the Primary Key field set up as autoincrement and this field is then used as a foreign key in another table there is a one to many relationship. I am working with ACCESS as a front end, I am having problems with the subform, the feids go in fine on the main form but once I get to the subform all the fields turn to #deleted# I think it has something to do with the auto number field. Does anyone know of this and more importantly know the solution?

Question Regarding MySQL Interface Options And MySQL Vs MS Access
First, there are many applications available for interacting with MySQL. One of the things that frustrated me at first was the CLI. I know that it's the foundation and understanding syntax is critical to making it all work in the end, but when dealing with large datasets, I feel that a GUI makes life easier at times.

So, I started out with phpMyAdmin, seems to be 'the' geek-preferred application. I also ran into a few shareware ones here and there as well as Navicat, and I recently learned that there are 2 apps from MySQL.com for administrating the DB and creating queries.

In your experience, what seems to be the best MySQL interface application? At the moment I couldn't say *exactly* what I'm looking for, but more-or-less something that will ease the headache of creating queries and managing users, tables, etc.

Question #2:

I have been hired to be a Web Developer, Network Admin, and Database Administrator all-in-one. We have are a retail company selling peuter goods and have a product line-up of about 100 items. The President wants a robust database to deal with everything. Right now we use MS Excel for our needs. He says it's becoming cumbersome and wants to move on. I suggested MS Access because that's exactly what it's built for, companies that have been running in Excel and want more robust control over what they're doing. But the president keeps hearing this 'MySQL' buzzword and wants a MySQL database. Fine with me, I just know Access better, that's all.

I've been cramming PHP & MySQL down as fast as I can and am starting to feel more familiar with both technologies. At the moment, I feel MySQL to be more of a headache (my fault I'm sure, I just don't know it yet) But For Example:

To properly normalize a DB, I sometimes have to break a table into 3 tables. The first and second are no problem, but the third 'association' table is annoying (at least doing it CLI or phpMyAdmin). See, Access, you just make 2 tables and graphically set associations very easily.

So, from what you know of MySQL, do you think it would be still superior to Access seeing as we want to integrate it into osCommerce eventually and possibly some of their user contributions?

Mysql Access Problem
I am trying to restore into DB and I am getting the following. Anyone know why I cant restore?

Linking Access To MySQL
Is there a way to link my Access database on my home computer to the MySQL database on my remote website, so that when I change something in Access, it changes it on the remote MySQL database automatically?

MYSQL Access Speeds
When developing complex systems using PHP and MySQL what technqieus do you suggest to quicken up the access speeds to MySQL?

Here are a few that I already know and just wondered if there are any missing?
INDEXES
Only search relevant columns not *
Have good joins and where clauses
Use distinct whenever applicable

Granting Access To Mysql
I have two networked WinXp machines. let's say machine called 'father' has java and mysql installed now I need 'son' machine to have permission to access, from a java app, mysql in father machine.

How can i grant the accesses?

Mysql Access In Dos Mode
(1) I am using XP.
(2) I installed mysql, apache, and PHP a few days ago.
(3) there is phpMyAdmin folder in "htdocs" folder of my Apache.

I can't connect to mysql in dos mode.

I entered "mysql" in (C:/mysql/bin/).

Then, It says "ERROR1045: access denied for user 'ODBC@127.0.0.1'<using password: NO>"

(Q1)what does that mean "ODBC" in your guess?

(Q2)what is "127.0.01"?
It seems like "IP". but my IP was different number as I checked.

So, I enter "mysql -uroot -p".
then, It ask "password".

Because I created a new user and the password with the phpAdmin tool,
I entered the password.

It says "ERROR1045: Access denied for user 'root@127.0.0.1'<using password: YES>"

(Q)Do you see any problems in my approach to learning mysql and phpAdmin?

(Q) why does it deny my access to it?

(Q) what should I do for the access?

Conversion Of Access To MySQL
ok i know it can be done and i know there are programs out there but does anyone know of any FREE ones? i really need just 1 database converted

Switching From Ms Access To MySql
1. Is Mysql secure than MsAccess (there are softwares avilable to
retrieve Ms Office Password)

2. Which 3rd Party tool Would You recommend for Administration,Query
creation.... Etc

3.Any tools for setup Mysql database to ship with ApplicationProgram
(Access need not be installed in a Windows machine)

4.Any known problems in a VB6/VB.Net - Mysql Combinations

Converter: MySQL To Access?
I'm a novice MySQL user, and need a recommendation on what FREE program i could use to convert a MySQL DB to MS Access. I'm using Bullzip's Access to MySQL prog to convert that way, but need a prog to go the other way as well.

I would just use a direct connecting feature, but my access to the internet is sporadic right now (I'm in Tanzania, where home internet access is about 1/3 of my wee volunteer salary!), so editing while on-line is not a good solution.

Moving From MS Access To MySQL
I have a database application written in asp that works with an ms access database.

I am moving the application to a server that supports asp but not access. It has mysql available.

I have used a converter programme to create a mysql dump file which I have checked I can use to create a new mysql copy of the original access database. So far, so good.

I understand that changing the connection file from access to mysql working is not difficult but I cannot find a suitable tutorial to explain this or how/where/if I need to edit my existing asp files.

Converted From Access DB To MySQL DB
I have recently converted my access dB to a mySQL dB. I can edit the new dB with Mysql-Front and PHP MyAdmin perfectly. My ASP application that uses the dB connects to the new dB fine and reads out the data into a Form. The problem occurs when I try to store any data I manually change, back into the dB. When I try this, I get:

==============================

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/storedata.asp, line 180

==============================

I can loose this error message by going through 'storedata.asp' rem'ing out the offending lines of data. Some data it is happy with and some it isn't. When I get to the bottom of the data where the 'ors.update' line is, I now get: Code:

MySQL 3.23 Windows Access
I am trying to set up an Invision Board forum on my windows 2003 RC2 server. I have already set up and configured PHP4. I am now in the process of setting up mySQL. I have never used it, so you can imagine how confused I feel right now.

I installed the 3.23 windows version on C:mySQL
I do not know how to access it, nor really how to use it.

The invision board is asking me to input

SQL Database Name
SQL Username
SQL Password

How do I createaccess this information.
Thank you for your help.

BTW: I recive this error while attempting to connect to my sql via cmd.exe

"ERROR 2003: Cant connect to my SQL server on Local Host (10061)"

I have two firewalls, XP's built in one and Norton Internet Security. Could this be the reason.