…2735703 | | 6739223639 | 1 | 2735703 | 139448919 | 2735703 | +————+——–+———-+———–+———+ 10 rows in set (32.47 sec) MySQL mysql> select max(forum_id) as m,…Sphinx select max(author_id) as a ,forum_id as f from sptest where num_links=1; Empty set (2.70 sec) MySQL mysql…
Post: Slides from my Sphinx talk at RIT++ 2010
April 14, 2010 By 1 Comment
… MySQL event of the year, I headed in the opposite direction to Moscow for RIT++ 2010 conference where I presented a talk on Sphinx. You can get the PDF file here – Improving MySQL-based applications performance with Sphinx. I have been invited to talk at Open Source Data Center Conference in Nürnberg, Germany in June…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 8 Comments
… working on a case where we needed to modify a lot of data before pushing it to sphinx – MySQL did not have a…_mysql(author), ltrim_junk_mysql(title) from paintings limit 100000; 100000 rows in set (2.97 sec) mysql> select remove_dups_mysql(author), remove_dups_mysql…) mysql> select count(*) from paintings where author != ltrim_junk(author); +———-+ | count(*) | +———-+ | 28335 | +———-+ 1 row in set (6.63 sec) mysql> select count(*) from paintings where…
Post: Finding your MySQL High-Availability solution – The questions
October 16, 2009 By 36 Comments
… window of time where data loss is possible. If you can afford to lose some data, you can consider “MySQL replication” and… text indexes might be replaced by another search engine like Sphinx in order to remove the restriction. There is no HA solution… Full text to Sphinx All 4. What is the write load? The HA solutions we present are not equal in term of…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… Flexviews (the primary purpose of FlexCDC). Feed search engines like Sphinx or Solr only the rows that change. Feed third party…. Each changelog is located in the flexviews database and is named $SCHEMA_$TABLE where $SCHEMA is the schema in which the source table… Duplicates: 0 Warnings: 0 And delete data in a second transaction: mysql> delete from test.demo where c1=1; Query OK, 1 row…
Comment: Full text search for all MySQL Storage Engines
February 12, 2010 By 37 Comments
… some underlying reason why this is not efficient. WHERE FT_QUERY_1 AND FT_QUERY_2 …column that puts all the information together. Unfortunately, in MySQL, there are no expression-based indexes, but … have reasonable success with MySQL 5.1 and 100k+ product rows. Sphinx is better in some ways, but …
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 …benefit in network time will not outweight the loss in CPU time. The question is, where…fast enough. Oleksandr Typlynski, one of the Sphinx users, has conducted a benchmark, indexing …
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
…mysql_time/wtime) mysql_ratio ,avg(sphinx_time/wtime) sphinx_ratio, avg((wtime-mysql_time-sphinx_time-utime)/wtime) lost_ratio from performance_log_081221 where…complex applications using mutliple components (like sphinx and MySQL) or multiple MySQL Servers because in these cases you often can’t …
Comment: Full text search for all MySQL Storage Engines
June 29, 2006 By 37 Comments
…. Regarding relevance the problem is that the result should be (in my case) sorted by date and not by relevance. If… can see the profile for sphinx to process the 10 most common word query, sphinx spends 16 seconds in the cpu while the… is where solutions such as sphinx works very well. However I found some issues (even if sphinx was way faster than mysqls own fulltext…
Comment: Full text search for all MySQL Storage Engines
June 27, 2006 By 37 Comments
… plugin for mysql (I assume this means that the sphinx index can be created directly from within mysql just as with mysql own fulltext… where the indexing will be slow) and the datastorage needed is smaller than with reversed index. Each vector is also splitted in…, does the implementation of sphinx as plugin for mysql 5.1 mean that mysqls own fulltext engine will/might be replaced shortly by sphinx ?
