Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Count on a subquery is slow

    Hello everyone,

    I know other ways around this problem, but I am just seeing if there is a solution to what I'm looking for.

    I have a table with 300,000 records and it is pretty wide. It is MyISAM. If I run "Select count(*) From schema.table" then the count comes back very fast. If I run "Select count(*) From (Select * From schema.table) subquery" then the query takes 30 seconds or so to come up... we assume it is making a temporary table in memory, then finally running a count on it.

    Our problem is that we are using PHP and we don't know what the subquery is going to be, but we are going to need a count on it. I need the count to come up just as fast as if I were running just the "Select count(*) From schema.table" query.

    We have thought about using PHP to remove the items that we are returning from the original query and only return the count ( Such as being able to change "Select Name, Phone, Address From schema.table" to "Select count(*) From schema.table" ), but with all of the variations of queries that are going to be passed, we don't think we'll be able to get the code correct very easily.

    I hope I explained this where you'll can understand what I'm looking for...

    Thanks for any help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the reason that SELECT COUNT(*) comes back so fast from a myisam table is because the database engine keeps the count of rows separately

    and of course it can easily do this because whenever it needs to make any change to the table (inserts, deletes, etc.), it must actually lock the table, after which it just updates the count field it keeps for the table

    innodb tables, on the other hand, use row locks, and therefore the SELECT COUNT(*) query must actually count the rows

    if you need the count for paging, have a look at the FOUND_ROWS function

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    r937,

    Thanks for the reply...

    Does that mean that the paging table is innodb when it is created in memory?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which paging table?

    and how do you create a table in memory?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    4
    What my understanding is of MySQL is that when you create a subquery, it runs that subquery and saves it in memory ( which is what I thought you were calling the "paging", but now I see what you mean for paging. I was mixing a paging file that the OS uses with what you meant by separating the pages that are displayed. )

    Sorry for the confusion...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    subqueries do not necessarily have their results saved in memory

    sometimes they are executed as joins

    it all depends on the specific query

    tell me again why you think you need a subquery?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2009
    Posts
    4
    What I'm running into is that we are paging the database results like you suggested.

    We currently are sending two different queries to our background code... one query that counts the rows and another query that has the fields that we want for displaying in our html table. (Both are the same table and same criteria.)

    Depending on the count of rows determines whether the code continues or not. If there are no rows, then it stops, but if there are some... it continues. I was just looking for a way to send the main query over and do two different things with it. Get a num rows count and then if there are some rows, get the actual field values for the query.

    Hope that helps...

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you look up the FOUND_ROWS function?

    you don't have to run a separate query to get the count!!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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