Forum Moderators: open
What I want to do is search by the COMPANY_id and get the following data out
BOXPRODUCT_id ¦ IMEI ¦ SMSNUMBER¦
0000000001 ¦ 123 ¦ 667 ¦
I am using the following query but it take's forever to bring back any data. Is this the right way of doing this?
SELECT
`boxproduct`.`BOXPRODUCT_id`,
`products`.`IMEI`,
`sims`.`SMSNUMBER`
FROM (`products` LEFT OUTER JOIN `boxproduct` ON `products`.`BOXPRODUCT_id` = `boxproduct`.`BOXPRODUCT_id`)
RIGHT OUTER JOIN
`sims` ON `sims`.`BOXPRODUCT_id` = `boxproduct`.`BOXPRODUCT_id`
WHERE `boxproduct`.`COMPANY_id` = 'ME' AND `products`.`COMPANY_id` = 'ME' AND `sims`.`COMPANY_id` = 'ME'
ORDER BY `boxproduct`.`BOXPRODUCT_id` ASC
Typically for long running queries, I first thing I check is that indexes exist for the fields involved. Putting EXPLAIN before the SELECT will provide useful information.
When joins are involved, the next thing I do is find a way to limit the amount of data involved in the join. In this case, it looks like your first join will end up creating a temporary table including all the data in the entire products table and the entire boxproduct table. Only after that are you limiting it by the company_id. Even with proper indexes this would still be slow if those tables are large.
