Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from 22) have significantly better execution time (about them in next posts), [...]
Wishes for new “Pure PHP” MySQL driver
October 28, 2006 By 17 Comments
If you’re following MySQL or PHP landscape you should have seen announcement by MySQL to develop pure PHP driver. If not – Here is FAQ . I’m to meet the team (Georg, Andrey etc) which will be developing this driver during my visit to Open Source Database Conference in November so I thought it would [...]
MySQL Prepared Statements
August 2, 2006 By 21 Comments
If you care about archiving best performance in your application using MySQL you should learn about prepared statements. These do not neccesary provide performance beneft but they may, they also have other benefits. As a quick introduction – before MySQL 4.1 there were only textual statements and textual protocol for data transfer – query was [...]
Handling big result sets
June 26, 2006 By 13 Comments
Sometime it is needed to handle a lot of rows on client side. Usual way is send query via mysql_query and than handle the result in loop mysql_fetch_array (here I use PHP functions but they are common or similar for all APIs, including C). Consider table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | CREATE TABLE `longf` ( `f1` int(11) NOT NULL auto_increment, `f2` date default NULL, `f3` date default NULL, `f4` varchar(14) default NULL, `f5` varchar(6) default NULL, `f6` date default NULL, `f7` smallint(6) default NULL, `f8` smallint(6) default NULL, `f9` varchar(13) default NULL, `f10` varchar(39) default NULL, `f11` int(11) default NULL, `f12` float default NULL, `f13` int(11) default NULL, `f14` smallint(6) default NULL, `f15` varchar(39) default NULL, `f16` date default NULL, `f17` smallint(6) default NULL, `f18` int(11) NOT NULL, `f19` date default NULL, `f20` date default NULL, `f21` varchar(14) default NULL, `f22` varchar(6) default NULL, `f23` date default NULL, `f24` smallint(6) default NULL, `f25` smallint(6) default NULL, `f26` varchar(13) default NULL, `f27` varchar(39) default NULL, `f28` int(11) default NULL, `f29` float default NULL, `f30` int(11) default NULL, `f31` smallint(6) default NULL, `f32` varchar(39) default NULL, `f33` date default NULL, `f34` smallint(6) default NULL, `f35` int(11) NOT NULL, PRIMARY KEY (`f1`) ); select count(*) from longf; +----------+ | count(*) | +----------+ | 5242880 | +----------+ |
