1

We have a MySQL database with a large table(30000000 rows) where the banner are. We have a query such as:

SELECT * FROM banner WHERE banner.id IN (1,3,8...N);

The problem is that number in IN is large which result in slow query.

I want separate the number into groups to query, e.g. 1000 each group

Is this a good method? or other optimization?

kukka
  • 121
  • 7
  • I dont understand why you request the banner by its id. Normally you request data by name, color, date or something like this. Requests by id are normally only to get one specific row, for details ore something like that. So cant you give your banner more attributes to make them more easy to choose? – Thallius Apr 23 '15 at 06:25
  • @ClausBönnhoff ids in IN come from our changed banners, which we first only know which rows changed. We want to know the details for these rows. – kukka Apr 23 '15 at 09:24
  • What about creating a cross table 'changedBanners' with the ids and request the banners with a JOIN or just add a "changed" column to the banners table? – Thallius Apr 23 '15 at 09:59
  • @ClausBönnhoff Actually we have a cross table which we call it log_push. The log push table record all the changed ids for different tables. Here the problem is sometimes changed ids is large. – kukka Apr 23 '15 at 10:40
  • 1
    So the query of the changed banners is also slow if you use a JOIN instead of the IN() ? I would guess that a JOIN will be much faster Also you could limitate the results if you have something like a change date. In this case you can only join id's which are changed in a time period e.g. – Thallius Apr 23 '15 at 10:47

0 Answers0