There is a request that is executed 7 seconds. Records in the table img about 10k.

 SELECT img.id,date,title,url,views,votes,values,name FROM img INNER JOIN img_views ON img.id = img_views.m INNER JOIN img_rating ON img.id = img_rating.m INNER JOIN users ON img.user = users.id WHERE status = '1' ORDER BY img.id DESC LIMIT 0, 10

By tests I found out that the brakes come from INNER JOIN img_views и INNER JOIN img_rating . These tables store data on the rating of records and the number of views, 10k in each table.

Is there a way to optimize the query? And does it make sense to store data on the ranking of records and the number of views in other tables? Tables in InnoDB.

  • one
    Execute explain for your request and attach to the question. And in a little more detail, outline the structure of the tables, especially interested in the presence of indexes on the columns participating in ON . And status is what is your table. It is impossible to give recommendations without understanding why views and rating are created for example, and what kind of load they still carry besides storing some columns, which in this query are not even visible from where - Mike
  • 2
    If the views and rating contain 10k records, i.e. as much as in the main table - does this mean that the records of them have a one-to-one connection to img, if so, then these tables are most likely not needed, especially if they store one field in addition to id - Mike
  • @Mike a table with a rating can be transferred to the main table, it will rarely drop, but what about the views, every time you look at the record set VIEWS=VIEWS+1 , that is, the table block, will it affect performance? - Rufex
  • will not affect. It is necessary to take out to another table when the connection is one-to-many, for example, if you would store who exactly viewed it. And in such cases, the total number for the speed of reading in the main table endure. - Mike
  • one
    I did not understand the last sentence. in the N block of records, depending on how much it fits. In the fields of type view, the main thing is to initially put 0, and not NULL, so that the place is immediately reserved and then when updating the record length does not grow. All single values ​​should be put directly into the record. Of course, the img record is longer and smaller in the block, but all the same with the same viewing that you want to calculate, this block rises from the disk so that the script takes the url from it, so at the time of update it will already be in memory and additional accesses in addition to the actual recording does not require - Mike

1 answer 1

You should add to the table img rating fields and views. If the tables are connected one-to-one, then to increase the performance should be joined. If the relationship is one to many, then write a trigger that will be executed when adding an entry to the table of ratings or visits and increase the counter of this data in the table img.