-1

Is there any difference in performance and query speed, if i select first 100 rows of data, or select 100 rows with specific offset?

Is that different (i mean not syntax, but difference in speed for this operations) in MySQL and MS SQL?

I will have table "Items" with columns "id", "name". I have second table "properties" with columns "id", "value", and foreign key to first table. I will select all items that have specific properties in some value range. There is up to 70 properties and item can have maximum of 6 at same time. First table will have like 20-30k rows.

So i need to know, should i just limit my select by 100 or implement pages with result. Because items past 100 in result is not so important, so if that type of sql query take much more resources than simple select first 100 i will not do this.

nuclear sweet
  • 1,079
  • 10
  • 27
  • 1
    Of course it is different for different databases. And yes, it also normally needs more time to take an offset since the first n rows have to be skipped [[MySql](http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down)]. – Tim Schmelter May 19 '15 at 09:18
  • What are you supposing to do? Please tell us your background. – etalon11 May 19 '15 at 09:19
  • Table definitions, indexes etc? – jarlh May 19 '15 at 09:21
  • @TimSchmelter I agree that it's different - but is this obvious? Have pity on the lowly 12k and 600k reps. – Strawberry May 19 '15 at 14:37

1 Answers1

1

It makes sense if your select has an order by.

Both SQL Server, MySQL, Postgresql and Oracle, must perform a sort of the entire result each time. Then skipping first 100 is low cost compared to sorting.

About SQL Server take a look at: https://msdn.microsoft.com/it-it/library/ms189463.aspx?f=255&MSPPError=-2147217396

GMB
  • 216,147
  • 25
  • 84
  • 135
user_0
  • 3,173
  • 20
  • 33