How do i make my SQL query faster and more efficient?

Question

im using a PHP-MySQL application and when i need to query about 1500 records with some JOINS to other tables and GROUPED BY statements, the query takes about an hour to load. I need to know some techniques on MySQL to make the query faster. (such as are VIEWS available in MySQL?)

Answer 1

With only 1500 records taking an hour it is likely that you are missing indices. Start by using EXPLAIN to find out how MySQL is processing the query. When looking at the EXPLAIN results, check for places where the query is doing a full table scan. Those are the areas where you are most likely to be missing an index.

Simple EXPLAIN example:
explain
select t.name, min(t.test_date)
from sometable t
group by t.name

Answer 2

Instead of a single highly complex query, why not break the query into a series of 2 or 3 queries each of which produces an intermediate table that is used in the subsequent query?

Answer 3

Try to break the query into simple units, step by step, the more important to the less important. Eliminate all the unimportant parts. And make a chain of easy simple queries.
That's gonna help you to review the code and maintain it.

Answer 4

Make sure that your your group by is on an indexed column!

You can also speed up the SQL by getting the PHP to do some of the processing.

Answer 5

better keys! no views.

that was easy!