Hot answers tagged

4

The point of "default" values is that they are the default values. You Don't need to list them in the insert query at all. They'll be inserted automatically. To pass CURRENT_TIMESTAMP as a parameter, you probably need to use the equivalent pseudo-function, CURRENT_TIMESTAMP()... which is the same as NOW() Stop and consider DEFAULT CURRENT_TIMESTAMP ON ...


3

From comments: DISTINCT is very similar to GROUP BY <all selected columns> and getting rid of the temporary table may be impossible when joining many tables as the server needs it to check uniqueness of the returned rows. Covering indexes (Using index) are quite useful when you need to get the top performance. I prefer IN() instead of multiple ORs ...


3

Without any knowledge about the workload of the database, is almost impossible to answer to a question like this. How often those “extra” data will be used? What are the most common queries? How frequent they are? and how much it is important the speed of the execution? The general advice is always the best one for me: make things as simple as possible, ...


3

De-normalisation and certain kinds of data duplication can be a convenient way to speed-up processes. Examples are caching, datawarehousing, and materialised views. Meanwhile the duplicated data is relied upon as a read-only snapshot (i.e. point-in-time copy of the actual data), or the system can guarantee consistency (like in the case of caches), it is ...


3

ORDER BY in the sub query is what's making it slow. ORDER BY makes query slow since you need to order (sort) all the objects in the query results. It is in order of N Square if you don't have an index, and N log N if you do. And you don't even need to order by all of it. What you need is only to filter out the minimum, so instead of: ORDER BY ...


2

You'll speed-up the query significantly if you create an index (ideally a unique index) on property_ad_id and created_at, and another on just created_at: CREATE INDEX ON property_ads_history (property_ad_id, created_at); CREATE INDEX ON property_ads_history (created_at); # if not already there! Note that the condition t1.price > -1 OR t1.price <> ...


2

Not sure what you are using for this, but apparently it is looking for a very specific answer. Your answer will work, but it looks like they are expecting WHERE City <> 'Paris' This would also be valid: WHERE City != 'Paris' but you would probably get the same error.


2

The simplest and probably most common way would be to declare the (album_id, artist_id) pair of columns a unique composite key. There are two variations of this approach. First, you can keep the current structure of the album_artist table and simply add a unique constraint on the said two columns: ALTER TABLE album_artist ADD CONSTRAINT uq_album_artist ...


2

We have millions of tables deployed running ARIA in production. It is definitely a better choice over MyISAM as far as crashing and recovering from a crashed table. If you have high concurrent write neither are a option as they both only support table level locks. For tables with more write intensive operations stick with InnoDB, and with heavy write tables ...


2

You can use variables: delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT @fn:=`first_name`, @ln:=`last_name`, ...


2

The CASCADE clause works in the opposite direction. From the parent to the child table. When you delete a row in the parent table, the related rows in the child table are deleted as well.


2

You can misuse ~/.my.cnf for being able to change the mysql-root-password. The trick is to have a task "Set root password"(nr.1), which will set the password. Afterwards, you have a task, which creates a ~/.my.cnf with the correct credentials (nr.2). On a new system, ~/.my.cnf is not present. Task nr.1 will create mysql-root-user with given credentials. On ...


2

Your query has many issues that difficult optimisation. You use LEFT JOIN in several tables where INNER JOIN should suffice (e.g. all cases of ged_tbl_document_dv and ged_tbl_document_type). You include tables that you later never really require (e.g. all cases of ged_tbl_document_type), so removing all of them wouldn't impact the result (replace all ...


2

Using an index requires bouncing back and forth between the index and the data. In MyISAM, each index is a BTree sitting in the .MYI file. At the leaf node of the index is a pointer into the .MYD file. (Or, for FIXED, it will be a record number.) Your SELECTs are happy to scan linearly through the index (a BTree is efficient at that), but then for each ...


2

The question as I understand it is two-fold: How to count some value occuring in a separate column How to autogenerate column list based on result of some query While 1 is solvable easily, I am quite certain you won't be able to acheive 2. The solution for 1 could be as following. Given the test tables and data: CREATE TABLE sections( id INT ...


