Hot answers tagged

7

To filter after aggregation, you can use a HAVING clause. SELECT order_id, sum(quantity) AS qty FROM order_product GROUP BY order_id HAVING sum(quantity) > 5;


6

You seem to be suffering from the Premature Optimization™ syndrome. I'm sure a database used for storing files requires completely different optimisation to run efficiently in comparison to one that stores largely string values. Why are you sure? Have you done any testing to confirm that? What exactly would you set up differently for these two use cases? ...


4

I've seen it mentioned in blogs and tutorials that a weak entity "doesn't have a primary key," but this is misleading. You are correct that every table needs a primary key. This is necessary for the table to qualify as a relation. Every relation has one or more candidate keys, and one of the candidate keys is chosen as the table's primary key. The ...


4

For large databases, recovery is faster when the data dir is backed up as files. When an SQL dump is restored, all the data is put back by using INSERT statements, which involves parsing the queries, converting data to the storage format, updating indexes etc. When restoring the raw database files, one simply needs to copy the files and start the server.


3

From MySQL docs, INSERT ... ON DUPLICATE KEY UPDATE Statement: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. You don't have to fix anything. The existing row was updated, affected-rows returned 2.


3

Your question is likely to be closed, unfortunately, because it's the type of question not well-fit for a site like this. That being said, the database systems you listed are server based, and assumptively your question is about which database system your client side mobile app should use. A popular choice from a relational standpoint is SQLite because of ...


3

Rolando's composite index is the "perfect" solution to the question. Or is it? Sure, it made that one query run 4 times as fast. I would argue that 1 second is still "too long". To discuss that, we need to understand "why" that query is necessary, and how often it is run. Also, let's look at the side effects of having that ...


3

The reason the status index makes it go slower is this: The value of status is read from the index The value of amount must be looked up in the table This means having to read from the index and the table separately per index entry. In your case, doing a full table scan, where both the status and amount values are, would be better without using an index. ...


3

Week numbers are controlled by %u, %U, %v and %V specifiers of the date_format format string. Returned value is in the range 0(1)-53. For grouping by week you need a date format like %Y-%u. %w specifier is standing for daynumber within the week. Sun = 0|7 Mon = 1 Tue = 2 and so forth.


3

Sometimes a "Server" (machine) is called a "Database" (aka "schema"). I assume you are not making that terminology error. There are more disadvantages of segregating Databases across Servers than advantages. These days, Servers come pretty much in "one configuration fits all"; all dimensions (CPU cores, RAM, disk, etc)...


2

Its recommend to stop copying off old blogs (and perhaps StackOverflow/DBAExchange answers too) to do any authentication relates changes in MySQL and MariaDB, the mechanisms are being updated and no longer apply. Always check the official documentation. Use SET PASSWORD or ALTER USER to manage user authentication. Also modifying a user/host component of the ...


2

