Tag Info

New answers tagged

1

If you dropped a 4GB MyISAM table and still have 100% usage of the disk, then something else is clogging up the disk. This is especially true if /var/lib/mysql is on the same mount and /root. Here is what you can do to start investigating: Go to the OS and do this: cd /var/lib/mysql df -h . If the root partition is 100% despite dropping 4GB, your ...


1

If you execute frequently those kind of queries: SELECT C1 FROM tbl WHERE source = 'x' and state = 'y' Then it might be advisable to build an additional index on both source and state. Maybe this question Should I create an index for non key columns? might help you to understand this concept.


3

Cardinality Even with high cardinality, the tipping point used by the MySQL Query Optimizer is either the key distribution or the storage engine. Back on November 13, 2012, I discussed how lopsided keys can make the Query Optimizer choose different indexes (sometime not choose and index at all) : Must an index cover all selected columns for it to be used ...


2

You need to be aware of query results and query behavior with replication running. While there are a minimum of two threads for MySQL Replication, it is the SQL thread that can get in the way of SELECT queries. Why? MyISAM Each time an INSERT, an UPDATE, or a DELETE is executed, a full table locked is issued. That can block SELECTs. The only exception is ...


2

ASPECT #1 This sounds like you are suffering from a classic case of bulk insert buffering. (Forgive me if I sound like a doctor). LOAD DATA INFILE takes advantage of a tree-structured bulk insert buffer. The size is set by the option bulk_insert_buffer_size. Please note what that part of the Documentation says: MyISAM uses a special tree-like cache to ...


2

You might need to LOCK the tables before you insert the data into the database and then you unlock after the LOAD statement. There are some tips on MySQL manual as well, check it out: http://dev.mysql.com/doc/refman/5.6/en/optimizing-myisam-bulk-data-loading.html


0

I try to avoid mixing MyISAM and InnoDB tables in the same database, but this is for sanity rather than practical reasons. However, I find it useful to have a database with MyISAM tables for fulltext search so I can run that on sites. Keeping it in a separate database with a foreign key for each entry makes it easy for any other developers working on the DB ...


0

Unfortunately the only way to recover your data is to recover them from a backup, you can import just that single table and then insert the particular row. I will edit my answer to get more precise here, first of all you write that you use dynamic row length. This makes the recover operation way harder if not impossible compared to fixed row length. If you ...


1

I will add to the recommendations made by @RolandoMySQLDBA. It looks like notes.cid should be a foreign key related to customers.id. You might try replacing the SELECT DISTINCT sub query with an EXISTS sub query. The select distinct will look for all related notes. An exists may perform better since it only check for the existence of at least one note. ...


2

SUGGESTION #1 You have no meaningful indexes. That can have a detrimental effect on execution since the MySQL Query Optimizer has no help in searching. You should all the following indexes ALTER TABLE customers ADD INDEX bigindex (status,deadlead,purchased,last_updated); ALTER TABLE notes ADD INDEX timestamp_id_index (timestamp,id); This may help with ...


2

You have not defined any indexes, so this query would be slow even without sorting, because it needs a full table scan. You should define a combined index on (uid, id). The first part helps in finding all rows WHERE uid = X. If these have been found, the second part can be used to sort the result set without the need to use filesort. Use this create table ...


4

+1 to @RolandoMySQLDBA of course for another valiant answer. But more to the point of your question: ...will [there] be any reading problem while I copy a MyISAM table files (the .frm, .MYD, MYI) and it gets a [write] transaction. YES. You can't get a consistent backup even for a single MyISAM table unless you do some type of locking to prevent ...


3

In some cases, you could face issues copying .MYD and .MYIs because of a major weakness MyISAM has: Data changes (changes to .MYD files) are cached in the OS. (Of course, this would be 100 times worse with MySQL in Windows, so I'll leave Windows out of this answer). If you are using MySQL 5.6, I have a little good news. The command FLUSH TABLES WITH READ ...



Top 50 recent answers are included