MS ACCESS Frontend SLOW

I am running mysql on a gentoo linux box and I am using it to store data for my family. I am interested in migrating them from an access database to mysql while still using access as a frontend. However while doing preliminary testing I realized that searching through access was very slow, e.g. the <ctrl + f> find method takes a few minutes to procure a single match.

The database has two tables, one table containing personal information, with about 500 records and 9 fields, and the second containing sales records, with about 1000 records and 5 fields, linked together by a id number relationship.

Access is set up with two forms, one for entering new personal information, and another form for entering a sale. In the sale form, I link data from the personal information table while hiding the id number because its easier to recognize a name rather than an id number.

What my ideal goal is for them to <ctrl + f>, enter in the last name of the person, and proceeed to enter in a new sale without the process taking 5+ minutes. Is there an alternative to the find command that we can use to speed up the data.

The machine on which the server is running has VERY limited system resouces, a pentium III 550 mhz processor, 256 megs ram, and a 20 gig hdd, but somehow, I know the problem is with the way that I am trying to find data rather than the limited resources of the serving machine.

---

I know this is an 'old chestnut', but I have been trying to add an ODBC connector for MySQL frpm mysql.com. It downloaded and installed correctly, but when I came to configure it, the dialogue looked different from the picture on mysql.com help page. I figured it had just changed and made a best guess of what went in which text box.

Thing is, whenever I try to test the connection, I get an error message;

[MySQL][ODBC 3.51 Driver]Access denied for user: 'dbusername@my fixed ip address' (Using password:YES)

I am assuming it got through to the MySQL database and was denied access because it had my fixed ip address attached, rather than say, localhost? Is it a config problem or have I missed something?

---

I have worked with a few mysql dbs on different servers but i have recently been asked to work with one on nicnames. It seems horribly slow. Working in phpMyAdmin (which i had to install myself) it takes ages when i want to do anything. View the table structure, view the data..etc. Any way i can test the speed so that i can compare it against another server i work with and proove there is a speed issue and take it to nicnames cus it is crazy and is going to affect the speed of the website!!

---

I'm running MySQL 4.1.7-nt on my laptop, and I'm accessing it through ODBC
(MyODBC-3.51.10-x86-win-32bit).

I'm developing a .NET web application, but I don't think that's relevant.

I already had the application running OK on my desktop, and when I created the
same environment on the laptop, retrieving trivial amounts of data (5 rows) from
a table is taking 6 seconds. Updates the same. IN otherwords any table access
takes 5 or six seconds. (Table only has about twenty rows in it).

