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!

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

r937,

Thanks for the reply...

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

which paging table?

and how do you create a table in memory?

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...

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?

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...

did you look up the FOUND_ROWS function?

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