Tag Info

Hot answers tagged

7

As stated in comments, I would store the current or effective version of a BlogStory in its respective table and keep all of its previous versions (or past states) in a separate (but related) BlogStoryVersion table. In this manner, you may find this post helpful since it presents a comparable method for a similar scenario. Business rules In accordance ...


4

To give you a flavour of JOINs and SQL, I created two tables - Customer and Cust_Order as shown. I then loaded these tables with data (see end of post for DML). These examples use both PostgreSQL and MySQL. A note on table names. I use singular names - you can, of course, use plural (as many do) - but decide and stick to one! A word of advice (and see ...


3

Not redundant, but rarely practical. An INDEX has limitations, based on 255 characters. (There are too many wrinkles for me to go into detail.) FULLTEXT is often used for big blocks of text. Would you be looking at 'words' and doing = comparisons on the whole column? Seems strange. Here's one trick for when I want to search for a phrase that messes up ...


3

If you want a proper database then of the two you should go for innodb - it supports ACID transactions, referential integrity, and smaller granularity locking. myISAM is faster in a number of read-only use cases but: The lack of support for transaction safety can lead to corruption in multi-user situations where several updates happen at the same time ...


2

I would create a distinct emails table and use a surrogate key to instantiate the reference of the email address to the person and to the user. The model would look like this: This handles the need to associate the email address with the users as well as the persons, but eliminates the redundancy of using the actual email address text to instantiate the ...


2

Assuming you always have pair , the following should give you desired result: SELECT a.event_id ,a.pair,a.versus, a.score as score1, b.score as score2 FROM table_name a INNER JOIN table_name b on (a.event_id = b.event_id and b.pair =a.versus and b.versus=a.pair and a.pair<b.pair) Side notes. 1. It makes sense to have a surrogate primary key column in ...


2

Model Here's a possible model. After I drew it I noticed that the difference with yours is that the product part number is now in the Product table; I think it should be this way as it's a characteristic of the product. Then the Job refers to a Product via its part number, and not viceversa. Each table uses a surrogate primary key for clarity. This is not ...


2

Can you get rid of the leading % in '%uggjplove13@gmail.com%'? If so, that will make a world of difference! Any index starting with EMAIL can be used (assuming you get rid of upper), and the query will be fast. If not, read on... FULLTEXT(email), when it can be used, will be very fast. But you may have to build some intelligence into when to use it, and ...


1

Out of the box answer: mysql> SELECT @@version REGEXP '^5.[6789]'; +------------------------------+ | @@version REGEXP '^5.[6789]' | +------------------------------+ | 1 | +------------------------------+ (Granted, it won't work forever.)


1

This is a simple SQL: select t2.category, t2.materials, t2.shipping from categories t1, raw_data t2 where t1.cid ='1-9' and t1.category=t2.category; or select t2.category, t2.materials, t2.shipping from categories t1 join raw_data t2 on t1.category=t2.category where t1.cid ='1-9';


1

Wrong design. When you need one more phrase, you have to ALTER TABLE, which is a costly operation. Instead... Build a new, 3-column, table PhraseGroups with PRIMARY KEY(languageid, phrasegroup). It will have L*P rows, where L = number of languages and P = number of phrasegroups. phrasegroup would be VARCHAR(44) and contain 'global' or 'forum' or ...


1

It looks that server lookup is taking time while searching for the host over the network and in order to minimize the lookup time add the entries in hosts file as entries in hosts file (/etc/hosts) does minimize the lookup time.


1

Yes, TIMESTAMPDIFF is the best approach: select TIMESTAMPDIFF(year,'2011-01-01', now() ) ; --> 4 select TIMESTAMPDIFF(year,'2011-08-28', now() ) ; --> 4 select TIMESTAMPDIFF(year,'2011-09-01', now() ) ; --> 3


1

Since there is a limit on the length of the index, if you want to guarantee the uniqueness of the whole field's value, you may want to add a hash field of that varchar field. Steps: Alter the table to add an MD5 field: alter table table1 add md5_of_column char(32) Update this field: UPDATE table1 SET md5_of_column=md5(column); Add the unique index: ALTER ...


1

As you cannot use the vendor name as identifier, since it may be duplicated (as it happens with the name of a person), you need a true identifier to be set upon creation. When someone identifies themselves in your system, they cannot identify by their name, since it is not enough to uniquely identify them. Thus you don't have any other option than assigning ...


1

If you want to drop the index that you created previously, the correct statement would be: alter table bill_item drop index idx_comp; created_date is the name of one of the columns in your table bill_item.


1

You show "create table" with MyISAM engine but the error comes from InnoDB so either you changed it or RDS did. From manual: "Tables created in older versions of MySQL use the Antelope file format, which supports only ROW_FORMAT=REDUNDANT and ROW_FORMAT=COMPACT. In these formats, MySQL stores the first 768 bytes of BLOB, VARCHAR, and TEXT columns ...


1

You may do deletes in batches from some script: 1) mark account for deletion (seems you are doing it right now) 2) in script - select one user for some marked account and delete it - as you expect many users per account, this will delete only small portion of all messages and attachments so should be fast enough 3) repeat 2 with remaining users of a given ...


