… as ci where CountryCode in ( select distinct co.Code from Country as co inner join CountryLanguage as cl on cl.CountryCode = co… > begin > declare res int; > select count(distinct co.Code) into res from Country as co inner join CountryLanguage as cl on cl.CountryCode…
Comment: Why MySQL could be slow with large tables ?
January 7, 2009 By 141 Comments
… who is interested it came out like this instead: SELECT COUNT(DISTINCT(u.unit_id)) FROM ( SELECT u1.unit_id FROM unit… a cross-database app). Furthermore: If I can’t trust JOINS…doesn’t that go against the whole point about relational…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…_trx_id: F00 # QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort… InnoDB_queue_wait: 0.000000 # InnoDB_pages_distinct: 973 SET timestamp=1325146286; select count(*) from auto_inc; Note that logging all queries… shown here that every query is reading approximately 38.53 distinct InnoDB pages (meaning 616.48K of data), however, 95% of…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…the rows from a table. Full_join means any of the joins didn’t use indexes. If …wait: 0.067538 # InnoDB_pages_distinct: 20 SET timestamp=1193841780; SELECT DISTINCT c from sbtest where id between… # No InnoDB statistics available for this query SELECT COUNT(1) FROM t2; Important note: Although the …
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… “val” column in this table has 10000 distinct value, so range 1..100 selects about …range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in …but I doubt it will be closed. Joins Joins are used to compose the complex …
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_NAME, INDEX_NAME from master_index_stats ) UNION DISTINCT (select TABLE_SCHEMA, TABLE_NAME, INDEX_NAME …_NAME, i.NON_UNIQUE as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_… as COLUMN_NAMES from information_schema.tables t join information_schema.statistics i using (table_schema, table…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… case we actually join 2 tables but because the access type to the tables is “const” MySQL does not count it as…_sizes: 0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only sends 2 rows… by, distinct and aggregate functions are removed (A) – look at number of rows examined divided by number of tables in join (B…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… the view query. As long as a view avoids aggregation, DISTINCT, GROUP BY, ORDER BY, etc, then it can use the… | Message | +——-+——+———————————————————————————————————————————+ | Note | 1003 | select `test`.`t2`.`c1` AS `c1`,count(0) AS `count(*)` from `test`.`t2` where (`test`.`t2`.`c1` = 10) group… warning): create alorithm=merge view v2 as select c1, count(*) from v1 join params p on p.connection_id = connection_id() and…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… index_usage AS iu INNER JOIN ( SELECT query_id, db, tbl, SUM(cnt) AS total_cnt, COUNT(*) AS variations FROM index_usage GROUP BY query_id, db, tbl HAVING COUNT(*) > 1 ) AS qv USING(query_id, db, tbl); +———————-+————————————————————–+————+—–+———+ | query_id…, -> GROUP_CONCAT(DISTINCT query_id) AS queries, SUM(cnt) AS cnt -> FROM index_alternatives -> GROUP BY db, tbl, idx -> HAVING COUNT(*) > 1…
Post: Just do the math!
July 6, 2009 By 4 Comments
…some reports from your apache log files – how many distinct visitors hit the page and stuff like that. …like this ? Say you have query like “SELECT page,count(*) cnt FROM logs GROUP BY page ORDER BY … “random” access patterns – some form of index accesses, joins etc may be slowed down 100-1000 then …
