Check these out
Widgetschmie.de hillrippers.16 Gedankenablagerungen the fact of fiction
feed Atom feed
Last 5 comments
31 years ago
Rafael:  Thank you very much, I was having a huge headache to solve the very same problem!
Attached to Temporarily remove specific localizations from your iPhone/Mac App
37 years ago
Ray:  Having the same problem. Very frustrating. Luckily, for some reason my released version worked on the iPad itself, but now I can't get it to run in the simulator. Getting no such table, which I'm guessing is an initialization error. Will continue to investigate.
Attached to Static SQLite Library with Unicode Support for the iPhone
38 years ago
Jeremy:  FYI, I've just tried it with the SQLite 3.7.0 preview and the same problem occurs.
Also, I'm not using any extra third-party libraries with my SQLite, so the problem isn't your Unicode extension.
Attached to Static SQLite Library with Unicode Support for the iPhone
38 years ago
Jeremy:  I'm having the same problem with compiling SQLite against iOS 4 for the iPad simulator, but in my case it works fine running on an actual iPad (also works in the iPhone simulator and on an iPod Touch).
Same problem with 3.6.23.1, 3.6.23, and at least back to 3.6.21. Compiling against iOS 3.2 makes it work, though that's not really an option for iPhone (as opposed to iPad) apps.
I have no idea what to do about it or how big a problem it really is...
Attached to Static SQLite Library with Unicode Support for the iPhone
38 years ago
Pascal:  The problem seems to have deep roots, however there is a solution, see the updated post. :)
Attached to Static SQLite Library with Unicode Support for the iPhone
The archive
March 2011  (1)
July 2010  (1)
October 2009  (2)
August 2009  (1)
July 2009  (1)
March 2009  (1)
December 2008  (1)
October 2008  (1)
August 2008  (1)
July 2008  (3)
June 2008  (1)
May 2008  (3)
March 2008  (1)
February 2008  (3)
January 2008  (2)
November 2007  (1)
October 2007  (1)
September 2007  (1)
August 2007  (1)
July 2007  (1)
June 2007  (3)
May 2007  (1)
April 2007  (1)
February 2007  (2)
January 2007  (3)
December 2006  (1)
November 2006  (3)
October 2006  (2)
September 2006  (2)
August 2006  (1)
July 2006  (2)
June 2006  (6)
phase of matter » Blog » 2008 » January » 11th » MySQL speed for different SELECT approaches

MySQL speed for different SELECT approaches

Friday, January 11th 2008 - 10:16
While working on a script (in Perl), I discovered that MySQL performs very differently depending on the method used to retrieve a data row. What I need to do is walk through a set of 5000+ rows of a table with 110 columns. I have tried four different versions, and instead of elaborating and discussing, I will simply write down the method used and the execution time:
  • Using a for(my $i = 0; $i < NUM; $i++)-loop and selecting every row individually with: SELECT * FROM table WHERE id = '$i'
    85 seconds. Constantly 64 queries per second.

  • Using a for(my $i = 0; $i < NUM; $i++)-loop and selecting every row individually with: SELECT * FROM table LIMIT $i, 1
    45 seconds. During the first 5 seconds, MySQL performed at 313 queries per second, the last 5 seconds averaged at 69 queries per second after a logarithmic decrease.

  • Using a for(my $i = 0; $i < NUM; $i++)-loop after creating a temporary table with CREATE TEMPORARY TABLE temp SELECT * FROM table and selecting every row individually with: SELECT * FROM temp LIMIT 1. After that select, the first row was deleted using DELETE QUICK FROM temp LIMIT 1
    17 seconds.

  • Using a simple SELECT * FROM table and loop-fetching each row using while(my $sample = $qry->fetchrow_hashref())
    • 5 seconds.

There's not much to be said about this but: Use the simplest method MySQL provides. :)
1 comment
Pascal at 17.01.2008 11:31

Just a note: The captchas seem to show up again, so comments are accessible again.
Write a comment
Comments are disabled