2

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows. SELECT * FROM animals WHERE name IN(SELECT @animal_names); is translated to SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\''); MySQL variable cannot hold table resultset so ...


1

You are using >= which is "greater than or equal to". <= is "less than or equal to". A trick to remember this is to think of the < or > as a mouth that is "eating" the larger number. 12 > 6: twelve is greater than six. 5 < 18: five is less than eighteen. Or just look at the < or > symbol and realize which side is bigger than the ...


1

What you are looking for is in fact possible. What you are looking to do (like many businesses) is build an automatic failover which is often called a hot standby. A hot standby is just a backup slave server that can be switched to either automatically or manually when you need to do maintenance or if your master server fails. MySQL has some great ...


1

The keyword DEFAULT is context sensitive within an INSERT statement only (some systems may support it as well within UPDATE). You can't use DEFAULT within the context of calling a stored procedure. Also, MySQL does not yet support default values for stored procedure parameters either. An alternative can be: delimiter // CREATE PROCEDURE mysql.insertTables ...


1

Indexing can have several purposes: - Access your data faster and to accelerate the execution time of your queries - Define the degree of uniqueness of a given column: Should every field be unique? Are duplicates allowed? When you send a request to your MySQL server, it is first assigned to the "parser" SQL which aims to verify the syntax of your request is ...


1

EXPLAIN tells you the difference. If it shows DEPENDENT SUBQUERY it means that the subquery in IN() is executed once-per-row for the table1, that may be really many times if the table is big. Different MySQL versions may apply different optimizations to the first query to get rid of the DEPENDENT SUBQUERY or at least minimize its performance impact: use ...


1

Have a look at date_format function. You can use it this way: mysql> select date_format(now(), "%Y-%m-%d 00:00:00"); +-----------------------------------------+ | date_format(now(), "%Y-%m-%d 00:00:00") | +-----------------------------------------+ | 2016-04-25 00:00:00 | +-----------------------------------------+ Or, if you want ...


1

You just asked Does: INSERT INTO table1... SELECT .. FROM table2 Also create a lock on table2? Yes, it does create a lock on table2. I wrote about this behavior back on Aug 08, 2014 (See my answer to MySQL consistent nonlocking reads vs. INSERT ... SELECT) In my old post, I mentioned from the MySQL Documentation: By default, InnoDB uses ...


1

Indexes ! Use InnoDB, not MyISAM. Reformulate the query so it does not 'explode' (LEFT JOIN), then 'implode' (GROUP BY)... Something like: SELECT a.article_id AS article_id, article_title, article_content, article_status, article_date_time, ( SELECT SUM(article_hits) FROM phpkb_article_visits WHERE article_id = ...


1

If the checksum part isn't required in the output then give this a try. The arrangement of the ORDER BY statement makes a difference in the output of your data. SELECT sales_people.person_id, sales_people.last_name, sales_people.first_name, sales_region.Region_id, TRIM(sales_region.`name`) AS 'Region Name', year, month, ...


1

Let's do the math. 6500 characters, even in CHARACTER SET utf8mb4 takes no more than 26000 bytes. TEXT has a limit of 64K bytes and needs a hidden 2-byte length field. LONGTEXT has a 4-byte length field. Let's say (for the 'math') that the average row length, including this text, is 3000 bytes. Math... Savings of switching from LONGTEXT to TEXT: 2 ...


1

Simply make these changes: call mysql.insertTables (`Debbie`, `Hopkins`, NULL, NULL, NULL, -- Changed 1, 123456789, 0, NULL, NULL, NULL, 12211985, 12345678, 1) NULL tells the INSERT to invoke the DEFAULTs.


1

By default the DEFINER is the only user that can read stored programs. If DEFINER is left out then the default user that is created the function is used. This is a security feature. Definer and invoker security contexts differ as follows: A stored program or view that executes in definer security context executes with the privileges of the account named ...


1

First backup the database. Sometimes character conversions can give unexpected results. I have had good success using these instructions: SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') INTO OUTFILE '/tmp/altertablesstatements.sql' FROM information_schema.tables WHERE ...


1

Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for subtyping in datamodeling Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping .



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