…”=2007 GROUP BY “Carrier”) t JOIN (SELECT “Carrier”, count(*) AS c2 FROM OTP.”ontime” WHERE “Year”=2007 GROUP BY “Carrier”) t2 ON (t.”Carrier”=t2.”Carrier”) ORDER BY… extract, filter, transform external data (there is even access to MySQL via JDBC driver) just in SQL queries (compare with single…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…, nothing more. Would doing a SELECT competition_id, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id; be slower without… for group by by this column. This works because by scanning data in index order MySQL gets data in already sorted order and looks at “one group at…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000…length),2) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10; …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…steps, without MRR: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note…n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue…MySQL 5.6 and MariaDB 5.5. Handler_mrr_rowid_refills counts how many times the buffer used by…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
… have been solved by querying INFORMATION_SCHEMA. For instance, counting the number of sleeping connections can be done with: mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA…’; +———-+ | 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 COMMAND ORDER BY…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… is amount of rows are analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will return only one row… so. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If there…
Post: New OLAP Wikistat benchmark: Introduction and call for feedbacks
January 28, 2010 By 16 Comments
… change datasize by varying amount of months loaded into fact table (so this is scale factor). EER diagram ( made with MySQL Workbench… of query ( again, full list on Benchmark Wiki) SELECT project, sum(page_count) sm FROM pagestat JOIN datesinfo di ON ( di.id….calyear=2009 GROUP BY project ORDER BY sm DESC LIMIT 20; I am going to load data and run queries against available engines: MySQL MyISAM…
Post: Just do the math!
July 6, 2009 By 4 Comments
… like this ? Say you have query like “SELECT page,count(*) cnt FROM logs GROUP BY page ORDER BY cnt DESC LIMIT 100″ to find the most… predict it without running benchmarks. There are couple of ways MySQL may execute query above but lest focus on the most…. Another aspect is the temporary table – the number of rows MySQL can insert/update depends on whenever temporary table can be…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier):…GROUP BY Carrier ORDER BY c DESC; — Q5 SELECT t.Carrier, c, c2, c*1000/c2 as c3 FROM (SELECT Carrier, count…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… to quote each field. After that load statement is: mysql -S /tmp/mysql-ib.sock -e “LOAD DATA INFILE ‘/data/d1/AirData… -Q4: Count of delays per Carrier for 2007 year SELECT carrier, count(*) FROM ontime WHERE DepDelay>10 AND YearD=2007 GROUP BY carrier ORDER BY 2 DESC…=2007 GROUP BY carrier) t JOIN (SELECT carrier, count(*) AS c2 FROM ontime WHERE Year=2007 GROUP BY carrier) t2 ON (t.Carrier=t2.Carrier) ORDER BY c3…