I can connect to the database on the laptop from the desktop (i.e - application
runs on desktop, retrieves data from laptop - works fine, not noticeably slower
than local on desktop.

If I connect the other way - application on laptop, database on desktop, it runs
just as slowly as locally.

The effect is the same whether I connect to 127.0.0.1 or via the machine name. Code:

---

I'm using the Access/Excel (DAO) driver to connect to (*.csv) files, but every time I change the location of the file, it takes around 30 seconds before the change is made...

Any idea about this? Could it be because of the Access/Excel (DAO) driver? If i'm using (*.csv) files with this driver, is there a conversion that could take that long?

---

I wish to design an entirely new GUI frontend for mysql without the use of php. I would also like to avoid the readily available GUI frontends like EMS mysql manager , rekall etc. I would like to know which programming language is suitable for this purpose. Is it possible to do it with python or perl. If possible which language is better. I wish to run the frontend as a standalone application. Cross platform compatibility is also one of my main concerns. i would be using it mainly in linux, freebsd and windows.
i dont wish to have a webpage frontend for mysql. the frontend has to run as a standalone application.

---

I recently saw OpenOffice's Base:

"BASE enables you to manipulate database data seamlessly within OpenOffice.org. Create and modify tables, forms, queries, and reports, either using your own database or BASE’s own built-in HSQL database engine. BASE offers a choice of using Wizards, Design Views, or SQL Views for beginners, intermediate, and advanced users ... For more advanced requirements, BASE supports many popular databases database natively (Adabas D, ADO, Microsoft Access, MySQL)"

I' am interested to hear anyone's experience with using this as a tool, development tool, or front-end builing tool for MySQL apps.

---

I am using an access (2K) front and back end, but need to create/drop tables in MySQL for some data import/export.

I have the ODBC and connection and it works fine, I can connect via VBA code no problem, but some times I need to create new tables in MySQL, also via VBA/DAO code.

---

you can find some problems below. the problem is about access denied for user...
how can I access the mysql? ......

---

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

---

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

---

What generally would be the reason why all my db driven sites are running slowly or even hanging. I am on braodband speed but just changed hosts.

---

i have this query on a website/webapp that has expanded beyond all expectation. It now takes nearly 30secs to return results from the database

SELECT cl_t.Client_ID, Buyer_1_Title, Buyer_1_Prename,
Buyer_1_Surname, Tel_No, Mob_No, Buyer_2_Title,
Buyer_2_Prename, Buyer_2_Surname, Email_Add,
Price_Max, MAX(activity_t.Date) AS lastcomm
FROM cl_t
INNER JOIN cl_want_t
ON cl_t.Client_ID = cl_want_t.Client_ID
AND Agency_Code ='$agencyloggedincode'
AND Deleted = 'N'
LEFT JOIN activity_t
ON Buy_Sell = 'B'
AND Ref_No = cl_t.Client_ID
WHERE cl_t.Sales_Agent_ID = $agentid
GROUP BY cl_t.Client_ID
ORDER BY $order
The problem is the call to MAX(activity_t.Date) AS lastcomm

activity_t holds all known contact with all known clients and as such is a very large table, the call to search through all of these records and return only the date of the last entry for this client is taking the time. If I remove this from the query I get results in 3 seconds.

I have indexing on activity_t.Date & activity_t.Ref_No

Question, is there a way of doing this quicker within the table I already have, or should I create another table that just holds the last update date for each client, and get the date from this much smaller table.

---

MySQL V 5.0.18 on SUSE 10.1

I'm not a complete *nix noob, but I sure as hell ain't a *nix or MySQL pro.

This is a new installation. Everything screaming fast. Unless it deals w/authentication.

Try to get in w/SQLyog from W2K locally ... intitial connection takes ~20 seconds. Then everything screaming fast.

Web Server (W03) attempts to connect via MyODBC ... same result ... initial connection takes ~20 seconds. Subsequent queries screaming fast.

VNC into the box at any time ... everything fast. (would seem to eliminate network/connection issues)

---

I find this query to be exceptionally slow(around 2.5 seconds), could some tell me why this is so?

MySQL
SELECT st.profile_views,count( DISTINCT p.ID ) news_submitted, count( DISTINCT pv.ID ) news_voted, count( DISTINCT pcom.ID ) news_commented, u.joined, u.weight FROM users u LEFT JOIN posts p ON p.submitted_user_id = u.user_id LEFT JOIN post_votes pv ON pv.user_id = u.user_id LEFT JOIN post_comments pcom ON pcom.user_id = u.user_id LEFT JOIN stats st ON st.user_id=u.user_id WHERE u.user_id='john' GROUP BY u.user_id
I traced the cause to this line
count( DISTINCT p.ID ) news_submitted (from LEFT JOIN posts p ON p.submitted_user_id=u.user_id)
But when i execute something like this

MySQL
SELECT count( DISTINCT p.ID ) news_submitted FROM posts WHERE submitted_user_id='john'
it is quite fast (around 0.03 seconds)
So why does it slow down when i'm joining the above query with 3 other tables ?
Should i use INTEGER for user_id instead of string like 'john'?

---

my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?

---

I've got two tables:
lo_users: nickname|id|...
lo_friends: from|to|...

The following query takes < 0.01 sec:
SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1'

...but if I use it in a subselect, the whole thing takes about 0.54 sec:
SELECT u.nickname FROM (SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1') f LEFT JOIN lo_users u ON u.id = f.userid

What can I do to make the query faster? "from" and "to" are indexed and lo_users.id is the primary key.

---

I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....

---

I need to update 25 * 5000 records, if I do one at the time it takes too
long time, do any one have a good proposal ?

---

I had downloaded a few years back mySQL v3.51 installed but never used it. Now I wanted to convert some B-TREE databases to mySQL and did some testing via ODBC to insert 70,000 records: My results:

MS ACCESS: ~60,000 msecs
MYSQL v3.51 ~18,000 msecs

Impressed with the speed, I went ahead and got the latest MySQL v5.1. Uninstalled the older version, I had nothing there to preserve, so I did a simple new install with MySQL v5.1. I noticed the size of the files and BINEXE increasted by 1,000,000%. Ok, Bulky. Not a problem.

I reran the same ODBC test, and now I got:

MYSQL v5.1: ~450,000 msecs or 7.5 freaking MINUTES!

What the hell happen? Nothing was done. I'm knew to MYSQL. I just installed it with all the defaults. I did choose "developer's machine" for the "optimizer wizard"

I can't redistribute MYSQL v3.51 and force it down people's throats! I have to use what they are using already, if already installed. Not even my current system takes 1 minute to add 70,000 records. Why 7.5 minutes? All it is simple inserts/free statements.

---

I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?

---

I build an application and installed it on many machines. In every machine except two, the program works without problems. On this two, the connection with database is too slow.

I saw the opened doors with 'netstat' and the computer opens about 5 or 6 ports (to the port 3306 of the mysql server) before sucessfuly connect with MySql Database and execute the sql. I don't know what could be happening. I realy need to fix this because the progrm is too slow with this error. Could anyone know what could be happening??

---

Does anybody know why it sometimes takes more than 10 seconds to connect to a database and sometimes it just takes half a millisecond?

---

I had a working web page that queried 20 tables and returned the data in the form of a table ... all of a sudden this stopped returning the results , and on investigation, up to ten tables, the query time is about 0.01 second ... but as the number of tables is increased to fifteen, the query time increases to 60 second .. and then gets too slow with nothing being returned at all.

A few days ago, this was all working fine. There have been no changes to the code on the web page ....
The version of mysql is 4.0.20, the server is a dual 1 MHz Xeon with 512 meg of RAM, running linux.

The tables have up to 15 fields each, and there is only simple text or numerical values in the fields.

Can anyone suggest what might have changed to suddenly slow down the query ?

---

I have a table with the following structure;

CREATE TABLE my_table
(
id_1 int(11) NOT NULL ,
id_2 int(10) NOT NULL ,
stauts tinyint(1) NOT NULL DEFAULT 0 ,
PRIMARY KEY (id_1)
) Engine =InnoDB';


The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query;

UPDATE IGNORE my_table
SET id_1 = 74240, id_2 = 5

I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.

---

I have this one site that slows down all my others because the queries are so massive.

For example one of these queries I use to-do a search by a user's account number. I also get the position he is at on the list, and in order to-do that I need to select ALL the rows.

For example, I filter out the other queries in php.
CODE$num = 1;
$q = mysql_query('SELECT * FROM lists');
while($r = mysql_fetch_assoc($q))
{

   if($r['account']=='accountNum')
   {
         print 'You are at pos. num '.$num.'<br>';
   }
   $num++;
}

---

I just changed to use MySQL few days ago but it was a bad idea. My server now is running very slowly with the database. I'm using Perl5 and DBD::Mysql in my script. The system is Linux9, Apache2.

I looked at these mysql pid and saw a lot of activities (about 400) while there are more 100 users online at this moment and lots of running under a the same pid number.

---

What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?

---

The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong?

I'm also including the dump of the table definitions. This is a cd cataloging database.

Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:

---

i am not using mysql but an unknown database system on a unix box - i have no control over the database but have purchased an odbc driver that seems very 'clunky' after using mysql - this is an sql statement question rather than a mysql tech question. if i run this:

SELECT
MK_01_vehicleRecords.registrationnumber, MK_01_VehicleRecords.vehiclenumber
FROM MK_01_VehicleRecords
WHERE (MK_01_VehicleRecords.vehiclenumber = '36176')

---

I am using MyODBC-3.51.11-2-win on Win 2003 OS. I am not able to see all of the connections in the list under the System DSN tab. The connections that show allow the ASP pages to run at an expected rate.

However, the ones not showing in the list are running extremely slow. If I attpemt to recreate the connection I am told that the connection already exists and asks if I want to replace the existing connection. Wheter I click yes or no the connections do not show and the pages run slowly. How do I get them to show or resolve the issue. The ASP code is the exact same SQL statements and connection strings as the in previous applications.

---

Mysql 4.1.15 on Win2k. Using InnoDB.

Using the mysql administrator gui to create a backup, everything goes
fine, and restores quickly.

Using the command line:

mysqldump %dbname% --single-transaction > %dbname%.sql

creates a file about 15% smaller than the gui produces, and is
EXTREMELY slow to restore. I have tried adding locks, skip opt,
everything. What does the gui use for a command to create this dump?

---

I just got a new dual opteron system, with raid 01, 2gb ram, and fedora
core linux running 2.4.22-smp kernel. For some reason mysql is running
pathetically slow.

Queries that should take 2ms occasionally take up to
20 seconds. It isn't every time, but almost once per page. The problem
usually occurs with queries accessing the large tables (up to 1gb), but
not always. I've tried 2 versions I compiled myself (with flags
suggested in the readme), as well as the version off the mysql website.
All were 4.0.18, and all had the problem.

---

I have a query that is running really slow !!!!
I have joined on Key fields and indexed the tables fully but it is still solw.
--------------------------------------------

select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual
from details d
join poten a
on a.id = d.id
left join recieved p
on d.id = p.id
left join users u
on a.signed = u.userid
where d.paid > '01-Dec-2005'
and d.authorrceived is not null
and d.authorrefused is null
and ((d.payment starting 'E' or
d.payment starting 'e') or
(d.payment starting 'Q' or
d.payment starting 'q' and
p.target = '500'))
order by d.paid, a.signed, d.id

---

I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns...

ID | Title

title is indexed. When i run a query like this
------------------
SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds...
------------------
Even a single like statement just on `index` (heap) takes 3-6 seconds.

Here's the table stats...
----------------------------
Data 397,442 KB
Index 24,639 KB
Total 422,081 KB
----------------------------
Why??

---

I have the long query time set to 15 yet MySQL is still showing results with a query time of 0 in the slow query log.

It says enter time in "seconds" in the MySQL Administrator but did it mean in milliseconds??

---

Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished)

