Tag Info

New answers tagged

0

In the end I found that if I dumped the data out of one database and into another on a different machine that things seemed to work. I thus decided to do a compare of the two config files and update values that were different. In doing so I updated the following: tmp_table_size from 5M to 19M myisam_sort_buffer_size from 8M to 30M read_buffer_size from 0 ...


1

It looks like you're joining returns on the date and the type of item only, creating a semi-cartesian product. E.g. If you have 100 orders for item A, and 100 returns of item A, on the same day, you'll return 100 x 100 = 10,000 rows, with every return joined to every order. Is this what you want? In terms of performance, the difference between your two ...


1

If I understand your specifications well, and altough I havent tested this query in the server yet, it should work: SELECT CO.conDat, PA.pesNome AS paciente, ME.pesNome AS medico, COALESCE(CO.conDiag, 'indefinido') AS condiag FROM consultas CO INNER JOIN pessoas PA ON ...


0

Based on your tables, you may be trying for the following: select c.conDat , pa.pesId , me.pesId , conDiag , case when 'yourColumnValue' is null then 'Indefinido' else 'yourColumnValue' end as from consultas c inner join pessoas as pa on pa.pesId = c.pacId inner join medicos as me on pa.pesId = me.medId; Note: ...


1

My guess would be that you have TEXT/BLOB data and that the row data in the 14562 row is bigger than your current MySQL Packet, which is sized by max_allowed_packet. MySQL Perspective You need to reserve more space for max_allowed_packet Jul 29, 2013 : Max_packet_allowed setting for Windows 7 Jul 03, 2013 : What max_allowed_packet is big enough, and why ...


0

If you want to DROP all at a time, list all of them (comma separated) except the one you do not want to delete DROP USER 'smithj'@'localhost', 'andersonk'@'localhost'; If you do not have a list try this, as you need in this format 'username'@'hostname' SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user; This will fetch result in ...


0

Solution to the query: select * from ( select * from ProQzQuizReport order by points desc, time_spend asc) a group by user_id; http://sqlfiddle.com/#!2/65fbf0/9


2

