SQL query taking too long.

[markhartnady]

I am running a search query but it is taking too long (even when I use limits). I have tried cutting down on the number of joins as well but nothing seems to help. Normally the query takes longer than 3 minutes and the page times out.

Any suggestions? I think it has something to do with the way I am joining the tables?

$sql = "SELECT t1.id_account, t1.mns_file_no, t1.contact_person, t1.company_name1, t2.username, t2.password, ";
if ($countryon) $sql .= "t3.country_name, ";
$sql .= "t1.email, t1.date_last_changed, ";
if ($pmapactiveon) $sql .= "t4.id_pmaps, t4.period_active, t4.pmap_status, ";
$sql .= "t2.user_active FROM tb_account AS t1 
LEFT JOIN tb_user AS t2 ON t1.id_account = t2.id_account ";
if ($countryon) $sql .= "LEFT JOIN tb_country AS t3 ON t1.id_country = t3.id_country ";
if ($pmapactiveon) $sql .= "LEFT JOIN tb_account_pmaps as t4 
ON t1.id_account = t4.id_account ";
$sql .= "WHERE $where1 $where2 $where3 $where4 $where5 $like1 $like2 $like3 
GROUP BY t1.id_account ";
$sql .= "ORDER BY $order_by $order_how $limit"; 

[mordred]

Ok, but what does the query look like? All you gave us is some PHP code that combines various strings together to form a SQL query, based on some conditionals. It would be nice to see the full query without any PHP fluff around it.

Does the timeout happen with every possible query combination? Further, if you don't need to select empty values, an INNER JOIN instead of a LEFT JOIN could speed things up.

And one last point: I've got no idea what WHERE conditions are used, but have you used an index on the columns you match against?

[markhartnady]

The query changes depending on what filters the user selects... ie: he might type something for a username (as is the case below) but nothing for country, etc.

The logic layer that builds the SQL (ie: that determines the $where variables) is far too long to add in this post. Besides, it works fine.

Here is the output of an example filtering only on the username:

SELECT t1.id_account, t1.mns_file_no, t1.contact_person, 
t1.company_name1, t2.username, t2.password, 
t3.country_name, t1.email, t1.date_last_changed, t2.user_active
FROM tb_account AS t1 LEFT JOIN tb_user AS t2 ON t1.id_account 
= t2.id_account LEFT JOIN tb_country AS t3 ON t1.id_country = 
t3.id_country WHERE username LIKE ucase('%mark%') GROUP BY 
t1.id_account ORDER BY t1.id_account asc 

Here is an example of searching on username and country and a particular product map:

SELECT t1.id_account, t1.mns_file_no, t1.contact_person, 
t1.company_name1, t2.username, t2.password, 
t3.country_name, t1.email, t1.date_last_changed, t4.id_pmaps, 
t4.period_active, t4.pmap_status, t2.user_active FROM 
tb_account AS t1 LEFT JOIN tb_user AS t2 ON t1.id_account = 
t2.id_account LEFT JOIN tb_country AS t3 ON t1.id_country = 
t3.id_country LEFT JOIN tb_account_pmaps as t4 ON 
t1.id_account = t4.id_account WHERE country_name 
= 'Afghanistan' AND t4.id_pmaps like '1' AND username LIKE ucase('%mark%') GROUP BY t1.id_account ORDER BY t1.id_account asc 

Yes the primary table (tb_account) is heavily indexed but I will try using inner joins. I wish I could provide a URL to the site, but unfort. is an administrative site for a subsidiary of the U.N.

[markhartnady]

I figured it out. I was doing like searches on integer fields. Duh. I think inner joins helped too. What is the difference between inner, outer and left/right?

[mordred]

Glad you figured it out.

LIKE searches on integer fields slow down a query? I didn't know that. Are you sure? Regarding the difference between LEFT and RIGHT and OUTER and INNER JOINs, my explanation would be far too amateurish, but maybe these articles provide some insight for you:

http://www.mysql.com/doc/en/JOIN.html

http://www.devx.com/dbzone/Article/17403/0/page/1

http://techrepublic.com.com/5100-6329-5142674.html

[markhartnady]

Sorry, what I meant to say was that I was JOINING a country table and searching for the name of the country (country_name... VARCHAR) using LIKE instead of just searching the matching index (id_country... which is INTEGER... faster). Especially considering that the index is the foreign key in the account table.

Thanks for those links.