Tag Info

New answers tagged

1

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons: No need for disk/memory/maintenance of the mySQL database If the updates to the various databases happen every few seconds, and the users may only ...


0

While there's still some missing info (it would help to know if there is only one table, or if there are two tables, the names of those tables, and which columns are held in each), I think this might help. From what you wrote, it sounds like there are two tables (let's call them 'table1' and 'table2'), and table1 has columns 'id', 'title', 'date', while ...


1

You have a syntax error This is not the right SQL for getting the count Select COUNT(*) from `user` into c where `mobile_no` = mobileNo; You do not put into c after the from clause. It goes before it. Therefore, this is correct SQL syntax Select COUNT(*) into c from `user` where `mobile_no` = mobileNo; Give it a Try !!!


0

You need to run DESC state_master;. This will show you all the columns in that table. Fine the correct column name for the state name. Evidently, from the error, it is not State_name. Then, use that correct column name in the SQL Statement. If State_Name is the actual column name, then try lowercase on all column names $result = mysql_query("SELECT ...


0

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB You definitely need to change the following innodb_buffer_pool_size = 4G innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_io_write_threads = 16 innodb_flush_log_at_trx_commit = 0 Why these settings ? innodb_buffer_pool_size will cache frequently read data ...


1

DISKSPACE FOR EVERYTHING INNODB SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace FROM information_schema.tables WHERE engine='InnoDB'; DISKSPACE FOR DATABASE mydb BY TABLE SELECT IFNULL(tbl,'Total') table_name, FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size FROM ( SELECT table_name ...


4

You missed closing bracket in line: user VARCHAR(100 NOT NULL, should be: user VARCHAR(100) NOT NULL,


1

create table `certificates`( `id` INT NOT NULL AUTO_INCREMENT, `common_name` VARCHAR(100) NOT NULL, `csr` LONGTEXT NOT NULL, `type` VARCHAR(100) NOT NULL, `certificate` VARCHAR(100) NOT NULL, `user` VARCHAR(100) NOT NULL, `creation_date` DATE, PRIMARY KEY ( id ) )


1

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko) When you update an indexed column, changes must migrate through this architecture as follows: From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1 From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file) If you are ...


0

Please look back at my answer again. Issue #1 might be your one and only problem. Why ? FACT #1 : 5199 Qcache_total_blocks allocated FACT #2 : 622 Qcache_free_blocks FACT #3 : 2269 Qcache_queries_in_cache FACT #4 : query_cache_limit is 268435456 (256M) FACT #5 : query_cache_min_res_unit is 4096 (4K) What can said here ? There are 2269 queries in the ...


2

You might laugh when you here this, by there is an aggregate clause that triggers summary between breaks in values. The aggregrate clause is WITH ROLLUP. (Look in MySQL Documentation under GROUP BY Modifiers) let's take your query and make the following changes Remove ORDER BY Substitute WITH ROLLUP Remove name You get this SELECT cdr.datefield AS ...


0

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly. Let's take your query and look for 'tom' SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info ...


0

I'm no expert on the subject, but I'd recommend you look into FULLTEXT searches and indexes. From what I understand, they're much more efficient than LIKE statements for searching for part of a string. Here's the documentation on the feature.


0

You cannot move just indexes to another location. You have to move both data and indexes. When you use innodb_file_per_table, this will create a file with the extension .ibd. For example, if you have an InnoDB table as follows datadir /var/lib/mysql database mydb table mytable The physical files are the following /var/lib/mysql/mydb/mytables.frm ...


1

This technique is possible. Here is the correct syntax: DELETE A.*,B.* FROM member A INNER JOIN subscriptions B USING (type_id) WHERE A.type_id = 1; I have another post from June 26, 2013 on this same DELETE syntax on multiple tables : Deleting Data From Multiple Tables As for the original question, @Michael-sqlbot has it right. The particular tool you ...


-2

DELETE FROM member WHERE type_id = 1; DELETE FROM subscriptions WHERE type_id = 1; Maybe that would work.


0

Your inner query should look like SELECT DISTINCT rp.post_id, CASE WHEN pm.meta_key = 'htk_premiere_date' THEN pm.meta_value ELSE '' END AS premiere_date FROM wp_related_people AS rp JOIN wp_posts AS p ON rp.post_id = p.ID JOIN wp_postmeta AS pm ON pm.post_id = p.ID WHERE ...


1

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates: test=# SELECT 1 UNION SELECT 1; ?column? ---------- 1 (1 row) There is a sharp distinction between UNION and UNION ALL: test=# SELECT 1 UNION ALL SELECT 1; ?column? ---------- 1 1 (2 rows) in your case the second subselect can never ...


0

It is a brave soul indeed that still uses an all-MyISAM database. Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock? Look at the error message again [20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running with the --read-only option so it cannot execute this statement ...


3

You can't* combine multiple queries like this. You need to execute them separately. When you see the error ...the right syntax to use near... that's telling you that MySQL's parser (lexer?) can't come up with any valid reason why it found whatever it's complaining about in the specific place within the statement where it was encountered... in this case, ...


0

If you are using MySQL version greater than 5.5, you can use performance_schema database in MySQL to monitor your MySQL server. There are few other queries that might help you like the SHOW FULL PROCESSLIST; SHOW GLOBAL VARIABLES; You can put these queries as a cron job to run after every few seconds. But making sense out of the huge data returned by these ...


1

You don't need a self join of any kind. Just use variables. SELECT time_recorded AS current, @prev AS previous, timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous, @prev := time_recorded FROM reading , (SELECT @prev := null) var_init_subquery WHERE device_id = 1154 ORDER BY time_recorded What's important in this query is ...


0

This answer is kind of late, but it might help others. If you are afraid to mess something up on the MySQL server, you can change the default engine when creating a table from phpMyAdmin. The default select creator for MySQL engines is this function under StorageEngine.class.php in libraries folders (in phpMyAdmin 3.5.8.2): <?php /** * returns HTML code ...


1

If you want to stick with left join, here is how it should be. The condition you want to exclude is placed on the left join .. on part and you keep the where .. is null check: select foo.* from foo left outer join bar on foo.id = bar.foo_id and bar.name in ('name1', 'name2') where bar.name is null ; It can also be done with not ...


0

Are you trying to do this query: select foo.*, bar.* from foo left outer join bar on (foo.id = bar.foo_id) where bar.name not in (select name from bar) or bar.name is null;


0

Rolando's answer worked for me with some additions. I had the same problem, with these 5 tables showing via SHOW TABLES, but SELECT or other operations on the table resulted in table not found. To resolve the issue, using Rolando's answer, I needed to: DROP TABLE <tablename> -- all 5 tables In the file system, delete the remaining .ibd files (the ...


0

This is not the answer you're hoping to hear but the comma-separated list in Team is not the way to go. Your table needs to look like the one below. It is going to be far easier to get the result you want starting from this table than from yours. ----------------------------- |ID| Team |Task | ----------------------------- |1 |team1 |23 | |1 ...


1

-- ---------------------------- -- Table structure for adresses -- ---------------------------- DROP TABLE IF EXISTS `adresses`; CREATE TABLE `adresses` ( `adress_id` int(11) NOT NULL AUTO_INCREMENT, `adress_adress` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...


0

You can use COLLATE : concat(COL1 COLLATE latin1_general_ci,COL2 COLLATE latin1_general_ci) latin1_general_ci is an example, try to figure it out what collation do u have. But i think this should work, you could try latin1_general_cs too.


0

If you are trying to optimize your database use some script like mysqltunner : Mysql Tunner This really help.


0

U should try to partition your table, try this : http://dev.mysql.com/doc/refman/5.1/en/partitioning-management.html I really suggest you to use MyISAM instead. Such a scenario i had in my workplace for some big tables that we use to store data. I have done a script that inserts every night records from the main_table to an main_table_bak a backup of the ...


3

There are two things wrong First, use DELIMITER $$ so semicolons are interpreted inside the stored procedure and not the command line. After defining the stored procedure, switch the delimiter back to semicolon (;). Next, UPDATE clickactivity SET Clicks = Clicks + 1; will update every row and you don't want that. SUGGESTION #1 Add WHERE IP = ipaddress; ...


2

Your syntax is ok, you just need to temporarily change the end of statement delimiter, like this: mysql> delimiter // mysql> CREATE PROCEDURE Click( -> in ipaddress varchar(45)) -> BEGIN -> DECLARE ex int; -> SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress; -> IF ex = 0 THEN -> ...


0

The fastest way to import your database is to copy the ( .frm, .MYD, .MYI ) files if MyISAM, directly to the /var/lib/mysql/"database name". Otherwise you can try : mysql > use database_name; \. /path/to/file.sql Thats another way to import your data.


4

Since you started both servers, you have executed (approximately) 162509 + 33073 + 11291 = 206,873 queries on the Linux server and 44648032 + 6866308 + 994889 = 52,509,229 queries on Windows. Why would you expect similar numbers when one has done more work than the other? The ratios, however, are similar with: 162509 / (162509 + 33073) ~= 83% 44648032 / ...


0

Best solution. Thanks to @Marshall Matters SELECT c.*, a1.adress_adress AS company_adress_adress, a1.adress_adressnumber as company_adress_adressnumber, a1.adress_zipcode as company_adress_adress_zipcode, a1.adress_zipcode as company_adress_adress_city, a2.adress_adress AS postal_adress_adress, a2.adress_adressnumber as postal_adress_adressnumber, ...


0

You have not mentioned but looks like you are using MySQL 5.6 version. Opened_tables is a counter variable which stores the value of all tables opened by queries since start of the MySQL service. What does 'very large' mean? This Value can be grown up to few millions. What does 'too rapidly' mean? To check whether this value is growing rapidly or ...


-2

create table notification_temp as select * from notification where CreatedAt < DATE_SUB(CURDATE(), INTERVAL 30 day) ; drop table notification; RENAME notification_temp TO NOTIFICATION;


1

$result = mysql_query("SET @Rank := 0"); $result = mysql_query("SELECT *, @Rank := @Rank + 1 AS Rank FROM teams ORDER BY xp DESC, created ASC");


1

Make a temp table, switch it in and out, and copy the last 30 days data into it. # # Make empty temp table # CREATE TABLE NOTIFICATION_NEW LIKE NOTIFICATION; # # Switch in new empty temp table # RENAME TABLE NOTIFICATION TO NOTIFICATION_OLD,NOTIFICATION_NEW TO NOTIFICATION; # # Retrieve last 30 days data # INSERT INTO NOTIFICATION SELECT * FROM ...


1

My favorite is pt-archiver from Percona Toolkit. It takes care of MySQL load, replication lag.


0

MySQL was designed to include special characters in Schema Object Names such as database names table names column names Certain characters (such as ' '(blank), hyphens (-), and other strange characters) can still be used within Schema Object Names by enclosing the identifier in backquotes (`). According to the MySQL Documentation on Schema Object Names, ...


0

Rewrote query to the following, reduced query time to 0.016s: SELECT *, floor((upvotes * 100) / (upvotes + downvotes)) AS percentage_liked FROM ( SELECT COUNT(*) AS views, file_name FROM video_views GROUP BY file_name ORDER BY views DESC LIMIT 12 ) p JOIN uploaded ON p.file_name = uploaded.file_name WHERE conversion_finished != 0 AND ...


0

I think mydumper/myloader is a good tool for operations like this: Is getting better every day. You can utilise your CPUs and can load data in parallelhttp://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/ I have managed to load hundreds of gigabytes mysql tables in hours. Now, when it comes to ...


3

For the simple case, GREATEST() on sub-selects may perform best: SELECT GREATEST((SELECT userid FROM guestuser ORDER BY userid DESC LIMIT 1) ,(SELECT userid FROM olduser ORDER BY userid DESC LIMIT 1)) AS userid; If you want to change query using max function: SELECT GREATEST((SELECT max(userid) FROM guestuser) ,(SELECT ...


3

Try it this way: select userid from ( (select userid from guestuser order by userid DESC LIMIT 1) union all (select userid from olduser ORDER BY userid DESC LIMIT 1) ) sq order by userid DESC LIMIT 1;


0

Following is the script just to give you a rough idea of how to proceed. Change as per your need. Check for slave status using following command if [ mysql -uroot -ppass -e "show global status like 'Slave_running'" -eq "OFF" ] then mysql -uuser -ppass -hhost -e "show master status" >file tweak required things in variables from "file" and ...


2

Short version: use a manual reference, not a DBRef. Explanation: There is no particular benefit to the use of a DBRef beyond giving you the collection and database that the referenced document resides in. If you know the reference is only between categories and products, then that is not particularly useful. A DBRef simply contains the _id of the ...


0

Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not. I once wrote a post about the home position of any empty ...


0

I have successfully done 5.1 to 5.5 and 5.5 to 5.6 for short periods of time, as it looks like you plan to do. Could you do a stepped upgrade? That is, 5.1 to 5.5 to 5.6? Some of the issues you could run into can be caused by things particular to your workload. I've used the pt-upgrade tool (http://www.percona.com/doc/percona-toolkit/2.2/pt-upgrade.html) to ...



Top 50 recent answers are included