New answers tagged

0

Your table is about the 95th percentile among MySQL tables. So, it is certainly possible. However, it will take a long time to insert 28M rows one at a time. If practical, do one of these: LOAD DATE INFILE ... -- probably the fastest INSERT ... VALUES (...), (...), ... -- "batch" load 1000 rows at a time; very fast Assuming you use ENGINE=InnoDB (which ...


0

3 problems with that partitioning: BY HASH is useless. SUBPARTITIONing is useless. Hundreds of partitions is inefficient. Stick with only PARTITION BY RANGE(TO_DAYS(rq_date)). The big benefit is in the 'sliding window'. More comments in my partitioning blog. What percentage of rows have t_id <> -1? If more than about 20%, it won't use an index ...


1

The problem in your query is this part: ON DUPLICATE KEY UPDATE -- paramvalue = t.extension ; The alias t is not visible in that part of the query. However, assuming that the two values, the one to be inserted in the params.extension column (12345) and the one checked against the params_extensions column (WHERE t.extension = '12345') are the ...


0

While MySQL does not impose a password length limit, replication does. (I have not been able to find this in the MySQL documentation, but, empirically, it holds.) This is all the less obvious, because the longer password works for login but fails for replication.


-1

I ended up creating the following trigger: DECLARE paramvalue_ VARCHAR(50); DECLARE msg VARCHAR(255); CREATE TEMPORARY TABLE IF NOT EXISTS params_temp(extension integer, type varchar(50), subtype varchar(50), paramvalue varchar(50), active integer, PRIMARY KEY (`extension`,`type`,`subtype`), UNIQUE KEY `extension_type` (`extension`,`type`)); DELETE FROM ...


0

the solution was to add WITH GRANT OPTION; at the end of grant command line. e.g. if i wanted to grant select privilege (+ allow user to grant the same privilege to another user), to a user called user1 for table EMPLOYEE. Here would a an appropriate command GRANT SELECT ON DATABASENAME.EMPLOYEE TO USER1 WITH GRANT OPTION;


1

Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id). ...for a start. You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the ...


0

