Good day. Please help with query optimization:

SELECT * , b.`id` AS bid, u.`id` AS uid, cd.`open_n` , cd.`close_n` FROM `bills` AS b LEFT JOIN `users` u ON u.`id` = b.`author` LEFT JOIN `close_docs` cd ON cd.`bill_id` = b.`id` WHERE b.`check` != "0000-00-00 00:00:00" LIMIT 1303 , 30

EXPLAIN EXTENDED gives:

alt text

In close_docs not always lines matching b.id
In bills and close_docs about 3500 records, the query is executed about 25 seconds.
Thank you in advance.

    1 answer 1

    Try this

     Select *, b.`id` AS bid, u.`id` AS uid, cd.`open_n` , cd.`close_n` from `bills` as b, `users` as u, `close_docs` as cd Where b.`check`!= "0000-00-00 00:00:00" and u.`id`=b.`autor` and cd.`bill_id` = b.`id`

    Create a foreign key on cd. bill_id = b. id , it will speed up.

    • This is all well and good, but there is a problem that there close_docs not always entries in close_docs , so part of the rows are not close_docs in the sample. Plus and update the question. - ling
    • Foreign key is worth creating anyway) - timka_s
    • one
      So my leg works, thanks. Indicated foreign key , leaving the left join 's. And where can I read about the mechanism of operation of foreign key , preferably in an understandable language? - ling
    • one
      @timka_s, it is now 0.028 seconds. Rough - 1000 times acceleration. - ling
    • one
      @ling, 3500 / log2 (3500) = 450, almost like in theory)) - timka_s