Without explicit transaction, all statements are in auto-commit mode - so each insert is separate transaction. And that comes with some overhead for syncing etc. If you insert all rows without commiting in between, mysql can do lot of it in memory/cache and sync it to disk only once (simplified, caches will probably be flushed multiple times by filesystem ...


1

You have to use select sleep(5); because it is similar to other mysql functions.


0

If you are using integers/numbers, always make col1 the lower value and col2 the higher value when you modify or query the table.


3

First retrieve id and the computed scores, then sort and join the result back to news: SET @line = 0; SET @last = 25; SELECT B.*,titlescore,timescore,textscore FROM ( SELECT id,titlescore,timescore,textscore,combinedscore,(@line:=@line+1) line FROM ( SELECT id, MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` ...


0

(I'll attempt some out-of-the-box thinking here. Don't hurt me if I'm completely off ;) I assume an essential part of your query execution time is spent on the clause computing the score for the news text. You'll need to verify that for yourself before continuing. In most of the cases, highly relevant articles (the top 25) will generate a decent score ...


0

Since your situation is product is commercial you cannot change the query you cannot change table layout Your best shot (really your only shot) would be ALTER TABLE table2 ADD INDEX status_table1_ndx (status,table1); You may or may not see a change since the AND table2.table1 = CONCAT( 'constant prefix', table1.attrib1 ) is really a JOIN clause ...


0

You cannot directly create a unique constraint across tables. I say directly because you could come up with some indirect scheme; what comes to my mind would be using triggers to insert into a third table that has the unique constraint. But that's a lot of extra work compared to the obvious solution, which is to keep the column "a" in the "my" table.


0

In the first way, you are sort-of correct. Here is why I say soft-of. InnoDB has a Clustered Index, known internally as gen_clust_index.Basically, it is a rowid index that is created for the InnoDB table under-the-hood (or if you are from Jersey City, NJ like me, created in-the-hood). These rowids are attached to all secondary indexes. Creating a PRIMARY ...


0

You need to make sure they do not contain any tables Please run this query select table_schema BlankDatabases from (select A.table_schema,COUNT(B.table_schema) table_count from (select schema_name table_schema from information_schema.schemata where schema_name not in ('information_schema','mysql','performance_schema')) A left join (select table_schema ...


1

You should be restoring to a completely empty folder - you need to remove all the content (files and folders) from the target folder before doing the restore. Xtrabackup contains the entire system, not just a particular database. As always, test the restore on your test environment before attempting on production! And remember to shut down MySQL before ...


3

Firstly, find out your constraint name thus: select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'My_Table'; And then remove the constraint by the following means: ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint; Refs: 1 & 2. ...


2

You need conditional aggregation using sum(case): select cat_id, sum(case when status = 1 then 1 else 0 end) as published, -- only count status 1 sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0 count(*) as totals from tab group by cat_id Edit: My crystal ball was apparently broken, but now it's fixed :-) If ...


1

In addition to the Enterprise solution mentioned by @TrentLloyd, there is a free Open Source solution available from Percona (who are big hitters in the MySQL world). What they offer is Percona XtraBackup which has an incremental backup functionality. The docco is here. You might also be interested in LVM snapshots - it's not a technology with which I am ...


0

You have a few options: Rebuild MySQL with USE=profiling Upgrade phpMyAdmin Use a more purpose-built tool for the backup: Percona's xtrabackup, mylvmbackup, mysqldump or anything else, in a cronjob.


0

Looking for the same thing, I came across the report bug about this issue; In addition to some weird tab naming that is also happening to me. http://bugs.mysql.com/bug.php?id=74966


0

Dan Craven answered the first half of your question, and I agree that it's better to store the path to the file rather than the file itself in your database. MySQL is fully capable of storing the full MP3 file as a BLOB, but can suffer some performance problems along the way. This part is an opinion based question -- there's no universal "right way", but ...


0

I answered a similar question in Aug 2011 : Which DBMS is good for super-fast reads and a simple data structure? Since you are asking about MySQL and which storage engine. To be honest, it is hard to say because there are rare occasions when MyISAM can outperform InnoDB when it comes to SELECTs. Here are some of my past posts on this controversy Sep 20, ...


1

If you are simply interested in adding a column Users_ID to the Content table, then the following will likely do the trick. (Assuming this is indeed SQL Server - see the 2nd code block for the MySQL version). /* add the Users_ID column to the table */ ALTER TABLE dbo.Content ADD Users_ID INT NULL CONSTRAINT FK_Content_Users_ID FOREIGN KEY ...


-1

If you need foreign key support, then you kind of have no other choice than InnoDB. However, if performance is the concern, MEMORY is the fastest. You have to make sure you have a permanent copy of the tables on disk, and load them into memory after each server restart. If you use other engine, and you enable query caching, you would gain performance with ...


1

I answered an old question like this Feb 25, 2014 : Questions since MySQL Startup In that post, I described how everything MySQL executes to retrieve info is a question. I also mentioned how a billion questions is no big deal (See my ServerFault post 1 billion mysql queries in 24 days? Can something be wrong?) Here is the difference Queries : The number ...


0

This is actually a very good question since MyISAM stores the count in its header, whereas InnoDB requires a full count (See my answer to the post Why doesn't InnoDB store the row count?) SOLUTION Use INFORMATION_SCHEMA.TABLES to create the SQL for counting each table ALL DATABASES SET group_concat_max_len = 1048576; SELECT ...


1

It doesn't work because the syntax is defined as INSERT .. SELECT i.e. there has to be a valid SELECT in the overall statement. For your "DOESN'T WORK" example, if you comment out the INSERT you're left with an invalid statement. This will not run: --INSERT INTO phppos_categories (name) (SELECT DISTINCT category FROM phppos_items as items UNION SELECT ...


2

Try this it will work INSERT INTO phppos_categories (name) SELECT DISTINCT category FROM phppos_items UNION SELECT DISTINCT category FROM phppos_item_kits


0

Well, with these Indexes, there shouldn't be any issue. How many rows does your table have ? how many joins you use in your queries, If you're using joins, do those tables have appropriate indexes ? The above mention queries should almost not have any issues. Post the actual queries you're running which are slow. Although, If you still want to go ...


1

I experimented with this and found something very interesting. Running Fedora release 20 (Heisenbug) 3.11.10-301.fc20.x86_64, 2GB RAM (I know!), 2 processor Intel centrino. If you enable the Performance Schema (P_S), the times seem to drop dramatically. Why this is, I simply don't know - the P_S is for monitoring, and not changing anything. I have put ...


1

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions. DELIMITER $$ DROP PROCEDURE IF EXISTS Events_insertion$$ CREATE PROCEDURE Events_insertion() BEGIN DECLARE i INT; DECLARE dTime DATETIME; SET i = 1; SET dTime = NOW(); ...


0

ok this is my table: mysql> desc name_test; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | fname | varchar(20) | NO | PRI | NULL | | | sname | varchar(20) | NO | | NULL | | ...


0

In my personal opion (however I am new to databases as well), I would look to store the relative URL, which would be the VARCHAR from here you could store the realtive path to the file (from where the script is being called from) and load the file that way. However it may be worth writing the SQL for your tables as well. Just incase you need to alter or ...


2

Lets say that the media tables has 1K rows and the emails one has 100K rows. Then this will insert into the emails_media_xref all possible combinations, 1K x 100K = 100M, that is a hundred million rows. Warning, this may take some time: INSERT INTO emails_media_xref (emails_id, media_id) SELECT emails.id, media.id FROM emails CROSS JOIN ...


0

Someone sent me a compressed gtar. Wasn't even too familiar with gtar, but it is another compression format. $ file core_production-1432173533.sql.gtar core_production-1432173533.sql.gtar: gzip compressed data, from Unix, last modified: Wed May 20 21:59:31 2015 However, I was able to decompress it the same as usual: tar -zxvf ...


0

I definitely agree the biggest bang for the buck comes from increasing your innodb_db_bufferpool size but unfortunately it completely depends on how large is your data set and how often different disk blocks are accessed. I maintain several database that are fairly large 200 GB+ so fitting everything into the RAM is not really an option and for that reason ...


0

For future reference, you can use something like this: MySQL :: MySQL 5.0 Reference Manual :: B.5.4.1 How to Reset the Root Password


2

Decided to just remove and reinstall it!


0

While replicating from a MySQL 5.0 Master to MySQL 5.6 Slave is allowed, it is not good to keep that scenario indefinitely. Why ? The way MySQL 5.6 reconnects to an older Master may be more difficult with older Masters. To illustrate problems of this nature, consider how MySQL Replication can break between versions because of Binary Logs changing between ...


0

In SQL Server you could do the following, some of this will not work in MySQL. ;WITH MinPopulation AS ( select distinct name as name ,CountryCode as CountryCode ,SUM (population) over (partition by name,CountryCode) NumberOfInhabitians ,AVG (population) OVER (partition by name,CountryCode) AverageNumberWithinDistrict from population) ...


2

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use ...


2

Disclaimer: If you have important data on your server, backup before you perform any of these operations. I did not exhaustively test my solution. To answer this, I did the following on Windows and Linux (example shown is for Linux) Logged on - selected from user table. Deleted all users except the root one with password. Logged out, then logged back in ...


1

The problem you have is that Ansible is trying to use the same root password to login as you want to change it to: - name: Set root user password mysql_user: name=root host="{{ item }}" password="{{ mysql_root_password }}" check_implicit_admin=yes login_user="{{ mysql_user }}" ...


0

If you need to do this with minimal downtime and have 100% confidence that you have consistent slave here are step by step direction how to rebuild the slave using Linux LVM. Preparation Set innodb max dirty pages percent to zero on the master. This will force MySQL to write all the pages to the disk which will significantly speed up the restart. set ...


1

The nested set model makes this kind of queries, for unlimited levels, easy to write: SELECT * FROM sr_projects WHERE name LIKE '%x%' UNION ALL SELECT p.* -- the parents FROM sr_projects AS p JOIN ( SELECT lft FROM sr_projects WHERE name LIKE '%x%' ) AS x ON p.lft < x.lft AND x.lft < p.rght ...


0

The way you are describing the expected result; I am concerned that what you are looking for won't get you what you want. Taking the example from the referenced post, you may actually want: create view yourview1 as ( select user_id, case when Item_Type = "first_name" then meta_value end as first_name, case when Item_Type = "last_name" then ...


0

If I understand, you want to get all trip_id values that appear in the table, against one stop_id on one or more rows, and against another stop_id on other rows. This should do it: select trip_id from stop_times where stop_id = 1538 or stop_id = 1540 group by trip_id having count(distinct stop_id) > 1; Example: http://sqlfiddle.com/#!9/e78aa/1 ...



Top 50 recent answers are included