Optimization question

Question

Hello, the question is in optimization. Is it possible to combine 2 requests into one.

$row = mysql_query("SELECT f.total_votes, c.sub, f.total_value, c.name AS cname, f.category AS catid, f.size, f.uploader FROM xbtit_files as f LEFT JOIN xbtit_categories as c ON c.id = f.category LEFT JOIN xbtit_users as u ON f.uploader= u.id LEFT JOIN xbtit_users_level as l ON u.id_level= l.id ".$wh." ORDER BY ".$r." DESC LIMIT ".$pp.",".$a[1]); while ($b=mysql_fetch_assoc($row)){ $mc = mysql_fetch_assoc(mysql_query("SELECT `name` FROM xbtit_categories WHERE `id`=".$b['sub']));

The first query displays all the information about the file. The second query $ mc is responsible for the output of the main category. Is it possible to combine? If you do not understand something or forgot to clarify ask in the comments.

Accepted Answer · 2012-07-22T17:31:14

Add one more

 LEFT JOIN xbtit_categories AS c1 ON c1.id = c.sub

It turns out:

 SELECT f.total_votes, c.sub, f.total_value, c.name AS cname, f.category AS catid, f.size, f.uploader, c1.name as subcatname FROM xbtit_files AS f LEFT JOIN xbtit_categories AS c ON c.id = f.category LEFT JOIN xbtit_categories AS c1 ON c1.id = c.sub LEFT JOIN xbtit_users AS u ON f.uploader= u.id LEFT JOIN xbtit_users_level AS l ON u.id_level= l.id ...

Threat It is very inconvenient to analyze an unformatted request, considering also nothing giving variables from php.

Chad Chad 8,652 one 13 37 · Answer 2 · 2012-07-22T16:39:53

so what ?:

 $row = mysql_query("/*второй запрос*/SELECT `name` FROM xbtit_categories WHERE `id` in /*первый запрос*/(SELECT /*f.total_votes, */c.sub/*, f.total_value, c.name AS cname, f.category AS catid, f.size, f.uploader*/ FROM xbtit_files as f LEFT JOIN xbtit_categories as c ON c.id = f.category LEFT JOIN xbtit_users as u ON f.uploader= u.id LEFT JOIN xbtit_users_level as l ON u.id_level= l.id ".$wh." ORDER BY ".$r." DESC LIMIT ".$pp.",".$a[1].")");

Warning: mysql_fetch_assoc () expects parameter 1 to be resource, boolean given.
I showed as an example how to combine, and not for save-paste.
Error - This version of MySQL doesn’t support 'LIMIT & IN / ALL / ANY / SOME subquery'.

Optimization question

2 answers 2

More articles: