mysql query optimization

Question

I have this database:

Tab1 ---1:n--->tab2 (parent->id) (max1:4) - the complicated part

Tab1 ---1:n--->tab3 (parent->id) - simple join

tab1

id
name
version
..etc.

tab2

id
parent
type
price
..etc.

tab3

id
parent
type
data

I'd like to select complete set of information from these 3 joined tables, but I need to use many variable conditions, so I need to come up with the best solution as possible.

I use this query:

SELECT tab1.id, CONCAT(tab1.name,' ',tab1.version) AS nv, ndvar.price, tab3.data,
ndvar.v1  
[more columns] 
FROM tab1 INNER JOIN ( 
SELECT parent, 
type, 
GROUP_CONCAT(type SEPARATOR '') + '' AS v1,  
MIN(price) AS price  
[more columns]  
FROM tab2  
WHERE show = 1  
[more condition]  
GROUP BY parent  
HAVING type IN (2,3) [1-3 parameters]  
)  
AS ndvar  
ON tab1.id = ndvar.parent  
LEFT JOIN content  
ON tab1.id = tab3.parent AND tab3.type = 0   
WHERE name LIKE '%xyz%'  
[more conditions]  
ORDER BY nv

I have tried to make it as simple to understand as I could.

My questions: 1) How to optimize this query to have if as fast as possible 2) What columns use as indexes? For now it is only 'id' column.

The 'type' column in tab2 contains values 0-3 and for each id there is only row with that type, so there coul by index, but i don't know if it would make any improvements to my query.

Thanks in advance.

EDIT:

This is a query used during search in on-line catalog, there will be just few inserts or updates during month, but many searches each day. The tab1 will have around hundreds of records, tab2 around tab1*4 records, and tab3 around tab1*15 records. There are many search conditions involving mostly tab1 (1-15 parameters) and tab2 for 3 parameters. All the parameters are numbers (double) except the name and version, which are name - varchar(25) and version - varchar(20).

The query is going to be executed on mysql 5.0.70, db engine MyISAM

By all statistics I have, searches for tab2.price range and tab2.type is the most common and searches for other number ranges are more common than search by tab1.name or tab1.version.

I will gladly fill in any other information anybody is going to ask.

BTW: sorry for my poor grammar, english is not my home language :)

EDIT2:

I might by misinterpreting the whole "HAVING" concept. In the v1, I need to have stored the whole set of values from tab2 rows for each parent from tab1, but I need to filter them by the tab2.type .. how to do that?

EDIT3:

This type of aggregation returns exactly what I need, BUT - I know its terrible solution, somebody knows how to improve it?

 GROUP BY parent 
 HAVING v1 LIKE  '%0%

Answer 1

Tab2.type and tab1.id, tab2.parent and tab3.parent are probably the most important rows to have indexes on, if they identify very specific rows.

As far as indexes go, other ones that might help are tab2.price, tab3.type.

As a rule, the more rows a condition has to rule out from the results, the worse it needs an index.

"WHERE name LIKE '%stuff%' is probably one of the WORST conditions to use in the world, (albeit one of the most common) because conventional indexes do nothing. If that test is eliminating a lot of rows from the result (meaning it is testing a lot of rows and few pass the condition), then you should use a full-text index.

The sub-select after the inner join may be a performance hog, since it has to build a result set regardless.