Hot answers tagged

5

This query: Uses ANSI JOIN (LEFT, INNER, ...) Uses LEFT JOIN for each table in order to count Sessions IPs without Input or Downloads Uses DISTINCT for each COUNT in order to remove duplicates added by the JOIN between tables Counts values for totals and counts ids for unique counts Query: SELECT s.ip , COUNT(DISTINCT s.id) , COUNT(DISTINCT ...


4

Yes, the data is up-to-date. But don't read too much into the numbers. InnoDB, especially, preallocates space for future rows. So, it is quite possible to look at the exact size of a table, insert dozens of rows (maybe even thousands), then look at the size again -- and see exactly the same Data_length and Index_length. When you first create an InnoDB ...


3

when you're using the "limit 48 offset 7776" mysql need to scan all rows(48+7776), along with offset number increase, mysql need to scan more and more rows for "Next page" To improve this, try: select ... from ... where id > 7776 limit 48 or try: select ... from ... where id between 7776 and 7824


3

This query GROUPs by Ids and uses: CASE WHEN to invert 0 and 1 and SUM it for Unsuccessful sessions SUM of all the 1 for Successful sessions. Query: SELECT t1.ip , COUNT(*) as `ALL` , SUM(t2.success) as Successful , SUM(CASE t2.success WHEN 0 THEN 1 ELSE 0 END) as Unsuccessful FROM Tab1 t1 INNER JOIN Tab2 t2 ON t1.id = t2.session GROUP BY ...


3

Here's one approach: create table users ( username ... not null primary key , <additional attributes> ) engine = innodb; create table rooms ( room_name ... not null primary key , creator ... not null , <additional attributes> , constraint ... foreign key (creator) references users (username) ) engine = innodb; create table conversations ( ...


2

OPTIMIZE is probably better because what it does is CREATE TABLE ... Copy all the existing rows over RENAME ... (Some time during or after step 2, the indexes are rebuilt.) Dump and reload: Read the entire table, write to disk DROP TABLE or TRUNCATE TABLE and CREATE TABLE ... Read the dump, inserting into the table This is slower because of the ...


2

Probably refers to "fresh installation". The workaround is painful... Dump all the data. Stop mysqld. Blow away ibdata1 and iblogs. Change the setting in my.cnf. Restart. (This should feel like a 'fresh' install.) Reload the data. Do you have some indication that changing that setting will help you significantly? If so, please explain. It is a very ...


2

This works on consecutive Start_date for each partition (on Member): SELECT `Member`, `Group`, `Status`, `Start_date`, `End Date` FROM ( SELECT @row := CASE WHEN @status=Status AND @member=Member AND @group = `Group` THEN @row + 1 ELSE 1 END as row , @member:=Member as Member , @group:=`Group` as `Group` , @status:=Status ...


2

InnoDB will only lock the row. But it will eventually have to update all the indexes; however this is mostly deferred. Was something else going on while the INSERT was taking 36 seconds? Perhaps a SELECT ... FOR UPDATE? Or even a SELECT can block the INSERT in certain circumstances. Next time it happens, quickly do SHOW FULL PROCESSLIST to see what else ...


2

You should consider the balance between how often your data will be accessed vs how often your data will be updated. Adding another condition on your SELECT just to display your content may well create more load than deleting rows in the infrequent occasion that a user un-likes. Note that you will need an index on visible to efficiently query against it.


2

If (post_id, user_id) is unique, then jettison the id and make that pair the PRIMARY KEY. I specifically suggest that order because I assume this query is frequently used? SELECT COUNT(*) FROM tbl WHERE post_id = ? AND visible; By having the PK start with post_id, you get the added efficiency of "clustering", thereby drastically reducing ...


2

Memory issues query-cache-size = 512M Too large -- tends to take a lot of effort purging entries; drop it to 50M. Or consider setting it to 0. table-open-cache = 2048M NO! That is a count, not bytes. 1000 (no M) is probably fine. innodb-buffer-pool-size = 6G InnoDB buffer pool / data size: 6.0G/284.1M ...


2

Remember where you "left off". That way the "Next" page can do WHERE id > $leftoff ORDER BY id LIMIT 10 very efficiently. "Prev" page can be done similarly, as can "First" and "Last". More discussion in my blog.


2

MyISAM! Shame. Switch to InnoDB. Don't worry about that PRIMARY KEY; it is too much hassle to split it up, etc. But do make sure it is the 'appropriate' character set: VARCHAR(21) CHARACTER SET ascii If it is case sensitive, then add on COLLATE ascii_bin. Yes, there is a memory and disk penalty. But there are tradeoffs with speed, simplicity, etc. ...


1

The correctness of a database design is specific to the requirements that you gather prior to building it. In simple terms, one size does not fit all and in many situations you will find more than one solution to your problem. The skill is in determining which solution best fits the requirements that you have. There will be occasions when the solution you ...


1

Try the below: Add below 2 lines in my.cnf skip-grant-tables skip-networking Restart mysql service service mysqld restart Note: if you don't have a service, you can do from /etc/init.d/ Connect mysql: mysql Update root password: UPDATE mysql.user SET PASSWORD=PASSWORD('MyNewPass') WHERE USER='root'; flush privileges"; exit; Remove newly added ...


1

When dumping, use the option to not write the CREATE DATABASE statement. When reloading, specify a new, empty, database on the mysql command. That will put the imported data in a different database. You can then compare them, or do whatever. However, if you plan to move them into the old db name, you would have to do DROP TABLE + RENAME TABLE one table ...


1

Beware when using the same name: WHERE `language_code` = language_code Suggest you precede any arguments or locally DECLAREd variables with a _. You can probably combine both procedures into a single SQL statement.


1

Do you want to remove the anon users? You can just use DROP USER to remove them. They shouldn't have any special privileges, as stated in the MySQL Docs and should not be a major problem as they're only in the localhost. If, even removing, you still can login or something, check out this other question.


1

So, you want to import the new data without overwriting and no duplicate keys, right? I'm no expert, but as I see it, you can either: Remove the current data and import the whole old DB, Use --ignore or --replace on mysqlimport - either won't duplicate rows (they'll either --replace existing rows or --ignore duplicates), mysqldump without creating the ...


1

You are working too hard. Plan A -- RENAME TABLE Assuming the production database is really readonly, you don't need FKs, just the indexes they generate. (And you may not need all of them.) So, before testing in Staging, turn off FK checks and drop all the FKs. Then consider dropping any excess indexes. Then test. Since there is no RENAME DATABASE, ...


1

The answer is simple... MySQL WorkBench! Enables migrations from the following systems. SQL Server, Access, PostgreSQL, SyBase, etc... Manage migration projects Source & Target selection Object migration Version upgrades Here's the link... http://www.mysql.com/products/workbench/migrate/ Price is good too! ;-) -Tony


1

Seems you've found a bug in migration wizard, I follow your description and I was able to repeat issue on my machine. Can you please fill bug report at bugs.mysql.com? Appreciate. We'll do our best to fix that isse. As workaround you can try this way - use Data Export to export only structure of your database. Run exported stricpt on target server. Then run ...


1

Dump output is raw SQL statements, numbers and everything else is represented as text so the size is in no way relevant (one int is 4 bytes, but as text 1 is probably 1 byte and maximal int value (signed) = 2147483647 is 10 bytes and minimal int value = -2147483648 is 11 bytes). I suppose those are all ibdataX, *.ibd, *.myi, *.myd, ...


1

I can only speculate what the people meant. Maybe it's related to how MySQL computes index statistics depending on n. The only case that comes to my mind is whether n is higher than 127 or not. As you may know InnoDB stores a string with its length. The length is stored in so called offset fields. The offset can be either one or two bytes. In REDUNDANT ...


1

Check and log/dump the insert/update queries - this looks to me as if the php code somehow recodes your data. The 00f1 is the hexadecimal representation of the character ñ and in the form \u00f1 it might be used as an special/escape sequence (for example in JSON).


1

Query 1: INDEX(person_type, person_id) -- in that order INDEX(person_type, person_id, full_name) -- to be "covering" Query 2 ("covering" is not practical because of *): INDEX(person_type, full_name) -- in that order Query 3: Before making suggestions here, please explain why you have a LIMIT without an ORDER BY. See also my cookbook on making ...


1

If I understand your intent, I might write the query something like this: SET @City = 'London,Lyon,Kln'; SET @sql = concat('SELECT * FROM Invoices WHERE City IN (',@City,')'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;


1

Have two instances of MySQL on that one server. Each has its own data directory, its own port (3307 is typically used for the second one), each has its own my.cnf and logs. But, since they share the RAM, you need to cut back many tunables relating to RAM. Why? What do you gain? Certainly not HA. You may be able to better use lots of Cores. But you are ...


1

See if this meets your specs: SELECT SUM(turned_on), SUM(turned_off) FROM ( SELECT ( @prev <= 5 AND EngineOil_P > 5 ) turned_on, ( @prev >= 15 AND EngineOil_P < 15 ) turned_off, @prev := EngineOil_P FROM ( SELECT @prev := 0 ) init JOIN tbl ORDER BY ...



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