Can mySQL handle it

[leewisener]

I am currently using access to manage a database which has 23 tables containig about 150K records and being accessed by approx 20 at the same time throughout the day.

Access is slowing and I want to port everything over to mysql using access (via odbc) as the front end.

I intend developing something more slick and web based for the front end in time, but I think the move to mysql and away from the jet database would be a key start.

my question is how good mysql really is, will it handle the load much better?

 

[leewisener]

just one other thing, has anyone used navicat for mysql administration? and how would they rate it?

 

[BNPMike]

Few points:

You don't say why you want to change to MySQL. If you have a problem with Access performance (is that 20 simultaneous users?) it would be easiest to just tune it. Mostly applications can be speeded up by improving the code side.

Agreed, Jet is not ideal for 20 users but you can most easily migrate to SQL Server, which can handle huge loads.

If you want to use MySQL (which is as powerful as any database you can buy) then I would look at available ADO/DAO connections to get best performance and to minimise re-coding.

 

[KarveR]

For our business we run a Mysql database with PHP/ASP. The main DB contails 67 tables, the main 2 being 270,000 recods / 2.8gig and 3,020,000 records / 4.75gig.
We currently acess this db with up to 60 simutaneous users, and approx 40 processes which update it directly.

Cant say as we've had any perfomance issuse .

Hardware is all P3 / 800mhz stuff, so its not rocket science.

Oh, and the really nice thing .. the price tag. MSSql server = eeep loads of cash. MySQL/Apache /Redhat9 = freeeeeeeee

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[BNPMike]

"MSSql server = eeep loads of cash"

MS SQL Server is free with Access. It is limited to 5 threads but all that means is other threads are queued, so it could well be capable of supporting the small application mentioned.

 

[leewisener]

As a test I have set up mysql locally as a test and exported all the tables from access to mysql via odbc to retain tables settings.

accessing the tables from access via odbc to mysql is quick, at least as quick as having them on access.

however, accessing forms and reports is taking forever, would the forms need to be rebuilt to make them access faster?

p.s thanks for all the advice.

 

[hos2]

do you have an asp interface for you're access application ???

the userinterface for mysql is made in php for instance.

 

[BNPMike]

"the userinterface for mysql is made in php for instance."

I'm not aware MySQL has any preference for PHP.It will work just fine with Microsoft environments.

 

[hos2]

OK that's new for me that you can take an access form and use that on a mysql database.

never to old to learn

 

[BNPMike]

"OK that's new for me that you can take an access form and use that on a mysql database."

MySQL is only a database - like Jet in Access. Access forms are a feature of the Access front end. Access will connect to all sorts of databases, including MySQL. You might want to move to a web model (PHP is an example) if you wanted people to access the application who didn't have Access on their workstations. For the problem we are considering here, the users have Access, so the line of least resistance is to keep Access as the front end.