… $result = mysql_query($query) or die(‘Query failed: ‘ . mysql_error()); // retrive result while ($line = mysql_fetch_array($result, MYSQL_NUM)) { } // Free resultset mysql_free_result($result…Let’s look mysql_query function: http://www.php.net/manual/en/function.mysql-query.php. Unfortunately there …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…customer_id, customer_name, sale_when; $ php convert.php demo < sales.sql CALL flexviews.create('demo...results mysql> select sum(total_lines) from dashboard_customer_sales ; +——————+ | sum(total_lines) | +——————+ | 155186550 | +——————+ 1 row in set (0.64 sec) mysql…
Post: Large result sets vs. compression protocol
December 20, 2007 By 6 Comments
mysql_connect() function in PHP‘s MySQL interface (which for reference maps to mysql_real_connect() function in MySQL C API) has a $client_flags parameter since PHP… bottom line: if you’re fetching big result sets to the client, and client and MySQL are on different boxes, and the…
Post: MySQL automatic data truncation can backfire
January 9, 2007 By 10 Comments
… problem I found out PHP and MySQL are both to blame. PHP is to blame because in 32bit PHP version result of crc32() function was returned as signed integer, in 64bit build of same PHP… is kind of expected. What was unexpected however is how MySQL executed select queries if key value would be out of…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… crazy. Why ? Because by default mysql_query uses mysql_store_result C library call and buffers all result set in the process memory. Not… memory_limit PHP config variable because that only controls memory which passes via PHP memory management which does not apply to MySQL result set. OK there is “easy” fix for this problem, you can use mysql…
Post: Cache Performance Comparison
August 9, 2006 By 73 Comments
… home test box (2Ghz AMD Sempron CPU) using MySQL 4.1 and PHP 5.0. For memcached access memcache extension from pecl… not work and results will be wrong. So what is about results and how we can use them for MySQL Performance tuning ? Not… on different level (ie single object constructed from multiple query results) MySQL Query Cache may improve performance of your application. Multiple layers…
Post: Impact of the number of idle connections in MySQL
December 17, 2010 By 20 Comments
… connections” php ./make_conn.php $numconn & cat dbt2.sql | mysql -h 10.2.2.129 -u root dbt2 sleep 120 ./run_mysql.sh -h… connections” php ./make_conn.php $numconn & cat dbt2.sql | mysql -h 10.2.2.129 -u root dbt2 sleep 120 ./run_mysql.sh -h… generating the idle connections from a third box and the results were the exact same. The conclusion is quite straightforward, idle…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… 1 10 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e’drop database test1′; mysql -e’create database test1′; done; tables: 1… following results: [root@DB10 ~]# for i in 1 10 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e’drop database test1′; mysql -e… following results: [root@DB10 ~]# for i in 1 10 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e’drop database test1′; mysql -e…
Post: MySQL wins C'T Database Contest
August 29, 2006 By 27 Comments
Today MySQL published the press release with results of Database Contest (results on German available here http://www.mysql.de/ct-dbcontest). Peter and…/connect/ct-dbContest.html). Most interesting results are (more orders per minute are better 
): MySQL5/PHP (our solution) : 3664 orders per minute…
Post: Apache PHP MySQL and Runaway Scripts
May 20, 2008 By 20 Comments
… these tests. Tests were done with PHP 5.2.5 and Apache 2.0.59. Results were a bit surprising: Running “TightLoop… unfortunately very serious limitation in practice when you’re using MySQL with PHP as you can have single runaway query which takes… open another MySQL connection to kill the query which was running (otherwise query may well continue to run even if PHP script…
