Something faster than mysql?

[Gage8]

We have written a database for our school in Access. But as you know Access is very slow with multiple users accessing the same database. We were looking for a server interface like mysql to speed up the process with multiple users. However, since we implimented mysql it seems to be even slower than just with access.

I was just wondering, what else is out there to replace mysql? I know there is Microsoft sql but have never tried it. And are there any free demos of your suggestions that we could test?

Thanks,
Gage

 

[MrChad]

There are a number of factors that contribute to database performance?

1. What is your data model?
2. What types of queries are being executed?
3. How many concurrent users are you handling?
4. What type of hardware are you running the thing on?

I would examine these questions before you overhaul the underlying DBMS. I haven't used MySQL personally, but I know that it should be able to handle a significant user load when running on the proper hardware.

 

[EagleKeeper]

You can get SQL Server demo from MS.

Hoeverever MySQL should be able to handle your needs as well.

It seems as if you may have a implimentation/schem problem that is being inefficient..

Analyze the questions stated above, then assistance may be able to be provided.

 

[Gage8]

1. Not sure exactly what you want. The tables are being linked into access through ODBC.
2. I was testing with one select query would take roughly 15 to seconds to complete and would seem to lock access.
3. Just one just for testing purposes on MySQL.
4. 1 GB DDR Dual AMD 2000+ MP's 10K Quantam drive W2K Server. CPU Load is less than 5%

Might just be the config on MYSQL not sure exactly how to optimize the DB.

 

[EagleKeeper]

Originally posted by: Gage8
1. Not sure exactly what you want. The tables are being linked into access through ODBC.
2. I was testing with one select query would take roughly 15 to seconds to complete and would seem to lock access.
3. Just one just for testing purposes on MySQL.
4. 1 GB DDR Dual AMD 2000+ MP's 10K Quantam drive W2K Server. CPU Load is less than 5%

Might just be the config on MYSQL not sure exactly how to optimize the DB.

#1
Are you using an Access front end into MySql?


How many tables are you using in the DB
How many fields are you searching for.
Is the table indexed?
Number of records in the table.
If more than one table being queries, are the tables linked via indexes?
When was the DB last compressed?

 

[Firus]

what type of select are you doing that takes 15 seconds?
MySql can't really handle nested selects, unless it's the new beta version(i think), but if they are nested, sometimes using joins instead will speed things up a bit...

 

[jonmullen]

yah, care to show us the select and give us some more info on the database, it sounds like some problem in the way it was coded, chances are there is a faster way to do it that wont require a new DBMS.

 

[jonmullen]

Edit: Double post

 

[Gage8]

will post shortly

 

[Gage8]

Yes I am using an access front end to connect to MySQL. Basically we are currently running an Access front end and have an access file with just the tables as a back end. It works fine when just one person is using the db. When you get two users in it is very slow due to locking etc. BTW we are not running on MySQL at all we are still using the access backend and Access frontend. I am just trying to get it to work because now it is becoming an issue where before we usually only had one user but we are now approaching a multi user enviroment so I am trying to stay ahead of the game. 🙂

I have not used MySQL much although we are now using it for our website and which has an asp front end. I am now experimenting with running it internally.

How many tables? There are 26 tables. The tables are indexed although only on the primary key. Perhaps that is something I need to work on. I wrote my own Access VB port that looks at the tables and uses SQL to create tables in MySQL. I believe the Indexes carried over but not entirely sure. I am a little fuzzy on the indexes if someone wants to explain or point to alink to read up that would be great. I normally only index the Primary Key usually an AutoNumber field.

The two tables are Credentials 96 records and Contacts 918 records. The two tables are linked through ContactID in the Credentials to ID in the Contacts table.

DB compressed? Is that the optimize function?

I am using WinMySQLAdmin 1.4. Also using MySQL Control Center .93

"SELECT Credentials.*, Contacts.* FROM Credentials INNER JOIN Contacts ON Credentials.ContactID = Contacts.ID;"

Hmm there it is this is the foundation for a form that allows you to work with Credentials. So I guess it would be pulling 93 records out of 918 Contact records.

Anyway thanks everybody for the interest hopefully you wil all be able to edumacate me. 🙂

 

[MrChad]

Ok I am seriously confused here:

Yes I am using an access front end to connect to MySQL.

we are currently running an Access front end and have an access file with just the tables as a back end. It works fine when just one person is using the db.

BTW we are not running on MySQL at all we are still using the access backend and Access frontend.

I wrote my own Access VB port that looks at the tables and uses SQL to create tables in MySQL.

So which is it? Access frontend to a MySQL backend (via linked tables)? Access frontend and backend?

 

[EagleKeeper]

sounds like currently he is using Access as both a front end and back end.

He wants to move to MySQL and still use Access as the front-end.

DB compressed? Is that the optimize function?

Yes

Which version of Access are you using?

 

[DavEagle]

Hey Gage8 and I work together on this project. Thought I would just join the conversation since I asked him to post and see if anyone could help us out.

Ok we are using Access 2000 and Access 2002 which are pretty interchangable.

Eaglekeeper is right we are currently using Access front and backends but we were trying to move it over to MySQL but it was so slooooowwwww!! I couldn't do it yet.

Edited to add: It has not been optimized or compressed.

 

[jonmullen]

Originally posted by: Gage8
It works fine when just one person is using the db. When you get two users in it is very slow due to locking etc.

Thats something you need to look into...if you now have mysql handling the back in you should not have to lock tables. That is also probally your slow down. The point of locking as I understood it was to avoid messing up your database by having two users trying to write at the same time. And in access this is a problem as you both are just reading and writting to a file. In mysql however you are sending read and write requests and then it takes care of them eliminating the need for locking. Now I dont know if you have your own locking functions built into your front end of if its an access option my I think it would be safe to turn it off with a mysql backend and it should speed things up.