Tag Info

New answers tagged

0

It depends on your DB type. If you use InnoDB, query cache is not required. Moreover query cache harms the speed of overall performance. Hence, if you use MyISAM for wordpress, you should use it.


4

A change in the 5.6.20 release notes: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation. As a ...


0

Use the TIME_TO_SEC() function $sql = "UPDATE table SET minutes = FLOOR(TIME_TO_SEC(time) / 60)"; Give it a Try !!!


0

This should do it: $sql="update table set minutes = (HOUR(Time) * 60) + MINUTE(Time)"


1

Building on the answer from @VĂ©race you can get your usernames like so: Select User_Name from dbo.user WHERE EXISTS (SELECT ul_user_id FROM dbo.user_language WHERE user_id = 'USERID' and ul_iso_code = 'LANGUAGECODE' ) AND EXISTS (SELECT ul_user_id FROM dbo.user_language WHERE user_id = 'USERID' and ul_iso_code = ...


1

"On what parameters can we monitor data warehouse?". Exactly the same ones that you use for monitoring an OLTP database. You might want to pay more attention to things like slow query log (MySQL) for a DW, but that should be on the radar for an OLTP system also. The common "gotchas" may change, but databases all sit on machines with disk, RAM, CPU and ...


2

This is a classic situation where you use a joining table. CREATE TABLE user ( user_id INT, user_name VARCHAR(25), PRIMARY KEY (user_id) ); CREATE TABLE language ( iso_code CHAR(2), -- or whatever you want the PRIMARY KEY to be. language_name VARCHAR(30), PRIMARY KEY (iso_code) ); CREATE TABLE user_language ( ul_user_id INT, ul_iso_code ...


0

The following LOAD DATA sentence loads the CSV file, ignoring the first line and inserting into the desired fields, but changing the format of the Date and Time columns into a proper datetime type: mysql> LOAD DATA INFILE '/tmp/alarms.csv' INTO TABLE alarms COLUMNS TERMINATED BY ',' IGNORE 1 LINES (@Date, @Time, ALARM_01, ...


1

Your $date have to be the correct MySQL date format : '2014-08-29 10:03:30' You can use this: $date = date('Y-m-d H:i:s', strtotime($time)); $sqlTemplate = "UPDATE table SET lastTime = %s" ; $sql = sprintf($sqlTemplate, $date); Checkout the formats that can be your $time string : http://php.net/manual/en/function.strtotime.php and ...


2

UPDATE table SET lasttime=TIME_FORMAT(SEC_TO_TIME(((SUBSTRING_INDEX(time,':',1)*60)+SUBSTRING_INDEX(time,':',-1))-480),'%i:%s'); Do the calculation in seconds then back to time,no need for php FIDDLE


2

You must group by all of the columns i the select clause: GROUP BY ticket.queue_id, queue.name, article.create_time or add aggregate functions such as MAX: SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time), COUNT(article.id) FROM article JOIN ticket ON article.ticket_id=ticket.id JOIN queue ON ticket.queue_id=queue.id GROUP ...


1

You can definitely keep all your dimensions and measures in one fact table and not use any dimension tables. Make sure your OLAP tool supports this though. Normalizing out your dimensions into other tables is done mostly to minimize the size of the fact table, which can get large fast. With no dimension tables you're looking at about 336 MB per year (not ...


2

@RolandMySQLDBA has given the right hint to answer the question. The problem seems to lie in the query and that for the results to be given back, each of those fields has to be read (somehow from the database). I dropped all indexes but the PRIMARY KEY, and inserted this new index: ALTER TABLE newbb_innopost ADD INDEX threadid_visible_dateline_index ...


0

You can go to the OS and drop the file because the data dictionary in the system tablespace (ibdata1) does not know of the .ibd file's existence any more. I wrote a post 2 months ago about deleting temporary .ibd files : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave. It applies in your case as well. Give it a Try !!!


1

There are three things you should do with this Change DROP DATABASE to DROP DATABASE IF EXISTS Add INTERVAL 11 HOUR to the START (as mentioned by @ypercube) Add ON COMPLETION PRESERVE to make it a repeatable event so you don't have to create it again. Here are the changes DELIMITER $$ CREATE EVENT le_drop_database ON SCHEDULE EVERY 1 WEEK STARTS ...


0

Use plain JOIN, not LEFT SELECT SQL_NO_CACHE chars.name FROM chars JOIN inv ON (chars.pid = inv.pid) JOIN institems ON (inv.institem = institems.id) WHERE inv.institem = 10001013730 || institems.container_slot_1 = 10001013730 || institems.container_slot_2 = 10001013730 || institems.container_slot_3 = 10001013730 || ...


0

Memory is not instrumented in MySQL until version 5.7 (currently in development), so this does make your question a bit of a guessing game. I can see from inside InnoDB status, that it doesn't appear to be InnoDB consuming the memory (assuming you collected this from when the problem was occurring): Total memory allocated 26217103360; in additional pool ...


1

While there some manual things that could be done at database level (moving tablespaces around, etc.), most of the settings for MySQL are transparent to the storage layer, meaning that you do not need to do anything to take advantage of the RAID. While a RAID 0 could provide you a better theoretical throughput in reads and writes (at the cost of having 4 ...


2

Firstly: unless this is a replication slave, development box, or similar, that can easily be reconstructed from other sources should the worst happen, RAID0 is not recommended. It provides good performance but potentially seriously bad reliability: if any one of your four drives fails then the whole array is toast. Edit after new information: it would ...


0

Error: Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. This error means that either the master log file is corrupted or the relay log file is corrupted. Before doing anything backup all your databases, logs, image servers, repeat, several times, and only continue at your own risk. First run "show ...


0

Performance is a very fluid concept. It can vary according, for example, to the number of rows in your tables (use of indexes or not). It can depend on whether you use InnoDB or MyISAM. It can depend on what else is occurring on the system at the same time. It can depend on your hardware. And it can certainly depend on your RDBMS. You ...


3

Maybe the problem is that the Workbench is running your statements one at a time without you being noticed, while JDBC exactly sends to the database what you exactly ask it to send. See this answer, where a parameter is used on the connection driver to allow many SQL statements over a single JDBC statement object.


0

Hi and welcome to the forums. In future could you please provide DDL (run the SHOW CREATE TABLE employee_leaves_management_system.location\G (&c.) and also DML (INSERT INTO employee_leaves_management_system.location VALUES(...)) so that those who wish to help you can easily test solutions. See here (and links within) on useful tips on asking questions. ...


1

= is the assignement operator only for SET statements,in your case is a comparison operator,for SELECT use rank := rank+1 Example SET @var=1; SELECT @var = 1 + 1; -> This compares @var which is one to 2 so it returns 0. Result 0 SET @var=1; SELECT @var := 1 + 1; -> This actually adds up. Result 2


0

You already answered this yourself: No, there isn't in plain SQL. You can use PL/PgSQL if you want variables, in a function or a DO block. Most of the uses for query variables in MySQL are satisfied by CTEs (WITH queries), window functions, etc in PostgreSQL. Well, actually there is, but they're not suitable for general use within queries. You usually ...


2

You could try MariaDB which supports virtual columns. Indexes are partially supported. Virtual columns do not support primary keys and indexes can only be based on PERSISTENT virtual columns. PERSISTENT columns can be part of a foreign key and can be referenced by foreign keys, but ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL are ...


0

For the second example you don't need a variable (neither in MySQL nor in Postgres): select id from client where platformID in (select id from platform where bios like '%INTEL%'); Don't be afraid of sub-queries, Postgres' query optimizer is much smarter than MySQL's. If the above is too slow, rewriting it ...


4

YOUR QUERY SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506; At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506. REALITY CHECK No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them ...


0

This is easy to do inside a PL/pgSQL function: create function myfunc() returns void language plpgsql as $$ declare aintconst constant int = -333; arealconst constant real = -9.99; pfid int; clientid int; begin select id from platform where bios like '%INTEL%' into pfid; select id from client where platformID = pfid into ...


2

Your structure i commonly known as an Adjacency List (should perhaps be Set) Model. If you don't want to change the structure of your model, you basically have to do something similar to what you are currently doing. You can rewrite your query using joins like: SELECT q1.* FROM qa_posts q1 WHERE q1.postid=1 UNION SELECT q2.* FROM qa_posts q1 ...


0

OK - following my comment, here is what I would do. Before shutting down the server or individual database. 1) Create backup table scripts for each table you need to enforce foreign keys on and their parent(s). Call them my_table_bak (or similar). 2) Run these CREATE TABLE my_table_bak(1..n) scripts on the database (don't definitively delete anything). ...


0

The solution was found on SourceForge. I simplified the column transformation from this: ('int2ext_accounts_trans', 'I', 'id', null, 0, 'identity', null, 1, current_timestamp, current_timestamp), ('int2ext_accounts_trans', 'U', 'id', 'id', 0, 'remove', null, 1, current_timestamp, current_timestamp), ('int2ext_accounts_trans', 'D', 'id', 'id', 0, 'remove', ...


1

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case. Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log. ...


0

When you see the Seconds_Behind_Master that high, I look at the following: Relay_Log_Space: 8179978166 You have 7.6182GB of relay logs to process. Master_Log_File: mysql-bin.000173 Relay_Master_Log_File: mysql-bin.000093 This tell me that you have read up to mysql-bin.000173, but you are currently processing things from the mysql-bin.000093. This ...


0

In your query, the join has no conditions. Shouldnt be something like SELECT xxx FROM table1 JOIN table2 ON table1.column = table2.column and xxx.date between xxx to xxx . Add: Here is the explain for the test table explain select find from table1 t1 join table2 t2 on t1.find between t2.from1 and t2.to1; ...


1

Syntax issue in the CREATE OR REPLACE. I've not written any procs with this notation so for me it rang alarm bells. I checked both with and without and dropping the 'OR REPLACE' allowed the proc to be created. You can 'DROP PROCEDURE' in advance of creating is for the same effect. Try with; DELIMITER // drop procedure if exists getEmpInfo1; create ...


0

Turns out I can now answer this question myself, thanks to the help of @James in the comments. The article at https://learntech.imsu.ox.ac.uk/blog/?p=151 was helpful, but not 100% accurate anymore, so here is how to do it for anyone in the future: Navigate to the mysql folder (C:/xampp/mysql in my case). Open resetroot.bat in a text editor. Look for line 8 ...


0

I suggest to start with a minimal configuration and then make adjustments For mysql 5.6 i suggest: tmp-table-size = 32M max-heap-table-size = 32M read_rnd_buffer_size = 16M join_buffer_size = 16M sort_buffer_size = 1M innodb-flush-method = O_DIRECT innodb-log-files-in-group ...


0

In Mysql you can use CONCAT function to concatenate values ,you can use your logic in if() to show yes/no according to the criteria.if you want to show the value from column when its not empty then replace 'Yes' with your column name SELECT `d`.`FirstName` AS `First Name`, `d`.`LastName` AS `Last Name`, `o`.`Name` AS `Organisation`, IF ...


-1

Your tables do have several indexes to update, did you remember to run a optimize table from time to time? /usr/bin/mysqlcheck -o --auto-repair MyDatabaseName This will optimize all tables and indexes in the Database MyDatabaseName. Depending on how your data looks this might help with INSERT and SELECT speed. I guess since you have tried everything you ...


0

I would have put it in a comment but do not have enough reputation to do so... So here some advice I find useful when looking for performance issues like these. Use the -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - to check if your system in healthy, if you can improve parameter, do it first, since it will allow you to better ...


4

Your question reminds me of PostgreSQL. It has a feature called TOAST (The Outside Attribute Storage Technique). PostgreSQL features TOAST tables in the event the length of the row data is too small. I have discussed TOAST before in the DBA StackExchange May 01, 2012 : what is bigger than a longblob? Mar 21, 2012 : Are many NULL columns harmful in mysql ...


2

PROBLEM From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column. Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on. SOLUTION What you really need is a single FULLTEXT index with all 3 columns ALTER TABLE ...


1

I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there. As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session. For ...


1

That is 100% normal: what you are seeing is the background threads performing the following operations: Flushing dirty pages from the buffer pool into the tablespace files. You can see that is happening on your case due to the difference between your LSN and the latest checkpoint on the SHOW ENGINE INNODB STATUS. Merging non-unique secondary indexes ...


1

Hi and welcome to the forums. A simple Google led me here. CREATE TABLE employee ( Employee_ID INT, First_name VARCHAR(25), Last_name VARCHAR(25), PRIMARY KEY (Employee_ID), -- Must be present - otherwise you cannot create the FOREIGN KEY Employee_designation VARCHAR(10) ); CREATE TABLE joining_vital ( Employee_ID INT(10), Joining_ID ...


1

I know it is not easy getting an execution plan for an update from MySQL since it only provides those on SELECT statements. But the clue might be in the order in which the records are updated, the evaluation of a WHERE which contains a IN with a large amount of static data, as well as the amount of connected reads and writes, intermediate caching, ...


1

I would say the statement is false, though it's difficult to prove a negative. I don't believe a CROSS JOIN could be done with sub queries SELECT * FROM T1 CROSS JOIN T2 or for that matter any join that delivers a many to many result


2

As far as I know, a JOIN [INNER JOIN], from the theoretical point of view, is a projection of a CROSS JOIN (every combination of two tables). If you can get a cross join using a subquery and apply any function on it, then you have a perfect substitute. I think you can always transform: SELECT T1.A, T2.B FROM T1 JOIN T2 ON condition(T1.C, T2.D) into: ...


0

Just a guess: Maybe mysql checks in the first query mysql checks 17 million rows, if one fit in the IN clause. in the second query you insert only 10000 rows, check the key by index and update the rows. If this is the case you could try to rewritee the first query to UPDATE... WHERE id <= 10000, but this only works if you realy have to update ...



Top 50 recent answers are included