Thank you, sir.
The scenario is as follows:
- Is a radius system
- I have a customer table
- Have a table with all customer authentication (maybe here is the problem, as are thousands of records for each customer, and usually I need only seek the latter, what to do with join should I use a subquery as is there to limit)
- Necessary to find all customers who authenticate at a certain nasipaddress then need to filter by acct
- I use the Yii framework and could greatly improve this query. Considering that I need to know if the client authenticated in the RB I use subquery or inner join for the count, and to seek normally I use the ORM option he seeks the first table, and then search the relationships in another query, see:
http://www.yiiframework.com/wiki/527/relational-query-lazy-loading-and-eager-loading-with-and-together/#hh2
I believe a solution has, think of a scenario that should not be too rare:
- The user type in a search field (up to look like the MySQL forum itself), and returns the results for the search made.
- Let's assume that is thousands of posts, each post has a user (author, which would not be bad, because it is already expected to be 1: 1), and that each post has hundreds of comments. In the result page appears posts satisfy the research, with the post's author, with the last comment made it. It is a similar example with mine, but mine is on a smaller scale and is already so. I've tried all ways to improve it.
Here is a real example:
http://stackoverflow.com/search?q=test
- Is counted all questions containing "test" and is very fast.
- Another example is Google which has billions of lines
The importance of having all results for paging is, for the user who made the search know how your search is popular, among other obvios.
Prints of how I did to improve the speed with queries and explain, but still remains slow, in 2000 records in the customer table and 2 million in the acct is so, imagine how it will be in a month, as these tables are growing fast.
WITH SUBQUERY -
http://imageshack.com/a/img913/3086/f73pEu.jpg
WITH INNER JOIN -
http://imageshack.com/a/img538/3593/EIaIfN.jpg
EXPLAIN SUBQUERY -
http://imageshack.com/a/img661/3818/oiTFpW.jpg
EXPLAIN INNER JOIN -
http://imageshack.com/a/img901/1584/08ljQh.jpg
