Hot answers tagged

23

SQL doesn't provide a good way of doing this because it's probably not a good table design to have N columns that might have values in the same domain. This is potentially a case of "repeating groups" like if you have columns phone1, phone2, phone3, ... phoneN. If you have a number of columns that have the same logical domain of values, it could be a case ...


14

The closer you can get is this, using IN: SELECT * FROM table WHERE 'val' IN (col1, col2, ..., colN) ; You still have to write all the columns you want to check. And it's not any different than the OR expression you have, not in performance or otherwise. This is just a different, equivalent way to write the expression, with a bit fewer characters.


12

Actually exists now for Maria DB 10.219 ALTER TABLE test ADD COLUMN IF NOT EXISTS column_a VARCHAR(255); Bonus, it works for MODIFY as well ALTER TABLE test MODIFY IF EXISTS column_a VARCHAR(255);


11

We can force SQL Server to do exactly this and see what actually happens. R has 1000 tuples and 100 page-accesses = 10 tuples/page = 806 bytes/tuple. S has 50 tuples and 25 page-accesses = 2 tuples/page = 4030 bytes/tuple. These are the tables: drop table if exists dbo.R; drop table if exists dbo.S; go create table dbo.R(n int, filler char(785) not null ...


11

No, you can't. You have overwritten the database so your best course of action is to restore from the latest backup. If that is 4 months old, that's it, you lost 4 months of data. You should take backups more often and also before any critical operation (like this one) that has the chance of destroying data if it goes wrong. Addendum: Before you go and ...


11

Do all the famous Relational DBMSes like PostgreSQL, Oracle, SQL server, SQLite, store it the same way? by making a clustered Index (b+tree) based on primary key and store data on the leafs of tree? No, not all. Lets take them one by one: MySQL. MySQL has several "engines" and depending on what engine a table is defined to use, the storage is: InnoDB: ...


10

Do databases support multiple transactions simultaneously on a single database connection from client? For SQL Server, no. If not, why not? (as multiplexing would save on resource overhead per connection) It would seriously complicate the network protocol, which has to be implemented on multiple client platforms, creating a possible source of bugs and ...


7

In MySQL 8 it is the option disable_log_bin which has to provided without any parameter in the my.cnf file in the [mysqld] section. [mysqld] disable_log_bin some-other-option = any-value


6

You want to check in code is NOT IN that list of codes. Your syntax needs correction. Specifically, to use AND code NOT IN (...): SELECT code FROM promo_codes WHERE promo_id = 1 AND code NOT IN (SELECT code FROM promo_codes WHERE promo_id = 2) ; If code is nullable, you should add one more check in the subquery to avoid NULL issues with NOT IN: ...


6

You should always store dates using the DATE type. It should use less storage and will generally be faster when searching than if you stored the same date as a VARCHAR. Additionally, if you stored it as VARCHAR then the format is baked into your data, which will make it harder for you to use different date formats in the presentation layer. For example, ...


6

The answer: as ypercube said, the solution is: adding parentheses around the OR conditions like so: SELECT name,url FROM additions WHERE enabled = 1 AND picture = 1 AND (name LIKE '%value%' OR tags LIKE '%value%' OR subtext LIKE '%value%'); With the parentheses, AND will take precedence and the entire section in the LIKE value comparison will have to ...


6

The truth is more involved than what you realize. It is true that the outer input of the join will require 1000 logical reads but only if the join key is unique. If it is not, the optimizer can pre sort it and fetch multiple rows at and match all of them at once. As for the inner loop, you are assuming a full scan per iteration. The optimizer will typically ...


6

Gap locks are necessary under the read committed (RC) isolation level to prevent potential integrity violations due to concurrent inserts -- this is what the documentation statement Gap locking is only used for foreign-key constraint checking and duplicate-key checking. implies. Suppose transaction T1, with the RC isolation, updates the value of a ...


6

The issue is a Mysql bug (https://bugs.mysql.com/bug.php?id=94185 ) Anyway, some tuning mitigated the issue and stopped the number of open files to grow. Specifically, we set: innodb_lru_scan_depth=100 innodb_flushing_avg_loops=5 thread_cache_size=216 Fixed as of the upcoming 8.0.16 release, and here's the changelog entry: Static thread local variables ...


6

I rant against 255 occasionally. Sure, there used to be some reasons for '255', but many are no longer valid, and even counter-productive. In MySQL, there are reasons to stop at 191, 255, 767, 64K, and probably other values. Some depend on Engine, some on CHARACTER SET, etc. A VARCHAR is stored as a 1- or 2-byte length plus enough bytes for the current ...


6

For the specific query that gets only one row from the table: SELECT * FROM message WHERE conversation_id = @some_id ORDER BY add_date ASC LIMIT 1 ; you only need a common (btree) index on (conversation_id, add_date). The query will need to do only an index seek (which will find the PK value) and then a seek on the table. The operations should be both very ...


5

In general, you want to utilize as much RAM as possible (leaving enough for the O/S, etc), since that means data is in-memory instead of being on disk. For a CRM system serving 100 people, you may find 16GB of RAM dedicated to the server is not enough. My phone has 8GB of RAM, for instance, and it's just a phone. In-memory access times for data is a ...


5

The associative entity idea is a good way to handle this, but it has a drawback that you might want to consider. Something that hasn't been addressed in some other answers is how you're going to maintain all of this data. It's going to be a lot of work, particularly if you want to be able to get from one word to any of its synonyms (or antonyms) without ...


5

Don't delete more than 1000 at a time. All the rows being deleted are saved in case of a crash (or reboot) so that they can be restored. (cf Atomicity.) This also explains why the table was non-responsive after the reboot. Index updates are delayed (cf Change Buffering). This may explain why subsequent deletes got slower -- the updates to the indexes ...


5

"Very few syntax changes" -- Excuse my while I laugh! SQL Server has a lot of features that MySQL does not have. Any use of such is not just a syntax change, but possibly a query rewrite. TOP 10 --> LIMIT 10 -- but only approximately. ROW_NUMBER --> does not exist SEQUENCE --> AUTO_INCREMENT -- but only approximately dbname.dbo.tablename --> dbname....


5

You're looking for GROUP_CONCAT: SELECT weight, GROUP_CONCAT(name SEPARATOR ' ') as name_list FROM Rider GROUP BY weight


5

First of all, MySQL has been supporting CTEs since relatively recently, starting from version 8.0 (as has been mentioned by others). The syntax error you are getting seems to indicate that you are using an older version. Therefore, your database server requires an upgrade1. Apart from that, there are issues with your CREATE VIEW statement that need to be ...


5

TL;DR - eventually figured out how to do this in a single pass - that answer's at the end. I left the original answer in place in order for people here to maybe learn from my own (creaking ;-) ) thought processes! Update: there's an alternative answer provided below. This proved to be quite tricky. To solve this, I did the following (see fiddle here) I ...


5

If this gives you an error: insert into mytable values('2019-03-31 02:06:29') But this doesn't: insert into mytable values('2019-03-31 03:06:29') You are most likely using a EU-like timezone, in particular, CET (I strongly suggest not to use tz on databases, and just use UTC, it is just easier). EU (and other countries) changed the clocks from 2 -> 3 am ...


5

A tinyint column can hold values from 0 to 255 (if it is defined as unsigned) or -128 to +127 (if it is signed). The (1) in tinyint(1) is only for some formatting options and generally ignored. You could create it as tinyint(100) and it wouldn't make a difference. Regarding the TRUE or FALSE, any int (int, tinyint, smallint, bigint) value can be used as (or ...


5

WHERE CinemaID = ? ORDER BY Showtime -- and WHERE CinemaID = ? AND (Showtime BETWEEN [date 12:00 AM] AND [date 12:00 PM]) -- need: INDEX(CinemaID, Showtime) WHERE CinemaID = ? AND MovieID = ? ORDER BY Showtime -- needs: INDEX(CinemaID, MovieId, Showtime) -- or INDEX(MovieId, CinemaID, Showtime) Assuming that the the triple (MovieId, CinemaID, Showtime) ...


5

Composite Primary Key I would define the primary key as a composite key of (CinemaID, MovieID, Showtime). These 3 columns uniquely identify each row, and so having a separate ID column is not necessary. Composite (Secondary) Index With this PK, the only additional index you will need for your queries is (CinemaID, Showtime). Why these indexes? A good ...


5

INDEX(result, event_type, event_timestamp) obviates the need for INDEX(result) and INDEX(result, event_type). Blindly using (255) hurts indexes and queries. Trim back to realistic limits. Splitting up the table the way you suggested helps nothing and hurts most queries. In particular, then you would not be able to effectively use the multiple indexes, ...


5

Yes, you can issue a DELETE statement before your INSERT. If you do it inside a transaction, they will be essentially one operation, they will either fail or succeed together (assuming that the table uses a transactional engine, like InnoDB): START TRANSACTION ; DELETE FROM payment_transactions_daily_facts WHERE date BETWEEN '2018-...


5

Oracle It is a little-known fact, that in Oracle, one can have 0, 1 or even more sessions in the very same TCP connection. This is discussed in the book Expert Oracle Database Architecture (ISBN 978-1-4302-6299-2, Authors: Kyte, Thomas, Kuhn, Darl) in Chapter 5 - Oracle Processes. https://books.google.com/books?id=NG4RpD8aLEIC&pg=PA170 Connections ...


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