1

I have 2 tables - info and comments. info has 270,000 rows, while comments has only 100 rows.

I run a php script that selects the data from the database and encodes it to json format. The PHP script also limits the response to only first 10 rows, and both PHP files Info.php and Comments.php are exactly the same, except the names.

So why is it, that the table with 270,000 rows takes way more time to load than the one with 100 rows when it only prints the first 10 rows? comments takes 1 seconds while info takes 10 seconds.

This is the PHP query code, pretty simple :

Info.php: $query = "SELECT * FROM info ORDER BY id LIMIT 10;";

Comments.php: $query = "SELECT * FROM comments ORDER BY id LIMIT 10;";

As for testing purposes, they both have the same columns and same data, the only difference is the rows number. So I tested times with PHP and:

Info.php: 

select from database time: 0.6090 seconds
time taken to decode JSON: 6.4736 seconds

while Comments.php results:

select from database time: 0.7309 seconds
time taken to decode JSON: 1.7178 seconds

Thanks

Yakirbu
  • 182
  • 1
  • 3
  • 14
  • create a timer before and after your database queries.. then before and after the json encoding.. is it the database or the encoding taking the time up? How many columns does each table have? – Dale May 11 '16 at 10:04
  • If you think the database is to blame, do an explain on the query and post the results – e4c5 May 11 '16 at 10:05
  • @Dale As for testing purposes, they both have the same columns and same data, the only difference is the rows number. So I tested times with PHP and Info.php results : Select from database time: 0.6090 seconds Time taken to decode JSON: 6.4736 seconds while Comments.php results: Select from database time: 0.7309 seconds Time taken to decode JSON: 1.7178 seconds – Yakirbu May 11 '16 at 10:28
  • why would JSON decode the same data in different times? – Yakirbu May 11 '16 at 10:31
  • @jokesonhiltionhotel because of the pure volume of data to decode – Dale May 11 '16 at 10:34

1 Answers1

0

It might be because of MySQL select clause execution order. MySQL engine has to sort all the rows in the table first by id column, and after that limit results to 10 rows. Take a look at this answer.

Community
  • 1
  • 1
Ance
  • 146
  • 8