1

Which approach is better:

  1. sending complex select query from PHP, or;
  2. create views, functions and stored procedures of it in MySQL and request them instead?

For example, I have these tables:

  1. language (id, code, name);
  2. country (id, code, name);
  3. region (id, ctry_id, code, name);
  4. city (id, rgn_id, code, name);
  5. company (id, cty_id, vat, phone, email, url);
  6. cnt_lng (id, ctry_id, lng_id, translated_name);
  7. rgn_lng (id, rgn_id, lng_id, translated_name);
  8. cty_lng (id, cty_id, lng_id, translated_name);
  9. cmp_lng (id, cmp_id, lng_id, name, info).

And I want to select these information:

country, region, city and company details by defined language e.g. en.

And this is my query:

SELECT
  ctry_lng.translated_name,
  rgn_lng.translated_name,
  cty_lng.translated_name,
  cmp.phone,
  cmp.email,
  cmp.url,
  cmp_lng.name,
  cmp_lng.info
FROM
  language AS lng,
  country AS ctry,
  region AS rgn,
  city AS cty,
  company AS, cmp,
  cmp_lng,
  ctry_lng,
  rgn_lng,
  cty_lng
WHERE lng.code = "en"
AND ctry_lng.lng_id = lng.id
AND ctry_lng.ctry_id = ctry.id
AND rgn.ctry_id = ctry.id
AND rgn_lng.rgn_id = rgn.id
AND rgn_lng.lng_id = lng.id
AND cty.rgn_id = rgn.id
AND cty_lng.cty_id = cty.id
AND cty_lng.lng_id = lng.id
AND cmp.cty_id = cty.id
AND cmp_lng.cmp_id = cmp.id
AND cmp_lng.lng_id = lng.id
ORDER BY cmp_lng.name ASC;

The query above is that which I am going to send from PHP to MySQL. Or should I create a view, a function or a stored procedure and then send query from PHP to MySQL its name.

Thanks anyway!

Salim Ibrohimi
  • 1,351
  • 3
  • 17
  • 35
  • 1
    @Jens: ok, could you show an example, please? – Salim Ibrohimi Jan 29 '18 at 06:32
  • @Jens: and this is not about: `WHERE` operators or `JOIN`, read the question again, pleas. – Salim Ibrohimi Jan 29 '18 at 06:34
  • 2
    This query is not particularly complex, just joins a couple of tables and applies a single filter. As to which approach is better? This is quite subjective. If the query is indeed complex, then a view may save you some time because it is compiled when you create it. Views also can be used for access control. But it is up to you to make up your mind. – Shadow Jan 29 '18 at 06:41
  • @Shadow: thanks a lot! i appreciate it. – Salim Ibrohimi Jan 29 '18 at 06:43
  • 1
    Regarding @Jens' comment: instead of `FROM table1, table2 WHERE table1.id = table2.id` use something like `FROM table1 INNER JOIN table2 ON table1.id = table2.id`. – Paul Jan 29 '18 at 08:07
  • @Paul: I will consider it too, thanks a lot! – Salim Ibrohimi Jan 29 '18 at 08:15

1 Answers1

1

The query doesn't seem very complex in its nature. Stored procedures and views are not relevant to optimizing this query and you can read more about them in this answer

Collector
  • 2,034
  • 4
  • 22
  • 39