Hot answers tagged

45

use --no-tablespaces see mysqldump-Documentation mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 8.0.21) PROCESS if the --no-tablespaces option is not used. Certain options might require other privileges ...


32

Instead of '2020-04-30' = date_add(op.dataDate, INTERVAL 14 DAY); Use op.dataDate = date_sub('2020-04-30, INTERVAL 14 DAY); Your first statement will be interpreted as "add 14 days to all dataDate and return when that is 2020-04-30." This will require a full scan of the table. The second statement will evaluate to: "return records where the ...


20

tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command. I experienced the same issue on some of my machines. Not all at once, not on all commands, not on all users. Why not all at once? Turns out this is a breaking change ...


6

The formulation is the main problem. After rewriting the query, the index you have can be used for the entire WHERE clause. SELECT * FROM op.prices WHERE ticker = 'AAPL' AND expDate >= '2020-04-30' AND expDate < '2020-04-30' + INTERVAL 3 MONTH AND dataDate = '2020-04-30' - INTERVAL 14 DAY See Sargeable in Wikipedia. ...


6

Look at it this way... There is a lot of overhead in each SELECT: Round trip between client and server Allocate a thread to work on the query Parse the SQL Optimize it Perform it (The meat!) Send the results back In some experiments, I have seen the "overhead" be 90% of the work. 5 queries takes nearly 5 times the overhead.


6

This is common comparison operators. When comparison is performed then some boolean result is produced. It may be TRUE, FALSE or NULL. These 3 values are related as NULL < FALSE < TRUE This relation is used for sorting. For example: select * from x order by a<b; If a and/or b is NULL then the result of ordering expression (comparison result) is ...


5

Looking at the MySQL Documentation, the glossary indicates this about Covering Indexes: An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than ...


5

Access to a row using a non-clustered index, which idx_name is, requires extra random I/O: b-tree lookup finds the clustered (primary key) index value, then you need to go and fetch the actual row from the clustered index. The alternative is a sequential scan of the clustered index itself, which does not incur that extra I/O cost and is also simply more ...


5

This is a common issue with functional transformations of any type not only dates. To avoid this issue just move the transformation to the constant side of comparison. Instead of WHERE CONVERT_TZ(TBL.completed_date, timezone1, timezone2) BETWEEN date1 AND date2 use the next syntax: (notice the reverse transformation of the TZs) WHERE TBL....


5

None of these queries should work at all - they are an abomination. Writing SQL where you can query whether an INTEGER is less than, equal to, or greater than a CHAR(n)/VARCHAR(n)/TEXT/<any_character_field> is completely meaningless! What a "REAL" database server (PostgreSQL in this case) does with such horrors is shown here (sample output ...


5

Unless MySQL does something strange, using only ASCII characters (i.e. only values 0 - 127) should be the exact same encoding, and hence the exact same size, between ASCII, UTF-8, and many other 8-bit code pages. It's only when you hit code points above 127 (or 0x7F) that UTF-8 starts to require additional space (though technically speaking, standard ASCII ...


5

Why storing data as a string a problem : Storing multiple datums as (in this case comma separated) strings is bad practice because: - First reason: It breaches Codd's second rule (called the "Guaranteed Access Rule") which states that Each and every datum (atomic value) in a relational data base is guaranteed to be logically accessible by resorting to a ...


5

To pick MySQL or MariaDB, right click on the wampmanager icon in the system tray and you should see this this menu Please Select Components like I did please check the 2nd Screenshot


5

You never set your variable @Watermarkvalue to anything, so the value of the variable is NULL. So this line: Set @tabName ='SELECT '+ @watermarkValue +'= MAX([LOAD_DATE]) FROM '+ @tableName Will always be NULL. You want to fill your variable, so think you need something like this: (change to correct datatype) ALTER PROCEDURE [dbo].[usp_UpdateWatermark] ...


5

Because it converts the datetimes to strings and does the comparisons with the string values. And '2019-12-19 00:00:00' <= '2019-12-19' is false in text comparison.


4

A port number is not necessary to use DBMS. Individual programs could write directly to the DBMS's files. But the rules of a DBMS have become very complex, and an immense amount of logic is needed to respect those rules and to respect the actions of other programs using the DB. I've known that situation with Oracle, you could compile your programs to ...


4

The "meta" commands (SHOW...) are generally not designed for performance. The presumption is that you rarely, if ever, need to run them. And when you do, it is more of a "one-time" operation. Please migrate from MyISAM to InnoDB. (This will probably have no effect on the Question at hand, but is important for many other reasons.) ...


4

Please note while @Kondybas' answer will produce expected results on correct data sets it will NOT restrict the number of iterations. All rows will still be read but only 2 of them will be returned. That means: Drop in performance You will NOT avoid infinite loops that way! If there is a loop in the tree of pid-id links in the table data due to some ...


4

This is an extremely broad question, but anyway: Typically this is solved using connection pooling. To serve tens of thousands (or millions) of concurrent users, you typically need way less concurrent connections on the database ("hundreds"). This is especially true for web-applications. Web-Applications (where you get that number of concurrent ...


4

Something along these lines. I need to link to the same table more than once so I need to give unambiguous table aliases. I favor minimal character aliases but you'll be needing to reference the doctor person, DP, or the patient person, PP. SELECT DP.Name AS DOCTORNAME , PP.Name AS PATIENTNAME FROM Register AS R INNER JOIN Doctor AS D ...


4

PROPOSED SOLUTION DUMPFILE=mydump.sql grep "^USE " ${DUMPFILE} | sed 's/;//' | sed 's/`//g' | awk '{print $2}' EXAMPLE # grep "^USE " dump.sql | sed 's/;//' | sed 's/`//g' | awk '{print $2}' CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine ...


4

Yes, you can (ab)use NULL for this. Unique indexes allow multiple rows where the unique field is NULL. You just have to make sure you make the field nullable. Note that this only applies to unique keys, not primary keys - primary keys cannot be nullable.


4

To generating random password for user while creating new one you can use : mysql> CREATE USER ran_user@localhost IDENTIFIED BY RANDOM PASSWORD; +----------+-----------+----------------------+ | user | host | generated password | +----------+-----------+----------------------+ | ran_user | localhost | UN:5_z05J._*VtU3K%Qu | +----------+-------...


4

For MySQL to handle your query efficiently, you need to create a compound index. Without an index, MySQL must examine every row in your table to count the ones you want. That means it has to read every row from the SSD. That takes time. Your filtering criteria are ... WHERE column_4 = 1 AND column_8 is null AND column_3 > CURDATE() - INTERVAL 30 ...


4

Instead, do CREATE TABLE new LIKE real; load new data into `new` by whatever means RENAME TABLE real TO old, new TO real; DROP TABLE old; Notes: The second step is the only slow step. The RENAME is atomic. You can continue to read the table during the process. Do not use any transactions or table locks.


4

I guess you can use CHECK constraint ALTER TABLE foo ADD CONSTRAINT email1_is_not_email2_CHK CHECK (email1 <> email2) ;


4

I have checked out mysql-5.4.46, and searched client/mysqldump.c for "SET character_set_client". It finds [:~/Source/mysql-server/client]↥ 9c3a49ec84b* ± grep 'SET character_set_client' mysqldump.c "/*!50003 SET character_set_client = %s */ %s\n" "/*!50003 SET character_set_client = @saved_cs_client */ %s\n" "SET ...


4

Use JOINs, not IN ( SELECT ...) Follow the tips for many:many mappings (video_tags) here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table The table is called videos; why is there a column is_video? Consider using FULLTEXT instead of LIKE with wildcards. If it will work, it will be much faster when a title is given. Consider ...


3

Using phpMyAdmin run a SQL query against the table that you want a column of data from and select only that column (something along the lines of SELECT cas_number FROM analytes). Scroll to the bottom of the results and click on Export. The only thing that will be exported is the column of data that was selected.


3

In MySQL 5.7, assuming you're using mostly INNODB tables, you can see how much memory is currently being used by running this query: SHOW ENGINE INNODB STATUS You can see the maximum amount of memory your DB will ever use by running this query: SELECT ( @@key_buffer_size + @@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@...


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