…group etc) and get sorted result set and claiming it is way faster than getting it with MySQL…indexes so selectivity is done by index and index used to resolve order by. Funny enough to filter by attributes or sort sphinx does not use indexes – indexes are only used…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… by MySQL 5.5 is as follows: SELECT s_nationkey, l_shipmode, count(*) FROM supplier INNER JOIN lineitem ON s_suppkey = l_suppkey GROUP BY…_nationkey | i_s_nationkey | 5 | const | 808 | 100.00 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i…
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY…group by so for large number of rows you’ve simply got to use different solutions, such as summary table. With MySQL…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…mysql> select c1, count(*) from v1 where c1 = 10 group by c1; +——+———-+ | c1 | count(*) | +——+———-+ | 10 | 130 | +——+———-+ 1 row in set (0.01 sec) We can use… NULL | 1310720 | Using where | | 2 | DERIVED | t2 | index | NULL | c1 | 5 | NULL | 2785914 | Using index | …
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…. After that load statement is: mysql -S /tmp/mysql-ib.sock -e “LOAD DATA …count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY carrier) t2 ON (t.Carrier=t2.Carrier) ORDER BY c3 DESC; I am using…indexes like InfoBright, but results are impressive. On drawbacks – the command line is weak ( I had to use…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
…aggregation (count(*)) on a range of records. This table is partitioned by month which means that MySQL can use …0;word-spacing:0;background-color:#fff;z-index:1;border-top:0px none;border-left:… AND ’2008-12-31′ GROUP BY DayOfWeek ORDER BY c DESC; Q3 – SELECT Origin, count(*) AS c FROM ontime WHERE…
Post: Air traffic queries in InfiniDB: early alpha
November 2, 2009 By 19 Comments
…0 Data_length: 0 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment:… The query includes syntax that is not supported by InfiniDB. Use ‘show warnings;’ to get more information…. Q4: mysql> SELECT Carrier, count(*) FROM ontime WHERE DepDelay > 10 AND YearD=2007 GROUP BY Carrier ORDER BY 2 …
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
…indexes: – load of data is getting slower; – to design proper indexes is additional research, especially when MySQL…GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY carrier) t2 ON (t.Carrier=t2.Carrier) ORDER BY….0547.67 For reference I used 5.1.36-Tokutek-…
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 “prefix%” which means index…Using Sphinx such non-traditional way required implementing some features more traditional for SQL databases rather than full text search applications. Group By…
Post: BoardReader - Forum Search Engine
November 29, 2006 By 13 Comments
… we have over 300 millions of posts indexed with only 3 search servers and still counting. I guess we’ll have half… 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…
