…another equivalent solution. If you’re talking about using a temporary table to store an interim result…`title` by a secondary index. But the result was that it created a temporary table to count the …this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… PRIMARY KEY index entry for … really matters (really!). By enforcing replication to…count where achievement = ‘killed_troll’”; } change your schema In Example 2, above, how above moving the ‘joined’ column to the users_groups…used to the tradeoffs we take when deploying conventional stand-alone MySQL…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are, by…Results: First of all, we used a smaller dataset for … = 2000M innodb_log_files_in_group = 2 innodb_file_per_table…index-updates=0 run POINT_SELECT + UPDATE QPS test sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… in-place (I will use INSERT .. ON DUPLICATE KEY …NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(…$K=rand(0,10000) // distributed by pareto distribution $C, $PAD = …=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb…= 4G innodb_log_files_in_group = 2 innodb_log_block_…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id; be slower without an index? A: I …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 this column. This works because by…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…group by t.table_schema, t.table_name, i.index_name; Now we can can see index sizing information in the all_indexes view: mysql…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… is “const” MySQL does not count it as …used in the query. This rule also does not work if you have any group by… SET timestamp=1347399108; select count(*) from sbtest group by k; This …index. So what is the easy way to see if query is worth optimizing ? – see how many rows query sends after group by…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | …COUNT(*) AS variations FROM index_usage GROUP BY query_id, db, tbl HAVING COUNT(*) > 1 ) AS qv USING…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
…MySQL can use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index…index exists for a query with both a GROUP BY and an ORDER BY clause? Right; you’re stuck, unless your query groups by…, by counting how many lookups to index …
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…by changing indexes. Discarding the result is possible with pager: mysql…COUNT(*) | +———-+ | 320 | +———-+ and counting the number of connection for each status can be done with: mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY…
