…nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have …= 1 and note like ‘%Jaime%’; On a table like this: CREATE TABLE `cast_info` ( `id` int(11) … pool was big enough to hold the whole database (data and indexes fit completely in memory). Also,…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
JOINs are expensive and it most typical the fewer tables (for the same database) you join the better performance you will get… to yet another table though why it does not work for multiple group remains question to check with MySQL Optimizer team 
UPDATE… make MySQL Optimizer to do what we want to do – Just add yet another table to the join so the info table just…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
… result set is really a virtual table. It has columns and rows, just like a database table, but it is temporary in nature… discarded. Views are not generally considered to be a performance optimization because: The contents of the result set are computed each… multiple different tables via CTAS and have them all be transactionally consistent with one another, unless you stop all database write activity…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… on database server for those things which need to be cached — system files, binary log, FRM files, MySQL MyISAM system tables etc… have and old table which was not optimized for prolonged period of time, so it reflect natural state of the table as it… experiment with even in regards to such a minor item – Database Scientists will never have their job complete.
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 3
October 19, 2009 By 25 Comments
… of databases that are supported, there are hash databases which are very similar to memcached, a table database which is similar to your classic database tables where you can add a where clause and search individual columns, and a ton more “database options”Â…. It’s our goal to make your application perform optimally. Finally, one concern you have to have is the scalability…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
…lets talk about optimizing application vs getting better hardware. With application optimization typically you have…application changes such as building summary tables, significant schema changes, use of replication, caching… of boxes to spread Web and Database instances and establish may be establish…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
…. If you are seeking to reduce the load on the database server and [as a result] increase response time for some…% yes, 21% no # Tmp table 2% yes, 97% no # Tmp table on 0% yes, 99% no # String: # Databases prod_db # Hosts localhost # InnoDB… then even better than that would be if it would optimize the queries all together! Unfortunately mk-query-digest won’t…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… radius? Anyone out there created a zipcode database and created a “search within x numer… a missing feature of the MySQL optimizer – and it has to do with …\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: coordinates type: range possible_keys: x_…
Post: A common problem when optimizing COUNT()
September 20, 2008 By 19 Comments
…it’s trying to do. You can’t fully optimize a query unless you know how to consider alternative…they were trying to do with COUNT(). This is database-agnostic, not related to MySQL. The problem is when… a column name, like this: select count(col1) from table; If you know your SQL well, you know COUNT…
Post: How to generate per-database traffic statistics using mk-query-digest
September 16, 2009 By Leave a Comment
… argument. This can be used to aggregate information by tables, hosts, users, or databases (full documentation is available via perldoc). %> perl mk… next most useful performance optimization is to migrate a database to a different server, you know exactly which database to move (db3) and…