select date from temps where date in (select distinct date from observations where camera like "a")

The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?

---

On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table.

Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.

---

The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records.
UPDATE items SET item_views = item_views + 1 WHERE id = 5897;
is there any way I could speed this up? Some setting I could change to make MySQL faster for this?
The field "id" is the primary table key.

---

I have set long query time to 2 seconds in my cnf file. Therefore all the queries taking more than 2 seconds are logged in the file called mysql-slow.log and the file size is 20 Mb.
Is there anyway to sort this file and find out the queries those are taking the most time so that I can optimize only those ones

---

mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that.

MySQL said: Documentation
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

---

I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form.
My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot?
Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?

---

I have a Python program that parses a file and inserts records into a
database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my
parser runs *really* slow. Writing out to CSV files is fine, but when I
try to insert the same records in a MySQL5 database, it slows to a
crawl. Using MySQL 4.1 seems fine. The data seems to be inserted
correctly, it's just really slow.

---

I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows.
My table is getting kinda slow here is the query i am using
UPDATE nametable SET
sectionname = replace(sectionname,'".$oldsec."','".$sec."'),
categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=&#391;'
where sectionname='".$oldsec."'
and categoryname='".$oldcat."' ;

I am wondering if the same thing is possible with an insert... on duplicate key statement?

