… MySQL will execute the join. If it will fist lookup Table2 using some other index and when go to Table1 to lookup row by primary key when you do not need an index on a field which is used to join tables… doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id; be slower without an…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…you might want to look at Prepared_stmt_count to see how many prepared statements are …to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length)… ENGINE: MEMORY NAME: #sql516_1c42_1 TABLE_ROWS: 2 AVG_ROW_LENGTH: 257 DATA_LENGTH: 126984 INDEX_LENGTH…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… as query which required processing millions of rows now can be instantly summoned from query cache…use SQL_NO_CACHE to block caching for certain queries, which you know do not need to be cached. Counting… saved by query cache. Now lets speak a bit about Query Cache configuration and mainance. MySQL …
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… an amazing place. Please use this technology constructively and …by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by… 1427 rows in set (4.94 sec) 1427 rows in set (5.00 sec) 1427 rows…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…rows in set (0.00 sec) mysql> select sum(`count(*)`) from ex2; +—————–+ | sum(`count(*)`) | +—————–+ | 73027220 | +—————–+ 1 row in set (0.00 sec) This is very useful…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from comments group by user_id; Query OK, 0 rows affected…_len: 8 ref: NULL rows: 1792695 Extra: Using index 2 rows in set (0.96 sec) mysql> select * from user_counts; +———+——+ | user_id | cnt | +———+——+ | 0…
Post: MySQL Query Cache WhiteSpace and comments
March 20, 2008 By 9 Comments
…row in set (0.00 sec) mysql> select count(*) from fact where val like “%c%”; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row in set (8.79 sec) mysql> select count…count(*) from fact where val like “%c%” This tells us we should not use MySQL…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…not different selectivity might show benefit from using indexes. In fact even MySQL optimizer currently does not take it …scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… as only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains… 2 (logical) tables used in the query. This rule also does not work if you have any group by in the query…_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only sends 2 rows while scanning 10 million, while we…
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… by using the edit command. Let’s say you have the following query: mysql> select count(*) from film left join film_category using…sakila.sakila’ doesn’t exist mysql> select count(*) from film; +———-+ | count(*) | +———-+ | 1000 | +———-+ 1 row in set (0.00 sec) mysql> exit And now if…
