Tag Info

New answers tagged

0

Since your situation is product is commercial you cannot change the query you cannot change table layout Your best shot (really your only shot) would be ALTER TABLE table2 ADD INDEX status_table1_ndx (status,table1); You may or may not see a change since the AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 ) is really a JOIN clause ...


0

I answered a similar question in Aug 2011 : Which DBMS is good for super-fast reads and a simple data structure? Since you are asking about MySQL and which storage engine. To be honest, it is hard to say because there are rare occasions when MyISAM can outperform InnoDB when it comes to SELECTs. Here are some of my past posts on this controversy Sep 20, ...


-1

If you need foreign key support, then you kind of have no other choice than InnoDB. However, if performance is the concern, MEMORY is the fastest. You have to make sure you have a permanent copy of the tables on disk, and load them into memory after each server restart. If you use other engine, and you enable query caching, you would gain performance with ...


1

You asked Can I delete the index-file and recreate the indexes on the new server to reduce the copy and tar-time by 50%? Yes, you can. You copy just the .MYD into the tar file. You will need a blank .MYI file. SUGGESTION Suppose you are moving mydata.mytable. You will have /var/lib/mysql/mydata/mytable.frm /var/lib/mysql/mydata/mytable.MYD ...


0

600 inserts/sec is more than out-of-the-box MySQL can do. You have done some tuning; more is possible. innodb_flush_log_at_trx_commit = 1 is the default, but it incurs a write after every transaction. =2 can be significantly faster. Batching inserts, if feasible for your application, can speed up INSERTs 10-fold. How many secondary indexes are there? ...


0

MyISAM is faster than innoDB in insertion and that is because it relies on OS to write data to disk while innoDB insures final disk write (fsync()). moreover, innodb MVCC feature reduces the write speed. If the little delay in write is not a problem, stick to innoDB. you don't want to face lots of table crash or long table locks while your table face read ...


0

Eventually I realized that it was some king of caching and probably on Linux level. After applying # sync && echo 3 | tee /proc/sys/vm/drop_caches followed by mysql> RESET QUERY CACHE; the query time became linear. Note though, that before this the effect was stable on different queries.


1

In addition to Rolando's suggested schema and my improvement on RENAME, you may want two more things: Purge "old" data via DROP PARTITION. This is about the only real use for moving to partitioning. Add a new partition using ALTER TABLE REORGANIZE PARTITION p99999999999 INTO PARTITION p2016_01_jan VALUES LESS THAN ('2016-02-01'), PARTITION p99999999999 ...


1

Here are the Steps to Partition Your Table by Month Create a Temp Table, Partitioned and Indexed on LogDate CREATE TABLE paramlog_ems_new ( SiteIndex smallint(5) unsigned NOT NULL, RegionIndex smallint(5) unsigned NOT NULL, OrganizationIndex smallint(5) unsigned NOT NULL, DeviceSlaveId smallint(5) unsigned NOT NULL, UserIndex smallint(5) unsigned ...


0

MyISAM still uses .MYD and .MYD. The only way you can see .frm files only and no other table file would be this: All tables are using InnoDB innodb_file_per_table disabled (all table data would be inside ibdata1 If you can log into MySQL, run this SELECT IFNULL(engine,'total') engine,(data_length+index_length) tblsize FROM information_schema.tables ...


2

MyISAM tables with FULLTEXT index and other index(es). SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM information_schema.statistics AS s1 JOIN information_schema.statistics AS s2 USING (TABLE_SCHEMA, TABLE_NAME) JOIN information_schema.TABLES AS t USING (TABLE_SCHEMA, TABLE_NAME) WHERE s1.index_type = 'FULLTEXT' AND ...



Top 50 recent answers are included