New answers tagged

0

MyISAM support in galera is cough beta at best. The best thing to do is: mysqldump ... -T db table_name > dump.sql (this will make a .sql and .txt file) drop the myisam table edit the .sql file and change ENGINE=MYISAM to ENGINE=INNODB LOAD DATA INFILE 'table.txt' INTO TABLE table_name; Do this on whatever node that you want to treat as the "source of ...


1

I need the tables to be InnoDB and in sync. Those two statements crashing. Does it crash with a stack trace in the server logs? Could you share it here? I tried the above two commands on a 2-node MariaDB-5.5.47 cluster and it worked as expected. As @jkavalik suggested, you could do something like the following : Create a new INNODB table with the ...


2

MyISAM! Shame. Switch to InnoDB. Don't worry about that PRIMARY KEY; it is too much hassle to split it up, etc. But do make sure it is the 'appropriate' character set: VARCHAR(21) CHARACTER SET ascii If it is case sensitive, then add on COLLATE ascii_bin. Yes, there is a memory and disk penalty. But there are tradeoffs with speed, simplicity, etc. ...


0

I have tried all thing you guys suggested. Thanks for that, it was useful but did not solve my problem. Indexes suggested by jkavalik helped a little bit, but the query was still too slow (45 seconds~). The problem was that there was 2 large tables - visits and calls. And I had to join them twice in the query. It was taking long time. After few days of ...


1

Composite indexes. LEFT JOIN calls last ON last.CallSourceMediumID = SourceMediumID AND last.CallCampaignID = 222 AND last.CallDate >= '2015-03-01' AND last.CallDate <= '2015-03-31' needs INDEX(CallSourceMediumID, CallCampaignID, CallDate) (The first two columns can ...


1

First add those indexes to the calls table: (CallCampaignID, CallSourceMediumID, CallDate) (CallCampaignID, CallFirstSourceMediumID, CallDate) Those should make it much faster because they allow the two joins to the calls table to check substantially less rows (it is ~70k each time now, which means millions of combinations). After that check the ...


2

MySQL is completely capable of serving as your full text search engine. InnoDB FTS indexes are reasonably good. With the size you described it should all fit in memory but of course that depends on the other tenants on the shared hosting. If it's not available (you mentioned shared hosting) you can implement your own pretty easily. Full text search is just ...


1

I often query a 170K-row table using InnoDB FULLTEXT. (Avg: 300 words.) I typically get the answer of a few dozen rows in under 200ms. That 200ms drops to below 20ms if the cache is warm. I don't think the number of documents in the table matters as much as the number of documents returned. If you need to fetch the text for 100 documents (after ...


1

(For InnoDB) All the rows with the same a will be next to each other; all rows with same a and b, will be adjacent, etc. So, I guess "yes" is the answer to #1. No. An InnoDB "block" is 16KB. If the entire table fits in one block, then everything will be in that one block. Note: The PRIMARY KEY is "clustered" with the table. So talking about the ...



Top 50 recent answers are included