Hello.

The page has:

counting the total number of ads (for pagination)

select count(*) from bazardnua_advert advert where advert.hide='show' and advert.end_putdate > 1481317200

sample (table names in select replaced by *, so that the place was not occupied)

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

  1. advert.hide (hide or show) - whether the advertisement is hidden (this field changes if the advertisement is expired)

  2. end_putdate - the end date of the announcement (the field changes if the announcement has been extended)

Page generation (530 thousand ads):

  1. without indices - 2.4 seconds

  2. unique and non-unique. composition index (hide, end_putdate) - 2.7 seconds, not used end_putdate enter image description here

  3. normal index (end_putdate) - 3.4 seconds enter image description here

Tell me, in my case, at least some indices will be able to help me, or is it necessary to look in a different direction?

May need other index options? Maybe you need to add some column to the sample? What do you advise ?

Thanks for answers .

  • 2
    Words and screenshots do not match. And where is the DDL table? - Akina
  • one
    add indexes and see how much faster it will work, itit! not? - Jean-Claude
  • one
    hide / show replace IMHO with 0/1 - Jean-Claude
  • @ Jean-Claude And what do you think his type is now? if you think that the varchar, then try to explain how keylen = 2 appears in the screenshot ... which is already = 5 in the next screenshot. However, the screenshots there are not clear from what, I already said. - Akina
  • hide is just the name of the index (hide, end_putdate) and in the second screenshot the same name, but the index is different - Sarkis Allahverdian

0