I ran into this problem when trying to run multiple instances of MySQL, but instead of removing apparmor, I updated the usr.sbin.mysqld file: vim /etc/apparmor.d/usr.sbin.mysqld For example, this is what my file looks like, and once I added the folders that need write permissions, all worked as it should. /usr/sbin/mysqld { capability dac_override, ...


0

If I got it right, you need those cartid which have both specified attributes set to specified values (19, 87) and (20,91). select * from myTable where cartid in ( select t2.cartid from myTable t2 where t2.attributeid=20 and t2.attributevalueid=91 or t2.attributeid=19 and t2.attributevalueid=87 group by t2.cartid having count(t2.recid)=2 ...


4

SQL (Structured Query Language) is a language used to communicate with a database. MySQL is a particular flavour of SQL, which supports a slightly different set of commands to other database types (while still having a lot of similarities, and some commands in common). Despite the name, it's more than just a "query language": it is actually the entire ...


0

I will agree with @A_Horse_With_No_Name and mention that PostgreSQL is a excellent open source DB engine that has grown by leaps and bounds. It's fully ACID compliant out of the box and has a lot of features you can use. If you were deciding just between MariaDB and MySQL though I would go with MariaDB personally. Many large companies such as Wikipedia ...


1

(Assuming InnoDB...) The data and the PRIMARY KEY are in one BTree. Each secondary INDEX (including UNIQUE indexes) is in its separate BTree. An update requires modifying a record in the data BTree. If that also involves updating a column that is in any index it requires, effectively, a DELETE from that BTree plus an INSERT somewhere else in that same ...


0

"Read chunks, but only update a few of them"... SELECT a chunk -- separate transaction, don't care about locking, etc munch on that chunk to figure out the 'few' ids to update BEGIN; SELECT ... WHERE id IN (list of the few) FOR UPDATE; minimal other work UPDATE them; COMMIT; -- plus checks for deadlocks, etc, and restart the code. The idea ...


0

One part could be handled thus: IFNULL(phone_number, 'N/A'); But, really, your formatting requirement is best done in your application code, not SQL.


0

Plan A: Create temp table with num and max(id). 400K rows Chunk that table to do only 1000 deletes at a time; there would be a JOIN in the DELETE to the temp table. More on Chunking for DELETE. Plan B: You need to keep 60% of the table, correct? So, it may be faster to rebuild the table with only the desired rows. CREATE TABLE new LIKE activity; ...


0

There are good reasons ALGORITHM=INPLACE may not be used and switches to ALGORITHM=COPY. REASON #1 In the link you gave in the question, there is a chart that shows the following: Adding a FULLTEXT index trigger a table copy is not supported for online operation Many operations can revert to copy because it says in the notes Although ALGORITHM=INPLACE is ...


0

Why write a trigger for this ? I have two reasons why you should not do that in this instance. REASON #1 Let's assume the following: reference_key is not an auto_increment column is the primary key counter is the column to increment is defined as INT NOT NULL DEFAULT 1 You can write the INSERT as follows INSERT INTO individual_key_log ...


0

I recently had to setup a similar rig but with ubuntu and not centos. I used MySQL cluster and HaProxy in a master-master setup and its been solid as a rock, no down time and haven't experienced any instances were latency has been an issue. I used this guide to help configure the MySQL cluster. MySQL cluster is great in my opionion but see the link bellow ...


0

You can also get what you want from a min() and GROUP BY with no inner select: SELECT l.id, l.time, min(r.time) FROM idtimes l LEFT JOIN idtimes r on (r.id = l.id and r.time > l.time) GROUP BY l.id, l.time; I would almost bet a large sum of money that the optimiser turns this into the same thing as Erwin Smout's answer anyway, and it's debatable ...


0

After reading @user19292's comment in Jan '16 on this old question, I upgraded from 5.7.9 to 5.7.12 and the problem went away.


1

As long as the bottleneck is the amount of RAM you can keep scaling up. MyISAM has some bug related to large key buffer size but InnoDB handles massive buffer pool just fine (even in the TB range). I see you already have 4 buffer pool instances. By increasing the buffer pool it's good to increase this too. The only "issue" you may experience is if you also ...


0

SELECT `integer` FROM mytable WHERE data = 'something' AND `integer` != 1234 ORDER BY `integer` ASC LIMIT 1; Empty table will give you no rows in the resultset. Your last test case will give '4'; I don't know where you get '3'.


1

I've used both MS Access and Toad to create table structure during data file import. Both tools will give you the chance to tweak the column size/types during the import process. Toad will create the table directly on MySQL, when I used MS Access, I imported to a local table, then exported to MySQL via ODBC connection. Big time saver if your input file ...


1

This is all IMHO: •table entry created and updated: date or timestamp? Why? With date isnt it easier to display and sort on the frontend? Use a timestamp - better for multiple mods in a given day. •Entry title - VARCHAR or text? You're not going to lose much with a text over VARCHAR, but I think that VARCHAR is the reasonable choice. •Entry body - I ...


0

Assuming cliente is connected to conta via depositante, then it sounds like you want: SELECT nome_cliente, saldo FROM cliente INNER JOIN depositante ON client.nome_cliente = depositante.nome_cliente INNER JOIN conta ON depositante.numero_conta = conta.numero_conta WHERE conta.saldo >= 200 AND conta.saldo <= 1000;


-1

This simple chown worked for me also, no more crashing process: sudo chown -R mysql:mysql /var/lib/mysql Thankfully, playing with apparmor gets complicated and hard to edit.


0

I faced the similar issue, some days ago, I tried the following methods to solve that issue. Increase the value of innodb_lock_wait_timeout, you can do this by using one of the following two methods, Set it in my.cnf file: [mysqld] innodb_lock_wait_timeout=120 This method required server restart. Set it during run time dynamically: SET GLOBAL ...


1

There seems to be a bug in MySQL (checked up to 5.6.21 on sqlfiddle.com) where the optimizer does not identify the optimal index when it is available. It can be worked around by using force index() hint: SELECT post_modified_gmt FROM ( SELECT @rownum:=@rownum+1 rownum, wp_posts.post_modified_gmt FROM (SELECT @rownum:=0) r, wp_posts FORCE ...


0

Your conditions are ranges on 3 different columns. The optimizer can only use one range scan per index. That means that the selectivity of the first column in the index is the only one which can quickly prune the rows to check, the two other conditions need to scan all these rows selected by the first one and check the conditions one by one. If you see using ...


0

One INSERT per transaction, containing 50 rows, with "consecutive" (or "nearby") PRIMARY KEY values will be optimal, regardless of one or many connections. Using MD5, UUID, etc, is a disaster for huge tables. When the table is too big to be fully cached in RAM, the inserts become I/O bound. In contrast, 50 'adjacent' records will cause almost no I/O. A ...


0

The Master 'pushes' changes to the TCP/IP port at the same time that it writes them to the binlog. The Slave keeps the TCP/IP port open and receives the changes 'instantly'. It writes them to its "relay log" via the "IO thread". The "SQL thread" 'immediately' sees the change and performs it. While simple changes happen on the Slave so fast that it is ...


0

This will be a lot faster, but has flaws: SELECT id, ( SELECT count(*) FROM tickets WHERE order_id = o.id ) as attached_tickets FROM orders AS o LIMIT 1; It will need INDEX(order_id) on tickets. Which order.id do you want? There is no ORDER BY to say which one LIMIT 1 will pick. This may not ...


0

I suspect you are 'out of luck'. Was there some reason not to have 'primary' part of the cluster? Look into pt-table-checksum and pt-table-sync. These may be a way to incrementally 'fix' the Slave to get it synced with Primary.


0

Don't "over-normalize". The table stats, items, and skills are just normalizations of short, unique, names. Consider just having the names in the other tables. Or... have just one table for id<->name mapping, perhaps with an ENUM to say which of stat/item/skill/etc it is. Based on your Schema, what queries will you be performing? Do you ever need to ...


1

This is my interpretation of your requirement.As a simple query, this will return what you want, however you will probably want to try other options to make it perform. I don't use MySQL, but in SQL Server I would look at trying CROSS APPLIES or some sort of grouping option. This query will return all the dates on which the minimum or maximum occurred. You ...


0

Instead of SELECT *, try selecting the fields you need (unless you need all of them!): SELECT id, magG, ... FROM Try to have the fields you are selecting in the used index. This way you will have a covering index, so the data will be read once only from the index If you need many fields to be returned, you may first select the IDs using the query you ...


2

In a word, no. Why? MySQL Documentation on bind_address says right in the beginning Command-Line Format --bind-address=addr System Variable (>= 5.6.1) Name bind_address Variable Scope Global Dynamic Variable No Permitted Values (<= 5.6.5) Type string Default 0.0.0.0 Permitted Values (>= 5.6.6) Type string Default * It's a global ...


1

According to MySQL documentation, in order to change a password of a user, bmcrae must have UPDATE privilege for the mysql database. To check the privileges of the user bmcrae, run the following command: show grants for 'bmcrae'@'hostname'; OR Show grants for bmcrae; And check whether, the user bmcrae has UPDATE privilege on mysql database. If the ...


0

Here are the answers to each of your question 1 How the copying of binary log works? I wrote a detailed list of the steps taken by MySQL Replication in my answer to Is MySQL Replication Affected by a High-Latency Interconnect?. Someone else answered that same post, but from the point of view of DB Client 2 Is it first writes to master binary log ...


0

To answer my own question, the "fix" is to change binlog_format to STATEMENT. It was previously MIXED. Not entirely sure of the reason behind this - MySQL should be intelligent when using MIXED mode about what is replicated as statement and what is replicated as row.


0

Performance is probably being hurt by all the COMMITs and single-row actions. The following should speed it up by order(s) of magnitude. Do a batch of 100 rows at a time A single multi-row INSERT for the the extra table. Use BEGIN...COMMIT around each batch. Since you need to pull the data out, encrypt it, and then put it back in, here are some more ...


0

INSERT and INSERT IGNORE add a new row unless the row specifies a duplicate for some PRIMARY or UNIQUE key on the table. The only difference is whether the "duplicate key" is an error or ignored. Do you want one row per user? Or can the user provide many passwords? In the former case, PRIMARY KEY(username); in the latter, PRIMARY KEY(username, password). ...


0

Any attempt to do this delete in a single query is likely to take hours, maybe days. If that is OK, then the code by @ypercube is probably good enough. Otherwise, I would recommend tackling it in stages. First find the items to delete. This will be relatively fast and non-invasive. CREATE TEMPORARY TABLE t ( PRIMARY KEY(item_id) ) SELECT ...


0

SELECT g(f(date)) AS 'week of', AVG(...) AS 'moving average' FROM tbl WHERE date > CURDATE() - INTERVAL 52 WEEK GROUP BY f(date) Where f() is a function that turns a date (or datetime or timestamp) into a number. Something like FLOOR((TO_DAYS(date) - 3) / 7), where the 3 is chosen based on what day a week "starts" on. (I doubt if ...


0

Dual-Master, where you write to both masters, is full of problems. You need to carefully set the two auto_increment_* "variables". You need to make sure users cannot insert the same PRIMARY or UNIQUE values into different masters. If 'East' goes down, the Slave is hard to reconnect. If a hurricane hits the East, you lose both the master and slave there. ...


0

Consider adding a new index first, then DROP the old index. DROP should be a lot faster than ADD. Or, if that fails, don't drop the old index.


1

The heavy hitters have Load balancer --> one of a set of web servers --> load balancer --> one of a set of MySQL slaves. That is, you are hitting at least 4 boxes. And none of them is "heavy" on CPU. Don't fear using dedicated servers. If the CPU is high in MySQL, then you are missing an index, or don't have a "composite index" or have a poorly written ...


0

Decrease the settings for the web server and Java. Increase max_connections for MySQL. If you are using Apache, MaxClients should be less than max_connections. You probably can live with small number around 10-20.


0

You have tradeoffs to consider when deciding on the design. Which do you want to optimize for: Insert new item Revise item Fetch "current" item Fetch some old revision Fetch all revisions If one of those actions dominates, then design for it, and kludge for the rest. Also, do revisions need extra columns, such as revisor, revision_date, ...


0

No. Instead: INSERT ... SELECT LAST_INSERT_ID(); LAST_INSERT_ID() is session-specific, so there is never any confusion with other threads also inserting into the same table. Also, there is no need for BEGIN ... COMMIT for this pair of statements. (You may need it if there are other statements.)



Top 50 recent answers are included