… are other drawbacks to using temporary tables in this way, for example when using replication, a temporary table could vanish if the slave restarts… rows in `title` by a secondary index. But the result was that it created a temporary table to count the movies per production… in index order. This made the first table in the EXPLAIN seem like it was scanning more rows. But by avoiding the temporary table…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
it’s fuuny…..but i got a trouble。。。。 the table: ID my_id follow 1 1 16 2 1 15 … by NUM desc limit 5 Follow was index。 the explain: type:index，Extra:Using index; Using temporary; Using filesort i am chinese, so my…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… MyISAM temporary table. What i decided to do is to see how much overhead do on disk temporary tables cause compared to MEMORY tables. To have things comparable I used medium size table and types which can… is not large enough to fit all key blocks from temporary table index, so we get a lot of key writes which kill…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing foreign keys as if expand_fast_index_creation is OFF to…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… we have these two tables: CREATE TABLE foo ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX(j), FOREIGN KEY (j…, you can’t recreate the “dos2″ table, and you can’t turn the orphaned temporary table back into its former self unless you… this database to get the actual filename of the temporary table rather than the temporary table name that was displayed in the error message…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… group by to be performed without temporary table or external sort. Q: How can we manage indexes on servers from DBA point of…: how 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 scanning data in index order MySQL…
Post: Derived Tables and Views Performance
August 31, 2006 By 16 Comments
… terms of query optimization. Derived Tables are still handled by materializing them in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example). One…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…: NULL rows: 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s type: eq_ref possible_keys… done by index and extra sort pass (filesort) is required, which also makes MySQL to store Join result in temporary table to do…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… innodb_index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_index_stats.index_name AND innodb_index_stats.table_schema = innodb_sys_tables.SCHEMA… stored in buffer pool rather than using some form of temporary measures. I often check these stats during warmup to see…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…..100 selects about 1% of the table. The times for full table scan vs range scan by index: mysql> select count(pad) from… going to work with in temporary table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster than range scans and other types of index…
