Good all the time of the day gentlemen.

there is a so-called association - "complex query" to the mysql database. the word is complex ... a loose concept, let's say:

$zapros = mysql_query("SELECT * FROM table LIMIT 0,1"); // запрос $massiv = mysql_fetch_array($zapros); // все что нашли $massiv["field"]; // нужный кусочек

This is the simplest query (I know, I know, the words of the amateur explained ...). you can wind more WHERE, ORDER BY clauses, there may be several (AND) conditions - but is this all?

I ask you to explain to me that there is a "complex query"? and how to use the result of the query. (I would like with some description) is it possible to refer to several databases with one request?

    8 answers 8

    For a long time I tolerated writing or not writing the answer, but I could not overcome myself, in the end I would still write.

    From what the previous speakers have written, I can only summarize.

    The question is what is a complex query to the database?

    Here you can discuss for a long time. On the one hand, a complex query to a database is a query which, in essence, must return only one value or one column from a table. Imagine that we have a table in which there are 1 million records , say, 15 fields of different types , and Blob, in addition, where they are stored, let's say pictures up to 5Mb . We need to get the name of the pictures, 300 pictures starting from 900 000 and ending with 900 300 they are in the name_picture field but the query looks like this

     SELECT * FROM af_products WHERE d_upload>'2010-11-02' and d_upload<'2011-12-22' ORDER BY `id` LIMIT 900000,300

    All fields will be selected here. Is everyone with math friends? Let's say the average size of a photo is 3.5 MB multiplied by 300 selected records 1 GB. just chosen from the database, the execution time of the script may be small, but the number of selected fields is wrong. But most likely this query does not relate to the complexity of the query, but to the stupidity of the developer, who doesn’t care about optimization, even as simple as selecting only the necessary fields from the tables.

    A complex query to the database is such a query. We touch several tables + we make sampling by means of LIKE. Probably difficult, although it is performed fairly quickly, with 10K entries in the table. This is the code from the module for ModX, wrote the dictionary.

     SELECT dic.name as dic_name, dic.id as dic_id,dw.id,dw.alias,dw.short_desc, dw.name AS d_name FROM `dictionary_word` dw LEFT JOIN `word` ww ON ww.id=dw.word_id LEFT JOIN `dictionary` dic ON dw.dictionary_id=dic.id where ww.name like '%".$_GET['search']."%' and dic.id=".$_GET['sid']." and dic.public=0

    Still, from a nationwide and generally accepted complex query to a database, this is a query that affects several tables, i.e. JOIN happens. But in practice, the complexity of a query is shaped by many factors; if you look at the first query, then the developer’s nonsense is there, the second one is large, but it is executed quickly. All the same, a complex query is one that takes definitely more time to execute that you were counting on, and implies optimization if it is possible. And it is possible in several ways, either by changing the conditions or breaking it up into a few more simple ones. You can experiment a lot, here the main thing is the correct final effect. also if you look at any request with

     LIMIT 900000,300

    We understand that at first all 900300 records will be selected and only then the 300 we need will be sorted - this is also a complicated query to the database, although the human factor to not do so is present.

     можно ли одним запросом обратиться к нескольким БД?

    In it is possible, and especially it is possible if they say so in one DB and are broken by prefixes. In fact, several databases are obtained, i.e. There can be many sites in one database, tables are the same only prefixes differ, for business card sites this is an option. And as a matter of fact, we cannot connect to one database. those.

     $link1=mysql_connect(); $link2=mysql_connect();

    2 different connections, and the request intended for BD 1 will not be executed for BD 2. So I think it’s hard to choose the right answer, they are essentially all right, or at least they are looking in the right direction.

    I have everything as usual :)

    ps 3341 letters / 538 words

      Well, one option:

       SELECT `t1`.`field1`,`t1`.`field2`,`t2`.`field3` from `table1` as `t1` join left `table2` as `t2` on `t1`.`some_key1` = `t2`.`some_key2`

      Or request with subquery:

       SELECT `t1`.`field1` from (select * from `table2` where `field1`='somevalue') as `t2` where `t1`.`field5`=`someothervalue`

      And I always told comrade @Expert that programming cannot be taught, trying to translate everything into Russian ... The word is complicated here in the meaning of "Composite" as opposed to simple - "Atomic".

        A question from the category of philosophical or religious :-) The complexity of requests is known by comparison, as everything in this world is relative, therefore:

        Complicated query - a query that consumes more resources from a SQL server than a simple query

        So for example a simple query:

         select * from table where id=1

        Complex query:

         select t.id, t.article from table t left join tag t1 on (t.id = t1.t_id) where t1.id in (2,3,4) group by t.id having count(t.id) = 3
        • wow !!! what was looking for. This is what makes me difficult, first of all in ignorance of 8-), you can explain in more detail what is being eaten with in this request? - sergey
        • In a complex query, the search for materials by tags, that is, table t is materials, and tag table is a bunch of materials.id <-> tag.id, and accordingly the result of the query will be a list of materials that have all three tags id = 2,3, 4 - chernomyrdin

        For me, a complex query is when you have to operate with a huge amount of data, here you need to take into account every little thing, and will not the server ever drop such a query.

        • Ie select * from table Where table - 1 TB is a complex query? - timka_s

        As I understand it, a "complex query" is a query involving several tables.

          Complex query - a query that uses many different constructions, keywords, work with several tables.

            in that the "composite" request causes boiling of the brain - this is an absolutely correct remark. but it also happens: one of my themes

            • In my opinion, difficult. difficult because unusually. with the fact that requests are composite - sorted out, by the way, thanks for the answer. let's talk about the diversity of these requests. Now let's say that this "implode", still for me, is not gnawed granite of science. Can anyone give an example of working with him, or else, with what the topic is not painted?

            Or maybe it’s hard when the regular run begins? Then for the server it will definitely be difficult even for small tasks.