1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

What does query overhead mean?

Discussion in 'General PHP and MySQL Discussions' started by Aarmin, Nov 19, 2012.

  1. Aarmin

    Aarmin Active Member

    I've noticed some add-ons have a statement that they add queries.

    More importantly, how would this affect little me on a shared server? How many queries are too many?

    The two add-ons that I'm currently testing add '0' query overhead, but I would still like to somewhat understand this.

    Keep in mind, I'm clueless about this even after searching the lands of the internet
     
  2. tenants

    tenants Well-Known Member

    A good performing add-on will use the minimum number of queries it can, this can sometimes be 0 (and it's nice when it is). 0 is what Add-on developers should be aiming for, but it's not always possible depending on the nature of the add-on.

    It's also worth noting that some queries are just bad by design (just 1 query can be very slow, and some queries can be very fast).

    If you want to know the overhead of any plug-ins, turn on debug mode (turn this off after use), open your config.php (yourForum/library/config.php) and put this line into it:

    PHP:
    $config['debug'] = true;
    I would do this on a local test environment, since I don't think setting debug mode to true on a real environment is a good idea (if you do, do it for only a short time, since these pages will be picked up by indexing spiders and cached.. )


    At the bottom of the page, you will then see something like this (the values will differ):
    Timing: 0.0208 seconds Memory: 1.491 MB DB Queries: 6


    Check the number of queries for that page with the add-on installed
    Check the number of queries for that page with the add-on turned off

    But more importantly check the time it takes to load the page (since some queries are faster than others) and do this a few times to get an average

    There is no set amount for "too many" but what you think is acceptable.... some developers are better at targeting performance more than others.
     
    Chris D likes this.
  3. Chris D

    Chris D XenForo Developer Staff Member

    I think this is an important aspect. The way I see it, an additional query in itself isn't necessarily a bad thing and although I do my best to avoid them, they are sometimes unavoidable.

    tenants, can you give a good example of a badly designed query that might be too slow?
     
  4. tenants

    tenants Well-Known Member

    Sure... just take a peek through my plugins...
    :D, but hopefully not

    That question is probably better answered by some of the Oracle devs / DBAs here (there are some around)... I've got a few:
    • Joining multiple tables without linking correctly (you could end up with millions of results instead of a few)
    • Not making use of LIMITS / WHERE where you can reduce the number of tables it needs to look through
    • Not using an index when you can... that's fairly bad (indexes are updated at the time of insert and can be time saving when queried)
    • Not using the correct column for indexing (sometimes you need to think about this)
    • "ORDER BY" for a very large table does take time (I worked on a corporate project that had over 20 million entries and they wanted to use quite a few order bys and they wondered why it was taking more than 10 seconds ... silly silly)
    • Using something like "ORDER BY RAND()" can be quite bad for a large table: SELECT * FROM table ORDER BY RAND() LIMIT 1 <<<< ekks. There are lots of ways around this, see here (I was looking at this recently)
    • SELECT * is not always great, if you can, pick out what you need
    • Using larger than needed datatypes (blobs instead of varchars) I've done this recently, but it wasn't a mistake.. and not on a large table used on the front end, this was more of a "will look at correct size when I have time")
    The list is goes on (and probably better answered on a SQL forum by a SQL dev)

    In a related note, but not the the query it's self: not making use of cache isn't a great design
     
    Amin Sabet, bousaid and Chris D like this.

Share This Page