Number of searches, thinking about a solution

timfire

Vanilla's search function lacks a result total ("1-30 of 283"). I know that Mark explained that including a count slows the search function down considerably, and so he purposely left it out.

I was trying to think of a solution for this, as the lack of a count is a pet peeve of mine. What about doing the count as part of the initial query, and then passing that value along? That would still slow down the initial search, but it would only need to happen once.

The simplest method, I assume, would be to add the count to the URL, using the GET method. I realize that the GET method is limited in how much info you can add to the URL, and that might cause issues. You would probably also have to worry about the integrity of the count value, if users are manually typing the URL. I guess if you re-worked the search function a little, you could pass the value invisibly via the POST method. That would involve more programming work, but would solve the above issues.

AJAX could also be utilized to speed things up for the user, couldn't it? What about popping up the initial search results and then using an onload function to add the count? (Hmm, in my head, though, it seems more intuitive to display the count first and then use AJAX to bring up the results.) You could certainly implement an AJAX count as part of an extension, but the count thing feels like something that should be in the core.

Fyorl

It doesn't matter which method you use to add the count, the fact of the matter is that you will always have to perform the same query (except with the COUNT() function) which is what causes the slowness. I do have a few questions about this though. I know the search query uses a LIMIT statement to only get the required rows but I'm not 100% sure how the MySQL server handles this request. I would have thought it would have to select all the rows, search through them and then limit the results based on the limit parameter. If that's the case then simply returning all the rows that fit the search criteria should take the same amount of time (ignoring data transfer speeds). If you return all the rows you can use PHP to emulate MySQL's LIMIT and cut the appropriate slice out of the results as well as just calling mysql_numrows() on the SQL handle.

timfire

Hey, what about the following function, FOUND_ROWS()? It seems to be able to return a "count" value without having to run the search query twice. I'm sure it would still slow things down, but by how much? My own forum is too small to be a good indicator, is there someone with a decent sized forum who wouldn't mind experimenting?

MySQL Reference Manual:FOUND_ROWS()

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause.

... [snip] ...

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to refer to the value later, save it:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ; mysql> SET @rows = FOUND_ROWS();

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again.

... [snip] ...

Fyorl

Ha, it's almost as if MySQL designed that function purely to solve this problem. Hopefully Mark gets wind of this and it's included in the core.

timfire

Ha, it's almost as if MySQL designed that function purely to solve this problem.

Actually, they gave an example of using this as part of a search function.