…ON md.id = mc.member_id LEFT JOIN categories ON …If I add any conditions regarding the category, the GROUP_CONCAT starts returning only the category mentioned in the WHERE clause (I don’t think I explained that very well, but here is an example). Adding a WHERE clause to the query: SELECT…
Post: COUNT(*) for Innodb Tables
December 1, 2006 By 32 Comments
… fact it only applies to COUNT(*) queries without WHERE clause. So if you have query like SELECT COUNT(*) FROM USER It will be much… be faster or slower both for MyISAM and Innodb depending on various conditions. In real applications there are much more queries of… but only for very specific case of COUNT(*) query without WHERE clause. It does not mean I would not like to see…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… UPDATE or DELETE and forgets the where clause or filters weren’t quite right… it’s strong points given the conditions. Percona XtraBackup for MySQL for binary backups… if it appears to be hanging Verify the file is the same on … WITH READ LOCK”. Namely when a select is blocking the flush from …
Comment: Is there a performance difference between JOIN and WHERE?
April 15, 2010 By 30 Comments
… JOIN .. ON .. syntax does *not* prevent having join conditions buried somewhere in WHERE clause in case of inner joins. My point is that if you…, but syntactically valid example: SELECT * FROM A JOIN B JOIN C ON (B.id = C.id) JOIN D ON (A.id = C.id… more readable than equally bad style below? SELECT * FROM A, B, C, D, E WHERE B.id = C.id AND A.id…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…select/group by/join/where. You may not use sub-queries or any non-deterministic functions like NOW() or RAND(). HAVING clauses…_alias: c mview_join_condition: USING (customer_id) mview_join_order: …If this had to actually access the orders, order_lines and customers tables directly (the tables on…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…conditions are placed on attributes in the dimension tables. It will be very interesting to test these queries on… IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none…WHERE DepDelay>10 GROUP BY Year) t2 ON (t.Year=t2.Year); — Q7 SELECT t.Year, c1 / c2 as ratio FROM (select…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… on one column and using order by on another column. do i need to add the index on column using order by clause. A: If index is used for ORDER BY the same index must be used for selection for the same table, not other index, also you only can have equality comparison as a search condition WHERE A…
Post: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
…if you have done mistake in select in from clause, ie forgotten join condition you might have EXPLAIN running forever. Views on… select * from (select * from test) t where i=5; +—-+————-+————+——+—————+——+———+——+———+————-+ | id | select_type …
Comment: Is there a performance difference between JOIN and WHERE?
April 15, 2010 By 30 Comments
… read with JOIN .. ON .. syntax while the same query written on multiple lines with visually separated join conditions and filtering conditions using comma syntax… difficult to forget join condition and end up with unwanted Cartesian product. It would be next to impossible if ON clause was mandatory for… D JOIN E; SELECT * FROM A JOIN B JOIN C JOIN D JOIN E ON (A.id = C.id) WHERE D.id = B…
