mysql Number of SQL Queries

views:

answers:

mysql Number of SQL Queries

Hi
I just converted an Access Db to mysql (using Access as frontend, mysql as backend)
It is a simple, 4 table database
I have a Master table, and 3 linked tables to it So an Access form displays has data from:

Master table (mainform)
- Details1 table (subform)
- Details2 table (subform)
- Details3 table (subform)

The master table will always be one row, however all the linked tables ("details" tables) can have any number of records, usually around 10-30 rows in each detail table per master record.

Everything runs well, however when checking Mysql Administrator Health>Connection Health>Number of SQL Queries, the number of queries jump to 10 (queries) everytime I move between Master record pages.

I am running this on my own laptop, and I am worried this will become a problem (performance) when I put 100+ users in the work server all working at once.

Could anyone advise if this high "number of queries" reported by Mysql Admin will be a problem?
What number is considered "dangerous" for performance purposes?
The idea is to have a fast running system, so I would like to avoid too many queries to the database.
I also dont understand why (example) it displays 7 queries when there are only 4 tables in total..with only one row per table being dislayed

ANy ideas/comments will be appreciated
Can there something be changed in Access front end to make the number of queries lower ?

thanks so much

Those 10 queries probably don't take a long time, and they are very likely sequential. I doubt there will be a problem with 100 users since they won't all be running the queries at once. Even then, mysql can handle quite a load.

I'm not sure what is going on inside Access. "Queries" can be just about anything (i.e. meta data), not just queries for records from the table. For example, getting the total number of records in a table to display something like "showing 23 of 1,000". If access is doing this for each table, that's an extra 4 queries right there, leaving only 3 to get actual data to display.

Brent Baisley 2010-07-10 21:38:55

exactly, the 10+ queries dont take long, however I am afraid it is running fast because I am running the whole things locally, just one user. You are possibly right about Access doing something to show "showing 23 of 1000" and perhaps this adds to more queries... If someone knows how to get rid of these "unnecessary" additional queries it will be great

griseldas 2010-07-10 22:17:16

If you are afraid, you gotta test it. Noone here can give an answer as we don't know the data you have, the server you have, how you've tuned your mysql instance , the queries run, etc. Be sure to run `EXPLAIN` on the queries that are done (you can turn on query logging on the mysql server), so you can add the proper indexes to your tables based on the queries run. With proper indexing, mysql can handle a substantial load though.

nos 2010-07-10 23:36:11

ok, I will do some heavy testing without any further development. I'll hate to waste a couple of months on finishing the complete application only to find out it can only handle a few users at the same time...thanks for your input

griseldas 2010-07-11 10:10:31

mysql can easily handle a thousand connections and running a few hundred queries at once. Turn on query caching in mysql and the same queries will be fast and nearly free as long as the data doesn't change.

Brent Baisley 2010-07-11 13:40:13

It's hard to be sure because it depends on a lot of things like server's memory, cpu and complexity of the queries but... Supposing the queries for the subforms are directly linked to the master table (with an indexed id field) and do not need to join with other tables (as you have only 4 tables), I think you're ok to run without problems as the number of queries is not too high.

As an example, some years ago I had an old machine (Athlon XP1600 with only 512MB or 1GGB RAM) running mysql and serving files for 20 users. Most of the queries were small Stored Procedures using mainly 20 tables but returning a lot of rows (usualy around 2000 for the most used query). Everything was fast. This old system ran 14 millions queries in 2 months (average > 700 per minute) so I think you will be OK.

Anyways, if you have a way to do a partial test it would be the best option. You could use a small script querying the database in a loop on several machines for example.

laurent-rpnet 2010-07-11 01:10:40

thanks for your comments on your own experience. I do have a much better machine than the old one you mentioned, so if you had >700 queries per minute (I would assume in my case will it be just a bit higher than that but with a much better machine), I can only assume it should do ok. Nevertheless I will run testings on several machines with some script to see how it performs. The thing is that I don't want to waste a huge time developing the application now only to find out that it can only handle a few users at once, hence my question.

griseldas 2010-07-11 10:09:00

yes, today's machines are a lot faster and I'm almost sure you won't have problems. Anyways, if you're converting from Access the result will be a LOT faster than it was as from my experience with Access (2003), its performance degrades very quickly when you add more than 3 or 4 users querying the database. This doesn't happen with mysql.

laurent-rpnet 2010-07-11 15:03:01

ansaurus

tags:

views:

answers:

mysql Number of SQL Queries

related questions