how can i execute large mysql queries fast

Question

I have 4 mysql tables and have a single query with JOIN on multiple tables and I am requesting it via jquery ajax, but it takes much too long, from about 1-3 minutes while I want to execute them on average 2-5 seconds.

Is there any special way to execute the queries fast?

Answer 1

There are various ways to optimize queries.

1. Check your indexes
2. Doing 1 large query ins't always quicker that doing a few small ones (check the performance of each)

A more detailed question with sample SQL would help as well.

Answer 2

With joins, the main operation that speeds things up is creating an index on the fields in the ON clause.

Answer 3

SELECT 
     DISTINCT * 
FROM 
   posts s 
JOIN 
   budy f ON s.userid = f.fid AND s.time >= f.f_since OR s.userid='$thisid' 
WHERE 
   f.myid='$thisid' 
GROUP BY 
   s.pid DESC 
LIMIT 20

Looking at your query, I'd think your JOIN is the culprit. I've never seen a join syntax with boolean conditions attached, so I am not exactly sure how it is going to behave without testing it on mysql directly.

Obvious thoughts:

• is posts.userid indexed?
• is f.fid indexed?
• what about posts.time and budy.f_since?
• do your AND and OR options on the join statement require parenthesis?

Have you tried rewriting the query to see if your time can be improved? Perhaps this might make a difference:

SELECT 
     DISTINCT * 
FROM 
   posts s 
JOIN 
   budy f ON s.userid = f.fid  
WHERE 
   s.userid='$thisid'
   s.time >= f.f_since
GROUP BY 
   s.pid DESC 
LIMIT 20

Since I am not sure what data is in your tables and what you expect to see as a result of your joins, I cannot be sure that my query will match yours. You'd need to check them first. Also, don't forget to make use of the EXPLAIN command to find out what mysql wants to do with your query.

Answer 4

Definitely Indexs. But maybe you have a poorly structured databse.

You might want to try auditing your databse - see http://dev.mysql.com/doc/refman/4.1/en/account-activity-auditing.html

Answer 5

Without seeing the schema, row size, number of rows in the database, and the same stats for typical queries its impossible to advise where you are going wrong. Unlike pdwalker, I have seen conditional expressions in join statements, and I still don't know what your query is intended to do. I suspect that its either a very clever bit of code or a very dumb one.

99% of the time, these problems can be fixed by tuning your queries / schema. For the other 1% you need to look at your infrastructure - e.g. having a database cluster (even if it is master/slaves) instead of a single node.

C.

Answer 6

I've found that how you write the query can mean the difference between an execution time of hours or days down to 5 milliseconds (actual result on a VERY big database).

Some of the things I did was learning to read EXPLAIN results (this is the most valuable tool you have in order to find how MySQL understands your query) using subqueries instead of joins, specifying an "index hint" in case the DB doesn't use the correct one, adding combined indexes (primary, column_1, column_2) and appending "straight_join" to force the database to join tables in the order you specified.

Answer 7

First, use EXPLAIN to find the bottleneck(s)! EXPLAIN tells you details about how the query is executed. This should always be your first starting point when optimizing queries (add indexes, rewrite the query and so on...).

http://dev.mysql.com/doc/refman/5.1/de/explain.html

Answer 8

To answer your actual question. No. There is no special way to make queries fast. We haven't all been using the secret "FAST" parameter and laughing as you struggle with slower queries.