…m using some silly like query to illustrate some search is required, so count(*)… works great if you have relatively small number of groups which you can …group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where slack like “a%” group by…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
…mysql.com/download/mysql-5.1.html” we could not use link=const where clause but had to use link like…rather than full text search applications. Group By was added to Sphinx so we could search number of matches per… OSCON 2007 and would like to learn more about Sphinx we have a BOF on Thursday …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…hash can have collisions so you will need to have your queries do something like SELECT * … you only can have equality comparison as a search condition WHERE A=5 ORDER BY B will…mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for group by by…
Post: Should MySQL Extend GROUP BY Syntax ?
August 17, 2007 By 11 Comments
…group: This is one illustration of group by limitations in SQL language which is not offset by any MySQL…group by because result in this case is not defined. What would be quite helpful is to have…like SELECT MAX(Population), City, Country FROM City GROUP BY Country GROUPORDER BY…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
…have not asked it. Another thing to try is GROUP BY – Sphinx executes GROUP BY…search applications when exact number is not important. Sphinx mysql> select max(forum_id) as m,author_id as a from sptest group by…
Post: BoardReader - Forum Search Engine
November 29, 2006 By 13 Comments
… and we’ll likely redesign it in the future to use sphinx instead (it can do extremely fast parallel group-by on many servers, in google style). Few features which I would like to highlight – first you can use it to Search MySQL… particular url I also should mention couple of ratings we have implemented. Love for ratings probably comes from my SpyLOG background…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…by removing the duplicates: mysql> create table ex2 as select val, count(*) from ex1 group by…have…search_set using (val) where compressed.cnt >= search_set.cnt; mysql> select * from compressed join search_set using (val) where compressed.cnt >= search…
Post: FaceBook Search, Search for social networks
August 12, 2007 By 4 Comments
…search implementation. As we’re recently a lot into search having implemented a bunch of search… which talks directly to the MySQL database we can get some…by their activity (or something else which gives me best profiles) I would likely… text search. Such as finding the goods from the given group or…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… queries. If you’re joining multiple tables or if you have GROUP BY query the number of rows which need to be utilized… have clause like TITLE LIKE “%MYSQL%” you may instead use Full Text Search indexes. If you have WHERE ID%100=0 you can have extra column divisible_by… (SELECT COUNTRY,COUNT(*) FROM PEOPLE GROUP BY COUNTRY) C WHERE COUNTRY=’USA’ As of MySQL 5.0 MySQL will materialize the subquery in the…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…the load in the application by aggregating query log with mk-… something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (… memory in this test. I have compared Percona Server 5.1…search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql…
