…with over 30 millions of rows. “val” column in this table has 10000 distinct value, so range 1..100 selects…mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…Query over Hive is that it works with existing MySQL data sets and queries. Another advantage is that it works with all MySQL… of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ |…query. . Query details: — Q1 SELECT DayOfWeek, count(*) AS c from ontime_fact JOIN…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… aggregating per-day, an ad that gets a million impressions takes one row, and an ad that…. If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and… complex query with many joins. I have never seen this approach scale well. Use The Best Technologies You Can MySQL …
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…items sold. A query may have to access many millions of rows of …is eliminated, the sorting, aggregation and join operations are still CPU intensive and…with only the information that changed since they were last populated. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… start with the MySQL query optimizer. The optimizer tries to choose the best join order based…MySQL doesn’t want to choose this join order, so we’ll force it with STRAIGHT_JOIN: explain select STRAIGHT_JOIN …. … STRAIGHT_JOIN becomes a table scan of about 313 sequential reads (150 million rows /…
Comment: Star Schema Bechmark: InfoBright, InfiniDB and LucidDB
January 12, 2010 By 46 Comments
…Join Cardinality Dimension, you can look at a given series and evaluate what happens as the selectivity…with 1 million vs. 120 million rows. To understand the Query Complexity Dimension, you could find queries with similar rows being joined…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…with JOIN MySQL still will not be able to use it as Optimizer is not smart enough yet to detect such cases: mysql> explain select… LIMIT. However if you’re dealing with LIMIT query with large offset efficiency will suffer. …” is showing us there are estimated 1.6 million of rows to be scanned, while …
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…$dbcon->query(“Select email from user where name=%s”,$name) – > wrapper will detect query is being called with multiple …Join. This is typical error. On other hand in MySQL you might be > better of using several queries…very low cardinality (1/2 a million rows with an index on a Boolean column for…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…query execution, sometimes being dynamic based on data. With EXPLAIN MySQL…MySQL 5.0 The workaround is to modify statement to become SELECT with…MySQL 5.0 with addition of greedy join the problem on looking at too many table join combinations in joins with… done at speed of millions of rows per second,…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…Even a query with an impossible where clause takes a long time to process: mysql> select * from v2 where c1 = 100; Empty set (1.64 sec) You can see that MySQL is accessing millions of rows: mysql> explain select…=merge view v2 as select c1, count(*) from v1 join params p on p….
