June 26, 2006

Handling big result sets

Posted by Vadim |

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:

PLAIN TEXT
SQL:
  1. CREATE TABLE `longf` (
  2.   `f1` int(11) NOT NULL AUTO_INCREMENT,
  3.   `f2` date DEFAULT NULL,
  4.   `f3` date DEFAULT NULL,
  5.   `f4` varchar(14) DEFAULT NULL,
  6.   `f5` varchar(6) DEFAULT NULL,
  7.   `f6` date DEFAULT NULL,
  8.   `f7` smallint(6) DEFAULT NULL,
  9.   `f8` smallint(6) DEFAULT NULL,
  10.   `f9` varchar(13) DEFAULT NULL,
  11.   `f10` varchar(39) DEFAULT NULL,
  12.   `f11` int(11) DEFAULT NULL,
  13.   `f12` float DEFAULT NULL,
  14.   `f13` int(11) DEFAULT NULL,
  15.   `f14` smallint(6) DEFAULT NULL,
  16.   `f15` varchar(39) DEFAULT NULL,
  17.   `f16` date DEFAULT NULL,
  18.   `f17` smallint(6) DEFAULT NULL,
  19.   `f18` int(11) NOT NULL,
  20.   `f19` date DEFAULT NULL,
  21.   `f20` date DEFAULT NULL,
  22.   `f21` varchar(14) DEFAULT NULL,
  23.   `f22` varchar(6) DEFAULT NULL,
  24.   `f23` date DEFAULT NULL,
  25.   `f24` smallint(6) DEFAULT NULL,
  26.   `f25` smallint(6) DEFAULT NULL,
  27.   `f26` varchar(13) DEFAULT NULL,
  28.   `f27` varchar(39) DEFAULT NULL,
  29.   `f28` int(11) DEFAULT NULL,
  30.   `f29` float DEFAULT NULL,
  31.   `f30` int(11) DEFAULT NULL,
  32.   `f31` smallint(6) DEFAULT NULL,
  33.   `f32` varchar(39) DEFAULT NULL,
  34.   `f33` date DEFAULT NULL,
  35.   `f34` smallint(6) DEFAULT NULL,
  36.   `f35` int(11) NOT NULL,
  37.   PRIMARY KEY  (`f1`)
  38. );
  39.  
  40. SELECT count(*) FROM longf;
  41. +----------+
  42. | count(*) |
  43. +----------+
  44. |  5242880 |
  45. +----------+

[read more...]

Posted by Vadim @ 12:03 pm :: production, tips :: 11 comments
Print This Post Print This Post book mark Handling big result sets in del.icio.usdel.icio.us :: submit Handling big result sets to digg.comdigg

Full text search for all MySQL Storage Engines

Posted by peter |

As we know build in full text search is currently limited only to MyISAM search engine as well as has few other limits.

Today Sphinx Search plugin for MySQL was released which now provides fast and easy to use full text search solution for all storage engines. This version also adds a lot of other new features, including boolean search and distributed searching.

A while ago I already wrote about Sphinx Search Egine, comparing it to built in FullText search and Mnogosearch. I guess I should soon repeat tests, adding Lucene to the list for complete picture.

And if you do not feel like patching MySQL or use MySQL 5.1 beta to use sphinx as MySQL Storage Engine you can still use it old fashion way as separate server.

Posted by peter @ 6:04 am :: production, tools :: 35 comments
Print This Post Print This Post book mark Full text search for all MySQL Storage Engines in del.icio.usdel.icio.us :: submit Full text search for all MySQL Storage Engines to digg.comdigg