Tag Info

Hot answers tagged

7

With the table of separate runs, making the sums will mean reading entire table each time so for performance it would be good to have the sums precomputed somewhere, maybe even in Player table, with updates either periodically (daily, each hour) or on insert/update in Runs table (trigger, ORM event or other ways). But that may be not possible for your case, ...


5

You can create secondary indexes on slave whenever required. There is no issue in creating secondary indexes (Non Primary and Non Unique). If you create Primary and Unique on slave there will be chances that your replication will fail with duplicate key error on slave. Take a example I have a table testRepl on master without having any index on it and has ...


5

This is due to MySQL Master and Slave version mismatch The message below is clear, and confirms the master/slaves run different versions. The variable SERVER_UUID is not supported with MySQL 5.5 but is supported with MySQL 5.6 2015-06-29 11:01:01 8026 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the ...


5

A table is, by definition, an unordered bag of rows. There is no guarantee that if you say SELECT * FROM table you will get the rows back in the same order you inserted. Think about throwing a bunch of popsicle sticks on the ground while blindfolded; now take off the blindfold and tell me which one hit the floor last. No ORDER BY is essentially telling the ...


4

Creating sample tables DROP TABLE Runs; CREATE TABLE Runs(pid INT , Runs INT); INSERT INTO Runs VALUES(1, 100),(2, 45 ),(3, 35 ),(1, 90 ),(2, 85 ); DROP TABLE Players; CREATE TABLE Players(pid INT,Name CHAR(30)); INSERT INTO Players VALUES(1,'Dhoni'),(2,'Sehawag'),(3,'Sachin'),(4,'Dravid'),(5,'Kohli'); MariaDB [world]> SELECT * FROM Players; ...


4

From here: (My emphasis below) The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other ...


3

Use a composite index: INDEX(confirmada, codTipoTransaccion, idTransaccionOriginal, idTransaccion) The columns can be in any order, so I recommend shuffling them to meet other need(s) and/or facilitate removing some other index from t2. If you need to discuss this further, please provide SHOW CREATE TABLE for each table. Edit For JOIN ... ON ...


3

You need to ALTER a column from TEXT to MEDIUMTEXT? Such an ALTER will copy the table over, making the change as it goes. At the end, rebuild the indexes and will RENAME the table back into place. The table will be blocked from (at least) writes for the duration, which could be hours. For a less invasive approach, see pt-online-schema-change. It will ...


3

Insert query: INSERT INTO pdone.reps (veeva_rep_id,display_name,username,first,last,email,avatar_url,rep_type) SELECT Id, CONCAT(UCASE(MID(firstname,1,1)),LCASE(MID(firstname,2)),' ',UCASE(MID(lastname,1,1)),LCASE(MID(lastname,2))), username, firstname, lastname, email, 'www.some_static_url.com', '1' FROM veeva.user ...


3

MySQL, when executing an update against a single table, does -- wrongly -- treat updates as "ordered," and update the columns in the order they're written in the query. The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This ...


3

