Tag Info

New answers tagged

0

Yes, I would add a surrogate 4-byte integer key. Your current two columns are 100 bytes, this could then be reduced to 58 bytes by the addition of the new identity column. You could even make the surrogate key a 2-byte smallint if you are sure you will never exceed 32,767 categories (might be still a good idea to leave as INT just in case). The space ...


0

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column. Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result ...


2

I've written this in SQL Server so you may have to translate to MySQL but this should work for you. CREATE TABLE #ExcludeList (ExcludeVal varchar(50)) CREATE TABLE #VideoList (VideoName varchar(500)) INSERT INTO #ExcludeList VALUES ('Prob1'),('Prob2'),('DontUse1') INSERT INTO #VideoList VALUES ('This is a prob1'), ('This is clean'),('This is ...


2

The WRITE method is minimally logged. If you use regular UPDATE statement, it would result in overwriting the entire string using FULL LOGGING. This would become inefficient when dealing with large updates. To support update for large value data types, the UPDATE syntax supports .WRITE method. This will result in less Transaction log due to its nature of ...


0

You should properly examine the data metrics Lengths of ip values greater than 45 SELECT IFNULL(LENGTH(ip),'Total'),COUNT(1) rowcount FROM mytable GROUP BY LENGTH(ip) HAVING LENGTH(ip) > 45 WITH ROLLUP; Suggested data type for ip using PROCEDURE ANALYSE() SELECT ip FROM mytable PROCEDURE ANALYSE(); Whatever SELECT ip FROM mytable PROCEDURE ...


6

Add a persistent calculated field that contains a CHECKSUM on the 5 fields, and use that to perform the comparisons. The CHECKSUM field will be unique for that specific combination of fields, and is stored as an INT that results in a much easier target for comparisons in a WHERE clause. USE tempdb; /* create this in tempdb since it is just a demo */ ...



Top 50 recent answers are included