SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I'm creating a script that, fairly often throughout, I need to use a SELECT COUNT() query on two seperate tables - this seems to be slowing the script down quite a bit, and if the script remains this slow, it won't be of much use to me...which would be very bad!

    Is there anyway to combine these two to grab the COUNT() value from both in one query?

  2. #2
    SitePoint Evangelist
    Join Date
    May 2000
    Location
    Canada
    Posts
    533
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    as far as my knowledge goes, there isnt a way .. but then, there might be something new since i last tried to accomplish something like such

    either way, the count() is heavily optimized code, it shouldnt slow a page down by any humanly noticable amount ....
    cogito, ergo sum

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you are using COUNT(*) then try counting on just the primary key column ie, COUNT(PK_COLUMN_NAME)

  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)
    If you are in MySQL execute:
    EXPLAIN
    SELECT ....;

    And see what it says. I haven't used MySQL in a while, but make sure it is using the clustered (sorted) index that you have set up on the table (which is generally the primary_key index).

    If you see the words "TABLE SCAN" in there, then you have problems.

    If you are COUNTing on a column that does NOT have an index, then immediately add one!

    COUNT( * ) should be fine because the optimizer's plans should automatically pick up the clustered (again probably primary key) index that you have set on the table.

    If it doesn't then the optimizer really sucks and you should kick the stupid thing in the head.

    You can try:
    Code:
    SELECT COUNT( t.threadid ),
           COUNT( p.postid   )
      FROM thread t,
           post   p;
    But that will just join the tables and spit out the total number of rows in the result set.

    You probably have some other issues if you need this several times.. Obviously if you're using it in the same file over and over what you want to do is just use the same variable.

    If used in multiple files try and use a global variable to do it or create a denormalized column in one of your tables to store those values.

  5. #5
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Matt: Don't quite understand what you mean by "EXPLAIN SELECT"...

    And yes, I have a primary key in each table, so I can modify it there - I'll give it a try.

    I'm not using it over and over again actually - these two queries are inside of a commonly used script-wide function that spits out what can only be described as survey results.

    As such, to display percentages, I need to grab the total number of votes for any one option as well as the total for that entire survey, and then perform the math - it works like a charm, it's just slow!

  6. #6
    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)
    Originally posted by TWTCommish
    Matt: Don't quite understand what you mean by "EXPLAIN SELECT"...
    EXPLAIN is a MySQL keyword which is used to show you the query plan that the little guy (or gal) who lives in your database decided to use. Basically it should spit out stuff like:
    Type of query is COUNT
    Using index ix_postid
    Index contains all information, base table not used
    yadda yadda yadda

    If you go to www.mysql.com I think you can find an explanation of explain

    Run the EXPLAIN select ... junk with the queries and post the output here.

    Originally posted by TWTCommish
    And yes, I have a primary key in each table, so I can modify it there - I'll give it a try.

    I'm not using it over and over again actually - these two queries are inside of a commonly used script-wide function that spits out what can only be described as survey results.

    As such, to display percentages, I need to grab the total number of votes for any one option as well as the total for that entire survey, and then perform the math - it works like a charm, it's just slow!
    Subqueries would help here. Too bad MySQL doesn't support them yet.
    Last edited by MattR; Feb 10, 2001 at 08:36.

  7. #7
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, here's what I got from "EXPLAIN SELECT COUNT(optionid) FROM options WHERE pid=4":

    Code:
    +---------+------+---------------+------+---------+------+------+------------+
    | table   | type | possible_keys | key  | key_len | ref  | rows | Extra      |
    +---------+------+---------------+------+---------+------+------+------------+
    | options | ALL  | NULL          | NULL |    NULL | NULL |   14 | where used |
    +---------+------+---------------+------+---------+------+------+------------+
    1 row in set (0.00 sec)
    I have no idea what this means, and I'm only hoping the table above will come out properly formatted...

  8. #8
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, and something else: I tried the exact same query without "EXPLAIN" in the front and it returned 3, if that matters at all - I guess I was expecting the "14" there to be the number of rows returned, but I knew it was too high...

  9. #9
    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)
    http://www.mysql.com/doc/E/X/EXPLAIN.html

    the "ALL" in the type means it is doing a table scan, possible_keys = null means there are no indexes it can use to help optimize it.

    Bad news.

    Add an index on options( pid ) and then re-run the EXPLAIN.

    p.s.
    That is so ugly. Why not give a nice output explaining what it is doing so you don't have to look it up? "adds that to my 'Odd things in MySQL' list"

  10. #10
    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)
    You also should run it for the other COUNT query you are doing.

    heh..
    "COUNT query" sounds like a vampire who programs in SQL..
    Last edited by MattR; Feb 10, 2001 at 09:00.

  11. #11
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's a good article on indexes. It uses postgreSQL but its applicable to all RDBMS.

    http://www.phpbuilder.com/columns/tim20010110.php3

  12. #12
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've read through that PHPBuilder column, and still don't "get" these indices. What is it they do? I always have a primary key in my table...something like this:

    Code:
    optionid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Is that incorrect, or is it a different way of doing things?

  13. #13
    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)
    Indexes are physical data structures used to find rows in a database.

    I'm sure you're aware of arrays. You can access an array element directly by using the index:
    echo $some_array[ 4 ];

    However, if you have say a linked-list (or a database table), you have to traverse the whole thing to find an element.

    So, you create an index on a column used in a where clause. That allows you to immediately find a row rather than traversing the whole thing (called a 'table scan').

  14. #14
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, I see - makes sense...if all I need is a number of IDs, I don't need to scan everything. For whatever odd reason, I thought I was already doing that with COUNT(optionid) instead of COUNT(*).

    I hate to ask, but what would creating an index look like in my case? I found I can only semi-follow that article on PHPBuilder.

  15. #15
    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 count( some_column ) only tells it to look at a particular column, yes -- but the WHERE clause is what limits the number of rows you want.

    Without an index, the database has to look at EACH row and see if it matches your WHERE clause. With an index, the database looks at the index and grabs the rows that has the matching columns immediately -- it doesn't have to look at them because the index already "knows" which rows match. (in a nutshell).

    Anyways, you'd want something like:
    CREATE INDEX options_pid ON options( pid );

    This may be incorrect -- if it doesn't work you'd have to look it up on MySQL's site.


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
  •