The main configuration settings are in my memory blog. Most important is 20% of RAM for key_buffer_size. (However, it does not need to be more than the sum total of Index_length of all the SHOW TABLE STATUS. This will also do the arithmetic: SELECT ENGINE, ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB", ROUND(SUM(index_length)/1024/1024, 1) ...


3

TL;DR: No, except for some basic cases. Some lock-strength reductions for ALTER TABLE have been added to PostgreSQL 9.5. You can't do anything that requires a full table rewrite without an exclusive lock though, in 9.5 or below. Some operations, like ALTER TABLE ... DROP COLUMN or ALTER TABLE ... ADD COLUMN ... without a DEFAULT and NOT NULL can be done ...


2

There are tools that allow MySQL DBAs to perform online schema changes. Both of them come from heavy hitters in the world of MySQL. The first tool is Percona's pt-online-schema-change. Documentation is comprehensive and Percona need no introduction in the MySQL world. The other tool is Shlomi Noach's (called online-schema-change again!) and is available ...


2

In order to take the backup of MySQL Server Objects i.e routines / triggers / events you can do like below mysqldump -h$MySQLHost -u$YourUser -p$YourPassword --no-data --no-create-info --routines --triggers --events $Database > MySQLStoredProcedures.sql The above will backup only the database objects , no data , no create table etc.


2

I've made a function where you just put the string and the output will be in Capital Letters: DELIMITER // CREATE DEFINER=`root`@`%` FUNCTION `LCAPITAL`(eCADENA VARCHAR(150)) RETURNS varchar(150) CHARSET latin1 DETERMINISTIC BEGIN DECLARE vPOSICION INT DEFAULT 0; DECLARE vTMP VARCHAR(150) DEFAULT ''; DECLARE vRESULTADO ...


2

Unfortunately, no there isn't because ALTER TABLE ... DISABLE KEYS disables nonunique indexes For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is ...


2

I assume the * is only for demonstration purposes. You can use GROUP_CONCAT to aggregate strings from several rows into one string. In the example below I only include carname, you probably want some more info: SELECT c.carname , GROUP_CONCAT(p.partname) FROM carparts as cp JOIN car as c ON cp.carid = c.id JOIN parts as p ON p.partid = ...


2

It is not a MySQL thing - MySQLTuner itself cannot see more memory if it is running with 32bit perl interpreter.


2

It's possible that a transaction may be partially committed leading to unresolvable corruption on reboot. This is especially the case if you use MyISAM tables - InnoDB tables have a double write buffer which is a form of Write Ahead Log (WAL). A good discussion of the concepts is available here and here. Note that from the Percona article we have "the ...


2

Check Codd's rules - there's no mention of "ordering". You can order by a PRIMARY KEY (in the SELECT statement) but you aren't forced to. If you use ORDER BY in your SELECT for your INSERT and use MySQL's AUTO_INCREMENT to INSERT into your target table, then you should have the target table's PK be "ordered" by your criteria. You should be aware that the ...


2

Query: SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME = 'sample' AND TABLE_SCHEMA = 'database_name' Limit 3; Note : Here 'sample' is table name and 'database_name' is database schema name.


2

Asuming you have: CREATE TABLE `new_table` ( `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT, `create` text, `proname` varchar(45) DEFAULT NULL, `prodb` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; Here is the query: select COLUMNS.* from information_schema.COLUMNS where ...


2

Price shouldn't be in your order table. A sales order hasMany line items. Price belongs to line item. As does delivery date. An invoice is a request for payment. An order can have many (or no) invoices, and an invoice can be for many orders. It's a many to many relationship. You don't really need invoices if you're doing pay-before-delivery (or don't do ...


1

Here's your query: SELECT T.location_id, GROUP_CONCAT(T.service_id) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING serviceids IN ('2,3'); You could use ORDER BY inside the GROUP_CONCAT(): SELECT T.location_id, GROUP_CONCAT(T.service_id ORDER BY service_id ASC) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING ...


1

since your date will grow rapidly. I would suggest to separate all the 'character field' into dimension and link with numeric values in the fact table which will only contain numeric data. This approach will be extremely fast and save you tone of space. also, depending on the partitioning configuration you can make it more efficient for retrial create ...


1

You say 100 measurements once a day. I'd have two tables as follows: CREATE TABLE Measurement ( Measurement_ID INTEGER PRIMARY KEY, System_ID INTEGER, -- FK into the System table Measurement_Date DATE (or DATETIME depending), Measurement_1 M1_Datatype, .. .. 100 lines Measurement_100 M100_Datatype ); CREATE TABLE System ( System_ID INTEGER ...


1

Of the two options mentioned, without any doubt, the first is better. MySQL is fine at handling large tables. There is no reason whatsoever to break the data into separate "equivalent" tables. In fact, having multiple tables with the same layout is usually an indication of a poor database design. For performance, you can then add indexes on the system ...


1

After much floundering around with the information_schema.tables table, I finally discovered the answer to your problem - update time doesn't work for InnoDB. See the points here by a.out and ajacian81. The only way you can do this is to implement logging tables (as suggested by Bill Karwin, a bit of a MySQL/database guru). The only other thing that I can ...


1

This error means you already have an BEFORE INSERT trigger on companies table. If it is the same trigger (meaning trig_companies_table_insert) that you created earlier and now you want to replace it then you need to drop it first: DROP TRIGGER trig_companies_table_insert; DELIMITER $$ CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON ...


1

It means you already have a trigger for BEFORE INSERT ON companies. (action time = BEFORE, event = INSERT) You can't have two of them for the same event and action time. You can fix it by editing the existing BEFORE INSERT trigger.



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