How do I reduce MySQL query execution time from 90s to less than 5s or even ONE?

I undertake performance optimizations on a daily basis with clients. Some optimization are easy, some are not. You need to know your tools such as EXPLAIN, CREATE TABLE, SHOW INDEXES, I_S and you need to understand the iterative process of verification.  Recently I turned a 10 table join for a client that run 15,000 times per second across 100s of servers.  They had invested a great amount of time to create indexes, and indeed all 10 tables were using indexes. Creating only better indexes, no code changes, no configuration changes, I was able to reduce the time 175ms to 10ms. Creating Indexes is only part of the process of query optimization.  Knowing how to create better indexes is a topic for an upcoming talk at http://effectiveMySQL.com

1. Read about the normalization of RDBMS 2. check if you have primary key defined for each table 3. check if you have foreign key defined for each relation between tables tables 4. if you do not have foreign key for one of the relations, think about whether your model is correct 5. if you want to understand you problem - Imagine two tables, each with 20,000 rows, without index. Then you can easily calculate that MySQL have to test 20.000 * 20.000 = 400.000.000 combinations :-(

It can be of either not properly indexed, and even if you have indexes, the index is not used properly. Check this link http://www.ezeestudy.com/2010/06/22/mysql-query-optimization-3/, where it clearly explains on how you can optimize your queries

Try using a series of simpler queries - I've sped up similar queries by breaking down the joins and using large 'IN (...)' statements instead. This change alone sped up an address search using wildcard matches from over 30 seconds to just under 0.5 seconds.

Never use Indexes on all the fields that you are using in WHERE clause.Try EXPLAIN YOUR MYSQL QUERY . This command will tell you about the no. of rows being scanned and whether your indexes are being used or not. To optimize a query, we need to avoid full table scans and reduce the no of rows being scanned. You got 20k rows with 6 joins and it takes about 25secs. Make sure you have your indexes on the correct fields (ex.scanning 2k over 20k is always going to be quicker). A row with just 20 records might not need indexes. Use your indexes wisely since it slows down writing your data .Clauses like GROUP BY, ORDER BY, LIKE ,DISTINCT are relatively more time consuming. If you are using them try evaluating it while scripting. Do not use sub-queries. Do not compute inside your query, evaluate it earlier if possible.If you have decent RAM, try caching your data if doesnt changes that frequently.Eventually, it will be a lot of trial and error to reach an optimized query. Also, your hardware matters. The more power it has ,the faster execution it does.I used to PARTITION my mysql table with great effect. See if you can use partition in your use case.In short : Sensible indexing + relevant caching + partitioning(if required) should solve your problem. 20K is still a very short no.

There’s no way to know for sure unless you tell us what’s the query.But let me give you some pointers. First of all make sure you add indexes to all the fields http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-workThis will improve your query performance 10x fold.2. Make sure you’re using INNER JOIN instead of LEFT or RIGHT JOIN. INNER JOIN is faster than the others.20k rows is really not that much so I guess is an indexing issue.