0

I have created a simple blog site with facebook connect to know when a friend visits the blog.

To know that, I have to save each id of the facebook friends and sometimes a people have up to 5000 friends and this mean 5000 INSERT query ( very slow ).

What solution do you recommend me to avoid 5000 INSERT query ?

Maybe do I could save all ids in a mysql text field at once ?

xRobot
  • 25,579
  • 69
  • 184
  • 304
  • Pleas refer how to pass table param to sp - http://stackoverflow.com/questions/10295326/should-you-make-multiple-insert-calls-or-pass-xml/10295812#10295812 – Romil Kumar Jain May 02 '12 at 07:23

1 Answers1

7

Do one long INSERT query instead:

INSERT INTO `table` (`foo`, `bar`, `baz`) VALUES
('...', '...', '...'),
('...', '...', '...'),
...
deceze
  • 510,633
  • 85
  • 743
  • 889
  • what is the difference with the classic approach ( INSERT INTO `table` (`foo`, `bar`, `baz`) VALUES ('...', '...', '...'); INSERT INTO `table` (`foo`, `bar`, `baz`) VALUES ('...', '...', '...'); ... ) ? – xRobot May 02 '12 at 07:22
  • difference with the classic approach... and? – Norse May 02 '12 at 07:23
  • Single insert statment will be not fast as compare to using table param in sp. becoz for every insert statement used in insert query, parsing will be done at sql end, so it will get slow. – Romil Kumar Jain May 02 '12 at 07:27
  • Pleas refer how to pass table param to sp - http://stackoverflow.com/questions/10295326/should-you-make-multiple-insert-calls-or-pass-xml/10295812#10295812 – Romil Kumar Jain May 02 '12 at 07:27
  • Each query has a certain overhead and requires a round trip between PHP and the database. One long query is just one long query, avoiding the repeated overhead and repeated round trips. – deceze May 02 '12 at 07:48
  • It is not just the round trip which is less. There are as well actions inside the database (triggers, transactions, whatever) which can be optimized. As well, the writes can be much better bundled (data and metadata, e.g.) – glglgl May 02 '12 at 08:13