MySQL Forums
Forum List  »  Performance

Re: COUNT with GROUP BY, JOIN and SUBQUERY very slow
Posted by: Anderson Scouto da Silva Dan
Date: March 02, 2015 02:28PM

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:

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:

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



Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.