JOINS and very very slow response

[buba]

Is it true, when doing a JOIN on multiple tables (i.e. 7 tables) MySQL might get into performance troubles??

I'm trying to select some records where a user_id exists in a order table and the corresponding order_id exists in a order_items table where I only need order_items where the product id matches one of the product_id's found in the Brands tabel where the brand_id correspond to a special category table.

I do have a working query with all of the above but the response of the mysql server does take a lot of time before it even gets sent back to me.

This query is also consuming a lot resources on the database server, making it not respond to other requests from other visitors.

Are there some things to speed up the query?

[tomhath]

Read up on EXPLAIN and INDEXES. The tools are there to help you figure out what's going on, it's worth learning so you write fast queries in the first place instead of trying to speed up slow ones.

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Query_Speed

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Optimising_Database_Structure

[buba]

Thanks,

I simply indexed the most essential columns and now the database is flying like crazy again 🙂