Tag Info

Hot answers tagged

4

You probably have lots of large transactions or one really big transaction. Take a look at Vadim Tkachenko's Pictorial Representation of InnoDB If you look inside the system table (ibdata1), you see rollback segments and undo logs. When a rollback segment rolls back a transaction, it has to use the MVCC information it stockpiled in the undo space. ...


4

The Rick James answer is good (I vote it up). You can try the following test. Create table and insert some values: create table table_x ( id int unsigned not NULL, status varchar(100) not NULL, index(id) ) engine=INNODB; insert into table_x values (123, 'PENDING'), (321, 'PENDING'); Then start two mysql sessions into two separated terminal. I ...


4

MySQL's optimizer looks only at what indexes are available. There is an exception in 5.6: If you have a subqueries such as FROM ( SELECT ... ) JOIN ( SELECT ... ) ..., there are no indexes on the temporary tables that are created. This used to lead to terrible performance. Now, the optimizer will try out various indexes, and create the best one for the ...


3

MySQL Documentation has SQL_NO_CACHE option: Two query cache-related options may be specified in SELECT statements: SQL_CACHE The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND. SQL_NO_CACHE The server does not use the query cache. It neither checks the query cache ...


3

"Will this query always be indexed AND fast as the rows in the table grow?" Definitely not necessarily. If there are many records and/or the value of deleted is skewed in some way (i.e. it's (nearly) always one particular value, or split 50-50), then the optimiser may decide to ignore a given index. Googling "why does mysql not use an index" gives 3.5M ...


2

The whole point of using DATE as a type is so the database can efficiently query the data. It's the same reason you store a number as an INT and not a VARCHAR - so the engine can make intelligent decisions. If you use the LIKE operator on a date, you lose the benefits of having chosen the correct data type. Using MONTH(birthday) allows MySQL to grab the ...


2

MySQL has no such feature. It is left to the user to "normalize" the data either for avoiding having to update multiple spots and/or for saving space. In your example, it is generally not practical to do such with first/last name. But it may be advisable for "locations". It is not practical to dedup names (for example) because the payoff is poor. With ...


2

MySQL can handle at least some aspects of such. At 15TB, disk I/O is the number one problem! Both on INSERT and SELECT. Indexes are BTree structured. This gives "close to instantaneous" fetching of one row, given the index. It also gives rapid scanning in the BTree order, forward or backward. You can have multiple unrelated indexes, but it is ...


2

Check out Drupal and RDF. Check out what StackExhange (etc) uses. And study how GIT works. I recommend keeping the current value in the primary table, and put the history in other table(s). That is, plan for efficient access to the current value, at the expense of history. If you are talking about "articles" of, say, 1K characters, then compress them. ...


1

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point: ...


1

This is called Write masters with Read slaves. Check out MySQL replication. You'll find an interesting document here (check out the slides) - but you probably won't be needing the level of availability of Booking.com, but hey, who knows? :-). There are many different replication/HA topologies, but this will give you a start. Also, see here (Andrew Morgan ...


1

I made some attempts to dig into this issue, here are the results. When you set a connection charset (i.e. SET NAMES utf8) MySQL transparently handle encoding conversion for you. For instance if I insert a à (\xE0 in latin1 \xC3A0 in utf8) in a latin1 table using a UTF8 connection it reads the UTF 8 value and store it in table as \xE0 mysql> SELECT ...


1

Thanks for the minimal test case. Your TEXT column is declared to be latin1; there is no "dotless i", in latin1, so it was converted from utf8's hex C4B1 to ? during INSERT. Change the table declaration, preferably to utf8. Also remove SET CHARACTER SET utf8; -- It seems to hurt! If you wish to complain about the "silent" conversion, file a bug with ...


1

Plan A: If dull and shiny don't change, leave both in effect?... GRANT ... TO dbuser@dull ...; GRANT ... TO dbuser@shiny ...; But, beware, this could leave the "production" system accessible to "QA". (Assuming that is really what you are doing.) Plan B: A simple script (after transferring): UPDATE mysql.user SET host = 'shiny' WHERE host = 'dull'; ...


1

(More of a bunch of comments, plus a redundant answer.) Your two examples are not identical -- one is limited to 2015; the other is not. WHERE birthday BETWEEN '2015-04-01' AND '2015-04-01' + INTERVAL 1 MONTH would be able to use INDEX(birthday), but that only covers those who will be born next month. Even if you had a mnth TINYINT UNSIGNED COMMENT ...


1

I go for SELECT * FROM customers WHERE MONTH(birthday) = 4; since its the common way to select by month


1

(sequel CHAR) CONCAT(@sequel) @variables are totally independent of non-@ variables. Two fixes needed: SET @SQL = CONCAT(@sequel); --> SET @SQL = sequel; (sequel CHAR) --> (sequel VARCHAR(999))


1

I think a GUI tool like SQLyog can help you in your case. The tools like Import External Data, Sync, Scheduling a job with the required query or even a time based scheduling will let you do what you are asking for. Also you can run batch scripts and execute queries as you want in the case of XML files. I would suggest you to try it once.


1

A composite index on position (date, domain_id, position) and on userdomainexpression (userdomain_id, expression_id) would help I think. The join to userdomain is not needed. Assuming foreign keys exist, the join to expression could be removed as well. The LEFT joins are actually (equivalent to) INNER joins (due to the WHERE conditions. You could try to ...


1

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.) More than half the users will have only one link. Some users will each have over 100K links. More than half the sites will have only one page. Some sites will each have over 100K pages. What does it mean? It means that any form of indexing, compression, etc, needs ...


1

In almost every use case, InnoDB is preferred over MyISAM. So, yes. To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed. Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased. In MyISAM, an UPDATE blocks all other operations on the table. In InnoDB, it ...


1

I wish the creator of that tool would toss that line. It unnecessarily scares people. And the number it gives is both too small and too large. But, in your case, it there is a real danger. If you had just 3 queries running that were nasty enough, they could be asking for 2.4GB -- more than your RAM!! This is because tmp_table_size and ...


1

Yes and no. No, there is no way to apply multiple cores or servers to a single InnoDB transaction. Yes, you can build a cluster with InnoDB. But it is a Galera (PXC or MariaDB) Cluster, which means multiple servers replicating to each other. Overall throughput increases, but response time for individual queries won't. This could help "load", but not ...


1

No harm. No connection termination. The cache is used only when clients are trying to connect; apparently something is out-of-date in the cache. (Follup: The OP tried it and found no side effects.)


1

If you are moving the users to another DB Server running the same major version of MySQL, copying mysql.user is not sufficient. If the users have access to specific databases, copying mysql.user brings the user and the password. Then, you would have to copy the following mysql.db for database-level grants mysql.tables_priv for table-level grants ...


1

The Procedure fails to mention the table containing price_per_meter and meter. Do you really want to feed in price? Do you want to store price in the table -- if so, need another set. Or do you want to use the price that is already in the table -- if so, don't pass it in. My recommendation is to completely erase the STORED PROCEDURE and start over. ...


1

If you could change the WHERE clause for the sake of the benchmark, do one of two things: OPTION #1 : Table Scan SET PROFILING=1; SELECT SQL_NO_CACHE field_a, field_b FROM table; SELECT SQL_NO_CACHE field_a, field_b FROM table; SELECT SQL_NO_CACHE field_a, field_b FROM table; SHOW PROFILES; OPTION #2 : Change WHERE xxx to WHERE 1=1 SET PROFILING=1; ...


1

Not sure if you found a solution to this but for us it seems to be related to the size of data leaving the VPC. If you run a query with a small result set: show collation like '%utf8_swedish%'; The result returns fine, but if you take away the like and get a large result set back it fails. We're still investigating the problem but not sure you found a ...


1

You cannot. MySQL don't have that mechanism. The common featues which are misunderstood about query cache Only works for SELECT queries Queries should be absolutely identical It is not possible to see query cache contents We can only optimize the parameters query_cache_limit = 150K query_cache_size = 512M query_cache_type = 1 Rather, we should ...


1

If you are using innodb tables, try to raise innodb_buffer_pool_size as much as you can. You must balance the ram from mysql and other tools you use for development. The idea is to don't have a swapping machine and to use memory resource as best as possible.



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