I cant seem to get the insert statement to work, but not even sure whether it is appropriate.

With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)

---

I have a text field that's indexed. When I try to sort this column (alphabetical), the first page is pretty fast. However, getting to the end, it slows down a lot, taking about 7 seconds just to show the last page of results.

Is there any way to speed up the sorting?

---

I've never come across this before, a very straight ahead query running extremely slowly...

Table1
Id | Field1 | Field2

Table2
Id | Table1Id | Field1 | Field2

Query....

SQL
SELECT Table1. * FROM Table1 LEFT JOIN Table2 ON Table2.Table1Id = Table1.IdWHERE Table2.Table1Id IS NULL

NOTE: An existing record in Table2.Table1Id is never null, it will always have a record of some description. We're just looking for missing records in Table2.

This query is taking anywhere from 6-20 seconds to run. It's really got me baffled as I thought it should run extremely quickly.

There's about 10,000 records in each table and the query appears to be producing the correct result, just extremely slowly.

---

I have a large table (77,321 Rows) and I'm trying to update it. For some reason, UPDATE takes a long time. Maybe it's my query? I'm doing it through php, maybe that's it? I'm running the program locally using the CLI, and it still takes ages.

Should I not expect it to be as fast as SELECT functions?

$update ="UPDATE `ch_products` SET `products_weight` = '".$IDS[$i][Weight]."' WHERE `xref1` = '".$IDS[$i][ID]."'";

How can I make this faster?

---

I have edited my.ini file to create a file called slow queries. My problem is that when the server starts up i do not get a full list of all slow queries. Is there anything else i need to change? How else can i come about in getting all queries that took a long time to execute? im using mySQL 5.0.9.

---

I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt
command line option to enable the slow query log, started MySQL, and the
option showed up as turned on. Then later I restarted the server, and the
slow query log option went back to being turned off. Is this a Windows
problem in not remembering the service parameter? Has anyone else seen
this?

---