SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Enthusiast Monkeyboy's Avatar
    Join Date
    May 2001
    Location
    England, UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a recommended upper limit for MySQL queries in a script?

    What's the recommended upper limit for MySQL queries in a PHP script? At what point, does too many queries start to slow your page down in the browser?

    Is it 5? 10? 20?

    Anyone know?
    Monkeyboy

  2. #2
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's no defined limit I know of but practically - well there's no definate answer - it depends on many factors - the size of the database, the complexity of the query, whether you're using indexes.

    But in my opinion 5 is already too many. I don't like have more than one query on a page for most applications. There's alot you can do with arrays in PHP for example and it may be more effective to pull more data into memory that you need and manipulate it with arrays.

    Anyway - don't think there's a clear answer to your question - depends on many things.

    You might be interested in this thread though, which explains how to show how long a page took to generate.

  3. #3
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It depends on the application.

    I think that for a forum 9 or 10 is a good query amount.
    It is impossible to do it all in one query really.
    The data for users is stored in a different table, the post in a different and possibly the templates.
    If the forum software uses a db based session system there will probably one or two additional queries which delete sessions that have been inactive or update the timestamp.
    Viewing a topic runs one query to update the view count and then the data has to be displayed.
    Just two examples, that 5 aren't really many queries for a forum.

    But as HarryF said, no defined limit.
    Benchmarking is a good idea and I would also suggest to use EXPLAIN <query here>
    That will give you some info wether indexes have been used and other useful information.

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The rule of thumb that I use is this (somewhat complicated but it seems to work well):
    Accomplish what you need to in the least amount of work.

    'Least amount of work' typically refers to database work, or physical/logical I/O.

    For instance, to compute the number of rows in a table you can do:
    PHP Code:
    $res mysql_query"SELECT *
                           FROM table" 
    );

    // Something like this, I think
    $num_rows mysql_num_rows$res ); 
    But that requires a significant amount of I/O, meaning that the MySQL engine will have to perform many disk reads (disk I/O), load it into memory (logical I/O) and throw it to your PHP client (typically over the network so network I/O). Add those together along with in the PHP overhead and you get the total IO for the query.

    If you run this query instead:
    PHP Code:
    $res mysql_query"SELECT COUNT( * ) AS counts
                           FROM table" 
    );

    $num_rows mysql_fetch_array$res ); 
    You'll see the I/O is significantly less. It's a more efficient query which accomplishes your goal (to get the number of rows).

    10 queries on a particular php file may use 100 I/O, whereas 4 on another page may use 500 I/O. Is more queries necessarily worse? No.

    Also remember that queries which change data have another cost, and that's the fact that it locks other records. In MySQL, you're dealing with full table locks. Depending on the RDBMS you can have different locking schemes and blocking factors to deal with, but remember that changing data will lock other queries which try and grab the data. So be careful in your design and account for concurant usage -- if you're changing records 1-100 and you request records 99-101 your request will have to wait until 1-100 are done updating.

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another definition of a word I use a lot -- 'expensive'. As in, deleting many rows is quite expensive.

    Expensive is the relative measure of all the I/O for a particular query. The more total I/O of a query, the more expensive it is. That's why cheap queries are nice to have.

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    Also remember that queries which change data have another cost, and that's the fact that it locks other records. In MySQL, you're dealing with full table locks. Depending on the RDBMS you can have different locking schemes and blocking factors to deal with, but remember that changing data will lock other queries which try and grab the data. So be careful in your design and account for concurant usage -- if you're changing records 1-100 and you request records 99-101 your request will have to wait until 1-100 are done updating.
    regarding the last line: in MySQL (with table-locking MyISAM tables anyway), reads on any rows will have to wait for the update.

    in MySQL, i think multiple threads (clients) can read concurrently from MyISAM tables, even with table level locking. if that's the case, it means table-level locking isn't a problem when you have a "read-only" table. it's on tables with a lot of updates that you can start to have problems because every other query is waiting for the update(s) to finish. am i right?
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  7. #7
    SitePoint Enthusiast Monkeyboy's Avatar
    Join Date
    May 2001
    Location
    England, UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks fellas. I'm redesigning my site at the moment, and I would rarely have more than about 5 queries in any one script.

    My site's strictly read-only, and any input by the user will have to be verified by me first. So any updates to the database will be done by me and nobody else.

    If multiple users are only reading from the database and not altering it, do I still have to lock the tables when I'm updating and then unlock them afterwards?
    Monkeyboy

  8. #8
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think locking is done automatically when you update.

    Well, I sure hope so, cauze I never lock them, and I never read anywhere that I had to lock them when I update.

    -cARL
    Ohai!

  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this can be confused. when we talk about MySQL's table locking, that's what it does internally, to ensure that multiple threads (clients) can't be changing a table's contents at the same time. this differs from you using `LOCK TABLES ...' and `UNLOCK TABLES' queries. since MySQL (with MyISAM tables anyway) doesn't support transactions, there are cases when you may want to use a `LOCK TABLES ...' query if you are doing updates between multiple tables and you don't want data in any of those tables to change until you have updated each. so you'd include those queries between `LOCK TABLES ...' and `UNLOCK TABLES' to get a sort of "transaction."

  10. #10
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting explaination of Table Locking in the manual.

    In MySQL Version 3.23.7 and above, you can insert rows into MyISAM tables at the same time other threads are reading from the table. Note that currently this only works if there are no holes after deleted rows in the table at the time the insert is made. When all holes has been filled with new data, concurrent inserts will automatically be enabled again.

    Table locking enables many threads to read from a table at the same time, but if a thread wants to write to a table, it must first get exclusive access. During the update, all other threads that want to access this particular table will wait until the update is ready.

    As updates on tables normally are considered to be more important than SELECT, all statements that update a table have higher priority than statements that retrieve information from a table. This should ensure that updates are not 'starved' because one issues a lot of heavy queries against a specific table. (You can change this by using LOW_PRIORITY with the statement that does the update or HIGH_PRIORITY with the SELECT statement.)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •