… interested in best selling product or top sales people. This information may often need simple select query, however what if you would… two queries: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by cnt desc limit 10; +——+—–+ | grp… | 290 | | 7918 | 290 | | 4055 | 290 | | 6950 | 290 | +——+—–+ 10 rows in set (21.12 sec) mysql> select count(*) cnt from dt where slack like…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
…the top of the stack. If you look at MySQL…mysql> select count(*),avg(wtime),avg(utime/wtime) cpu_ratio, avg(mysql_time/wtime) mysql_ratio ,avg(sphinx_time/wtime) sphinx_ratio, avg((wtime-mysql…| | 09 | 4810 | 6.330725168364 | 0.60329631087965 | | 10 | 5445 | 3.8355732669953 | 0.53918653169648 | | 11 …
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…MySQL will still print full number. Here is example: mysql> explain select * from lt limit 10; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…to help lookup rows for B BETWEEN 5 AND 10 predicate because it is not index prefix. It …is not very selective. A lot of this mistakes come from missing one very important MySQL limitation – once MySQL runs into…many pages requiring potentially a lot of IOs. On top of that if you just need access to …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…Clicks by day, by customer, top ads by clicks, top ads by click-through ratio,…do it on the master with INSERT..SELECT queries, it will propagate to the … either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and… much less memory, or even with 10-15x fewer servers. Clever application design,…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
… MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you…many queries were accumulated in the logs or finding 10 last queries is not fast. The good thing however… SELECT * FROM slow_log ORDER BY start_time DESC LIMIT 100 you would find new queries coming to the top…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…mysql-bin.000022 | mysql [root@sb logs]# mysqlbinlog –verbose –base64-output=decode-rows –start-position 336797275 mysql-bin.000022 | mysql mysql> select…top…
Comment: Innodb performance gotcha w Larger queries.
September 28, 2009 By 7 Comments
… to say, I didn’t find the ” thd_is_select(trx->mysql_thd)” clause in the 5.1.30 codebase but did… dominating in the 5.1.30 codebase, disappear from the top 10 functions in 5.1.37 codebase. Though unlike your success…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
…OLTP workloads. In fact, both Percona Server and MySQL 5.5.7rc have numerous performance improvements …;text-indent:0px;padding-left:3px;border-top:1px solid #CCC;border-right:1px solid #…ORDER BY c DESC LIMIT 10; Q4 – SELECT carrier, count(*) FROM ontime WHERE DepDelay>10 AND FlightDate between ‘…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… web sites you will fine top tags, recently registered users etc -… of query with LIMIT 1000,10 you will have WHERE … fast: mysql> explain select * from test order by k limit 5; +—-+————-+——-+——-+—————+——+———+——+———+——-+ | id | select_type | table…
