i have three tables in one database like this: company,categories,seo. i use this query :
SELECT seo.seo, company.diafhmish, comp_cat.cat, company.category, company.foto, comp_cat.diaf, company.comp_id, company.comp_name, company.perifereia, company.polh, company.thl, company.adress
FROM company
INNER JOIN comp_cat
ON comp_cat.comp=company.comp_id
INNER JOIN categories
ON comp_cat.cat=categories.cat_id
LEFT JOIN seo
ON seo.comp=company.comp_id
WHERE categories.category = '$kathgoria' AND
company.perifereia = '$polh'
ORDER BY company.comp_name ASC
this take about 2,5 sec to execute, but if i try the same query without LEFT JOIN piece the time reduse at 0,5 sec!! Is there any way to replase the LEFT JOIN piece?
EXPLAIN:
id | select_type | table | type possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | comp_cat | ALL | NULL | NULL | NULL | NULL | 15549 | Using temporary; Using filesort
1 | SIMPLE | company | eq_ref | PRIMARY | PRIMARY | 4 | tileodig_tileodigos.comp_cat.comp | 1 | Using where
1 | SIMPLE | seo | ALL | NULL | NULL | NULL | NULL | 1283
1 | SIMPLE | categories | eq_ref | PRIMARY | PRIMARY | 4 | tileodig_tileodigos.comp_cat.cat | 1 | Using where
I change the line LEFT JOIN seo ON seo.comp=company.comp_id with: LEFT JOIN seo ON seo.comp=comp_cat.comp both columns have the same value and i solved the problem.