mysql performance

vronsky

Hi to the community,
I would like to ask if you have already experienced working on mysql database having tables of more than 100.000 records.

I suppose it depends from the performances of the machin it is installed on, but with a reduced power server
(Penthium4,2400Mhz,512GbRam),it can cause slow performances if multiple users (20-30 at time) are running queries on it?

Thank you

Deany

Well, I think it makes sense that the more connections to the server database the more CPU power get's used.

I work in a shared hosting environment, and my hosts restrict the number of connections allowed to about 30-40 at one time. I think the spec is about the same you make mention. If you have your own server, nice and healthy it might manage more.

On a complicated search, I'm sure the CPU power will get sucked up like 50% with ease. The thing is that not everyone will do a power sucking search at the exact same moment!

I'm not a tech, nor an expert in benchmarking - but other than php and mysql what other option do you have for searching 100,000 records ??

One of my dbs has about 4,000 records each with about 140 fields each (all in one table lol). Performance isn't an issue.

Database design and slick programing might be more important.

🙂

Roger_Ramjet

Don't wory about it. I have a production website on a shared server at our ISP with that sort of record count and am continually surprised at just how fast my pages load, faster then the public pages that have flash in them. And they are complex multi-frame pages selecting and displaying data from 7 or 8 tables at a time.

There are people here who run dbs with amny gigabytes of data and complex queries that run in 1-2 seconds. Read through the manual for the tools you can use to time each query, and the tools you can use to optimise them if they are too slow.

What you probably have not grasped, being newbies is that the db engine does not have to read through all those records to find the ones it wants. Relational databases use indexes to find the record and then grab it in one disk access. Typically a well indexed table only requires 4 disk reads to dind a record whether the are 100 or 1,000,000 in the table.

nemonoman

I agree. I have a fairly huge database. MySQL is never the bottleneck. It is a very fast database.

vronsky

OK... thank you...