Turns out this was a dumb question. The DBMS won't allow an autoincrementing column without an index. It throws Error 1075 upon attempts to do this. Because of other filter patterns not mentioned in the question I'm going with these indexes. PRIMARY KEY (post_id, meta_key, meta_id) UNIQUE INDEX meta_id (meta_id) INDEX meta_key (meta_key, meta_value(32), ...


2

You want to change the PRIMARY KEY from PRIMARY KEY (meta_id) to PRIMARY KEY (post_id, meta_key, meta_id) This is allowed in InnoDB engine but it requires that you have an index (not necessarily UNIQUE on (meta_id) or an index with more columns and meta_id as the first column. So, to answer the last question: Or can I omit that index to save a little ...


2

One approach is to look at weeks, ignoring year and month boundaries: w = FLOOR((TO_DAYS(date_col) - n) / 7) n is some number between 0 and 6, based on that day of the week is "first". w is a week number. Then work backward to find which date that week starts on: FROM_DAYS(7 * w + n) Example: mysql> SET @n := 2; -- for Monday being the "...


2

MySQL doesn't support one query against multiple instances. Your application would need a separate connection to each MySQL instance, and then you would write code to execute a separate query on each of these connections, and then combine the results by appending one result set to the other using code. I posted an answer to a related question on Stack ...


2

You could try with: select referred_by, referred_by_count, @rank := @rank+1 from ( select referred_by, count(referred_by) as referred_by_count from referrals r group by referred_by order by referred_by_count DESC, referred_by ) as t cross join ( select @Rank := 0 ) o order by referred_by_count desc, referred_by; But I don't ...


2

Introduction: You have two possibilities to do what you want - one works for versions of MySQL from 5.5 (uses aggregates) and upwards and the other works for MySQL 8 and up (uses window functions). All of the code below is available on the fiddle here. NOTE: The fiddle is for MySQL version 8. If you wish to run versions 5.5 (or 5.6 or 5.7), please change the ...


2

Try this: SELECT DATE_FORMAT(`history_out`.date_mod, '%Y-%m-%d') AS "thedate", `history_out`.group, `history_out`.ticket AS "ticketOut", cast(NULL as unsigned int) AS "ticketIn", `history_out`.id FROM history `history_out` WHERE (`history_out`.prev_group IN (53042)) AND (`history_out`.date_mod >= @s) UNION ALL ...


2

If this is a web application, there is probably no problem. A web page connects to the database, does its thing, then disconnects. If you have a thousand users running web pages, you might not actually have more than a couple of dozen actually running at the same time. They will (or at least should) come and go so fast that you don't need a big ...


2

You probably want to use Differential or Incremental backups more frequently than your Full backups. Differential backups only backup the changes to your database since the last Full backup ran: Performing a series of differential backups. Each differential backups includes all the changes made to the data since the last full backup was performed. To ...


2

It seems that your server is using sql_mode=''. OVHCloud MySQL server has sql_mode ANSI,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_ALL_TABLES. ANSI activates the ANSI_QUOTES mode which interprets double quotes (") as fields name. Your jaro winkler stored procedure uses double quotes to define strings string=&...


2

I suspect you want the ROW_COUNT() function UPDATE table SET table.name = "Anthony" WHERE table.id = 3; SELECT CASE WHEN ROW_COUNT() > 0 THEN 1 ELSE 0 END AS updated;


2

I recommend you enable log_slave_updates on both of your masters. Then they will add the replicated changed to their own binary log, so both instances' binary logs will have all changes. Then you don't need multi-master replication. See https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#log_slave_updates


1

Rob's answer is great for most situations, but Pipe Viewer doesn't work well in use cases where a tty isn't available, like when monitoring a mysql docker container's initialization output or when you want to log the progress to a file. Pipe Monitor (github) is an alternative designed to output updates to a log stream via STDERR. Disclaimer: I am the author. ...


1

There is essentially no reason to prefix tables with the database they are in. Ditto for prefixing column names with the table names. Keep in mind that these syntaxes can give you an equal amount of clutter, without threatening the max name lengths: dbname.tablename tablename.columnname "Everybody" recognizes genus_species; I suggest that is ...


1

The structure seems reasonable. The datatypes have some issues. "2-1/2 bathrooms" in an INT? BIGINT is excessive for most cases. INT won't work for latitude and longitude; consider FLOAT as being quite adequate for buildings. What will the "slugs" be used for? Location -- Some things (eg "city") are normalized; other things ...


1

You tagged it with MySQL, so I will answer it for that. SELECT `week`, a.sd / b.id AS "Ratio" FROM ( SELECT `week`, SUM(...) AS sd FROM .. JOIN .. ) AS a JOIN ( SELECT `week`, SUM(...) AS id FROM .. JOIN .. ) AS b USING(`week`) (I don't know about Redshift.)


1

Another cause for the "Error 13 Can't Stat " is that the target file is not within the /var/lib/mysql directory hierarchy. Apparently the mysql server approaches the file system quite differently than a normal user. I was trying to load/update a table with: LOAD DATA INFILE '/home/pi/sites.csv' and MySQL (MariaDB actually) would barf with Error ...


1

If you change the id column to AUTO_INCREMENT NOT NULL it will again a unique identifier. AUTO_INCREMENT columns need to be part of a primary or unique key and since you don't have one, this is recommended: alter table spark_car_brand modify id int not null auto_increment primary key;


1

After checking what seemed to be thousands answers on Stack Overflow I found a work-around for me: let's imagine we store a condition in a boolean variable called @DEBUG. SET @DEBUG=1; Then, dependently on its value, we may execute SELECT query. SELECT v FROM some_table, ( SELECT @DEBUG as DEBUG ) AS DBG WHERE DEBUG=1; The good thing is, that this ...


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