1

If it's MyISAM - no chance to recover w/o a backup. If InnoDB - it depends. InnoDB flags a record as deleted and keeps it in a page for a while. When a tree is rebalanced the deleted records are purged. So whether you can undelete records depends on how much writes were done to the table after the delete. Open InnoDB tablespace in a hexeditor and try to ...


1

You can try this: SELECT m.vlanID, v.vlan_name, m.interfaceName, count(1) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType FROM macs m INNER JOIN network_devices n ON n.nd_ip_address = m.ipAddr INNER JOIN vlans v ON m.vlanID = v.vlan_id INNER JOIN ...


1

Based on the explain image above, the query is doing a table scan. (possible key and key are Null). Rows_examined: 1,088,312,551 ( 1 billion rows examined, that's quite a lot, look like a cross join to me). The second query (not exist) might not be right as it will exclude all data FROM bot_sessions_statistics WHERE date_active >= date(NOW()) compare to ...


1

The first thing I would do is fix the network issue. I would also set up pt-heartbeat to monitor Mysql slave delay. Why do you need to purge the binlog on Master? If you miss the position of a binlog to purge you could affect all your slaves. Mysql allows to set up binlog retentions. If you are running out of space, because your binlog are too large, I ...


1

The easiest method is the following Go to each Slave and run SHOW SLAVE STATUS\G Look for Relay_Master_Log_File on Each Slave Whichever Slave has the oldest Relay_Master_Log_File is the one you purge to on the Master Why Relay_Master_Log_File ? First Look at SHOW SLAVE STATUS\G mysql> show slave status\G *************************** 1. row ...


1

You should rather monitor your slaves for replication lag or if it's actually catching-up / connected with master or not! You may simply write a shell script to look at io_thread and sql_thread values and alert if they're NO. There are already many scripts available for reference. You can use Percona Monitoring Tools's pmp-check-mysql-replication-delay / ...


1

Active-Passive master-master is a good setup but I have seen SUPER (humans)users writing on slave without setting sql_log_bin. (Though super_read_only in 5.7 will change things around this.) Anyways, following is possible and works. ProdActiveMaster<---->ProdPassiveMaster | | | \/ DRmaster----->DRSlave So you know now that another master-master ...


1

I am not sure when but sooner or later we're going to see deprecation of MyISAM. InnoDB is performing well and it does have fulltext as well (though performance is a bit of something being worked over there.) InnoDB is even a default storage engine in latest MySQL versions. If you really really need performance from FULLTEXT go with MyISAM (Note ...


1

Have you tested or would it be possible to drop indexes on the destination DB table(s) where you are inserting into, insert those into smaller batched chunks (optimal as indicated above), and then rebuild the indexes on the destination table(s) once all inserts are complete? May be something easy enough to test to confirm.


1

As a general rule, run (and post the results here) an EXPLAIN on your query to see if indexes are being used or not. At first, I see there are no other indexes on the sessions table except PKs, this will almost surely make your query run slow, since the WHERE will cause the RDBMS do a full table scan to find the rows. I guess your query will at least require ...


1

Few points here: "Cartesian product" is not simple :) In your specific query, and based on the index you have, the time will highly depend on the distribution of the data. Try this query: SELECT COUNT(*) FROM table WHERE column2=''; I guess the result is not a small number. UNIQUE entry (column2, column3, column4, column5); is not healthy. The index is ...


1

With MyISAM, an INSERT/UPDATE/DELETE will request an exclusive lock, and have to wait for any SELECTs to finish. If another SELECT comes in after the write, then it will be blocked waiting for the write to get its lock and finish its action. That is, one simple write can snowball into the mess you are seeing. Changing to InnoDB is likely to avoid the ...



Only top voted, non community-wiki answers of a minimum length are eligible