Search engines such as Google use very sophisticated hidden algorithms to index search queries. In fact, they have already determined which words appear on each page, as well as the relative importance of these words and the relative importance of the pages (compared to other pages). These indexes are very fast because they are based on Bitwise Indexing .
Consider the following Google searches:
custom : 542 million google hits pager : 10.8 m custom pager 1.26 m
Essentially, they created an entry for the word custom, and in this entry they put 1 for every page that contains it, and 0 for every page that doesn't contain it. Then they fasten it, because there is much more than 0 than 1 s. They do the same for the pager.
When the custom pager search comes in, they unzip both entries, perform bitwise And on them, and this leads to an array of bits, where length is the total number of pages they indexed, and the number 1s represents the number of hits to search. The position of each bit corresponds to a specific result that is known in advance, and they only need to view the details of the first 10 to display on the first page.
This is simplified, but it is a general principle.
Oh yes, they also have huge server banks that perform indexing and huge server banks that meet search queries. HUGE server banks!
This makes them much faster than anything that can be done in a relational database.
Now, to your question: could you insert an SQL sample for viewing?
One thing you can try is to change the display order of tables and joins in your SQl statement. I know that it doesn't seem to matter, but it certainly can. If you put the most restrictive joins earlier in the instruction, then you may well get fewer common joins performed in the database.
An example of the real world. Let's say you wanted to find all the entries in the phone book under the name "Johnson", whose number began with "7". One way is to find all the numbers starting with 7, and then join them with numbers belonging to people called Johnson. In fact, it would be much faster to filter in a different way, even if you have indexing by both names and numbers. This is because the name "Johnson" is more restrictive than the number 7.
Thus, the order is counted, and the datbase software does not always determine well in advance what is combined to execute in the first place. I'm not sure about MySQL, since my experience is mainly with SQL Server, which uses index statistics to calculate which order to perform joins. These statistics become outdated after several insertions, updates, and deletions, so they need to be recalculated periodically. If MySQL has something similar, you can try this.
UPDATE I looked at the query you posted. Ten left joins are not unusual and should work fine if you have the right indexes. Yours is not a complicated request.
What you need to do is break this request down into its basic principles. Comment on search joins, such as those in currencies, exchange rates, countries, states, and cities, as well as the corresponding fields in the select statement. Does it still work just as slowly? Probably no. But he is probably not perfect yet.
So, comment on everything else until you have courses and groups at the rate id and order by courseid. Then, experiment with the addition in the left join to see which one has the greatest impact. Then, focusing on those that have the greatest impact on performance, reorder the queries. This is a trial and error method. It would be much better to look at the indexes of the columns you enter.
For example, for the line cm.method_id = c.method_id will need a primary key for course_methodologies.method_id and a foreign key index for .method_id courses, etc. In addition, all fields in cases where, by groups and by orders need indexes.
Good luck.
UPDATE 2 You should seriously look at filtering dates for this query. What are you trying to do?
AND ((('2010-09-01 00:00:00' <= esched.date_start AND esched.date_start <= '2010-09-25 00:00:00') OR ('2010-09-01 00:00:00' <= esched.date_end AND esched.date_end <= '2010-09-25 00:00:00')) OR ((esched.date_start <= '2010-09-01 00:00:00' AND '2010-09-01 00:00:00' <= esched.date_end) OR (esched.date_start <= '2010-09-25 00:00:00' AND '2010-09-25 00:00:00' <= esched.date_end)))
It can be rewritten as:
AND ( //date_start is between range - fine (esched.date_start BETWEEN '2010-09-01 00:00:00' AND '2010-09-25 00:00:00') //date_end is between range - fine OR (esched.date_end BETWEEN '2010-09-01 00:00:00' AND '2010-09-25 00:00:00') OR (esched.date_start <= '2010-09-01 00:00:00' AND esched.date_end >= '2010-09-01 00:00:00' ) OR (esched.date_start <= '2010-09-25 00:00:00' AND esched.date_end > = '2010-09-25 00:00:00') )