-Discussion: when the data volume is slightly larger (hundreds of thousands of records), you can use PHP to analyze the results of multiple single-table queries.

-Discussion: when the data volume is a little large (hundreds of thousands of records), use PHP to analyze the results in the subquery of a table and use mysql to optimize the performance of multiple single-table queries.

Let's talk about my current project.

The master table has 40 million records, and most associated queries are slow (more than 3 seconds). primary key index.

If a subquery is used, the execution may even Time Out (30 seconds) or be longer than 10 seconds.


Now, I use PHP to analyze the query results in each step and assemble them into strings for query using in. it is much faster now.

For example, query the Weibo posts published by a person who is interested in the table (assuming that the main table is a Weibo table ). I will first find out the person who is followed by someone, and splice the uid into a string, and then query the microblog table uid in (followed by a string.

But I am still worried about whether it will be caused by other problems, such as a serious increase in PHP load.

At present, there are few promotion users on the website, so it is unclear about the high concurrency and the number of workers.


Do my websites use join table queries or subqueries? Is it used like me?

Please kindly advise

Reply to discussion (solution)

Even a good database design can hardly avoid subqueries and joint queries.
However, a common query takes 3 seconds, and the subquery is exaggerated even 30 seconds.
Maybe you can paste your table structure and discuss it together.

It is better to use join queries for left join and right join.
When a join query is used, the query ends when a match is found.

Select aid, B. name where tb1 a, tb2 B where a. id = B. id is a Cartesian product first produced by tb1 and tb2.
Then, filter a. id = B. id entries in the Descartes to reduce the efficiency.

So use left join and right join.

It is better to use join queries for left join and right join.
When a join query is used, the query ends when a match is found.

Select aid, B. name where tb1 a, tb2 B where a. id = B. id is a Cartesian product first produced by tb1 and tb2.
Then, filter a. id = B. id entries in the Descartes to reduce the efficiency.

So use left join and right join.

When I use join queries, I first left join and then use these where conditions, which should not conflict with each other?

Reasonably create a table and properly index the table.

What about explain?

Sometimes the join query cannot be completely canceled. it mainly depends on the suitability of index creation and check whether the SQL statements properly use indexes.

It is better to take out your structure and SQL for analysis and analysis.

We recommend that you keep track of cpu usage and processes.