Hello .

select * from table advert where advert.hide='show' and advert.end_putdate > 1481144400 order by advert.premium_adv DESC, advert.sort_time DESC limit 0 , 10

The base 800 thousand. Announcement, 600 thousand - active. Long chooses ads. Does it make sense to use indexes? - if so, for this sample, how to create it correctly?

at the moment I have such indexes

 id_advert - primary hide,premium_adv,premium_adv ,sort_time - составной

Page generation: for the first time the download from 3 to 9 seconds became (was 20), the next 0.2 seconds.

I do in the database explain on this request (above) and get my indices and row 600 thousand

It turns out that instead of 10 all are selected? Tell me, what I do not understand to the end?

Thank !

  • Probably because there is no end_putdate in the index? - D-side
  • error in the text .. There is in it, just doubled duplicate premium_adv,premium_adv - Sarkis Allahverdian
  • So correct the text, you can after all. - D-side

2 answers 2

Page generation: for the first time the download from 3 to 9 seconds became (was 20), the next 0.2 seconds.

This is understandable - the first time the cache is filled.

Does it make sense to use indexes? - if so, for this sample, how to create it correctly?

Yes. For THIS sample, composite (hide, end_putdate [, other fields]) . Sorting due to the presence of a condition on the inequality index speed will not work.

How many ads select such a request without a limit? How cached is it?

    I do explain on this query (above) in the database and get my indexes and row 600 thousand. It turns out that instead of 10 everyone is selected? Tell me, what I do not understand to the end?

    This is because 10 entries are selected from a sorted list. Those. First, you read all the records that meet the condition, are sorted, and then LIMIT is applied to them. In such a situation, it is worth thinking about amending the selection conditions.

    Does it make sense to use indexes?

    Definitely yes. For this request, an index on advert.end_putdate will give a noticeable performance boost.

    It also makes sense to look towards partitioning across the advert.hide field, but this is an ambiguous topic and will require some research and tests from you.