Tag Info

Hot answers tagged

24

The phrase ORDER BY 1 refers to different columns; in the first it will be id, in the second val. Since id is the key it will be indexed and the order by will be a trivial amount of work. To order by val, however, the system will have to retrieve every row, sort the complete table by val, then choose just one of those rows. Change both queries to order by ...


4

The performance difference in your query is well explained by MG. I am going to address this: I've always believed that * queries should be avoided specifically for performance reasons. select * carries no particular penalties by itself, it is problematic when misused. In a single-table query it works just fine. now join that table to another with 20 ...


3

It would be better to store the credentials in a config file, and load them as an extra-defaults file: $ mysqldump --defaults-extra-file=mycred.cnf ... The config file is the same format as /etc/my.cnf or ~/.my.cnf $ cat mycred.cnf [mysqldump] user = myuser password = xyzzy MySQL 5.6 also introduces encrypted storage of credentials. You can set up an ...


3

I highly recommend that you do not run GRANT ALL PRIVILEGES ON *.* TO 'username'@'%'; This user has the SHUTDOWN privilege, which can allow the user shutdown mysql remotely with mysqladmin -hIP_of_DB Server -uusername -p shutdown You also do not want the SUPER privilege given remotely to just anyone. Why ? The SUPER privilege enables an account to ...


3

Try using MySQL's built-in ExtractValue function, which does all the XML heavy lifting for you: SELECT id, name, ExtractValue(elements, '/elements/video/url') url FROM jos_zoo_item; Ref: http://dev.mysql.com/doc/refman/5.6/en/xml-functions.html


2

You are better off dropping and recreating. Why ? ANALYSIS Let's take the hypothetical example from your question CREATE TABLE result_table AS ( SELECT * FROM tableA JOIN tableB ); This is literally a pure Cartesian Product. Let's say tableA has 2,000 rows and tableB has 5,000. A Cartesian product would result in result_table having1,000,000 rows. Now ...


2

Suppose you have mydb.mytb and you want to create mydb.mytbcopy I have five(5) approaches to doing this copy APPROACH #1 In the mysql client, run the following USE mydb CREATE TABLE mytbcopy LIKE mytb; INSERT INTO mytbcopy SELECT * FROM mytb; APPROACH #2 MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" mysql ...


2

First step: SELECT testname, entry, MAX(`value`) as max_value FROM your_table GROUP BY testname, entry Second step: SELECT MAX(max_value), MIN(max_value), AVG(max_value) FROM ( /*here we insert the query from the first step*/ SELECT testname, entry, MAX(`value`) as max_value /*rename the column so we can conveniently access it from the outer query*/ ...


2

Looks like you installed MySQL 5.1 over a 5.0 (or older) installation, without running mysql_upgrade to create new system tables and add columns to existing system tables. – Bill Karwin 25 mins ago It is safe to run mysql_upgrade without disturbing your other databases. Read http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html for more ...


2

Assuming you have run innobackupex --apply-log successfully you either have a configuration problem (probably innodb_log_files_in_group is 1 in the original and 2 -or not set- in the restored server) or you lost ib_logfile1 somewhere in the process. InnoDB is ok, it is normal for it to fail when it detects a different configuration on disk than on the file. ...


1

Why not changing the text strings to a binary encoding or non-utf8 encoding, or to use binary strings (varbinary, blobs)? That will allow you to store all invalid values that you want. Edit: To be more specific: change the type to varchar(255) charset binary, that may cause you all kind of charset problems . @RLF, funny that you mention Wikipedia, text on ...


1

There is no way to do what you are asking as this would fundamentally break the Atomicity part of ACID


1

SHOW GLOBAL STATUS LIKE 'Uptime'; http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Uptime


1

Looking at the original query SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE() GROUP BY DATE(timestamp) It appears that you are looking for counts for just today. In that instance, why use GROUP BY at all ? You should run SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE(); OK, let's get a little more ...


1

First create a Calendar table (a table of dates) as described in this answer; then extend it by adding and populating Year, MonthName, DayOfMonth, and Tomorrow columns in that table and add a unique index on it by (Year, MonthName and DayOfMonth), and another unique index on it by the base column (Date) - the Primary Key. Now you can generate your reports ...


1

You do not need to purge anything, you just need to follow these instructions to reset the administrator password. If you happened to delete the admin user, just create a new one on the init file: create user root@localhost; grant all privileges on *.* to root@localhost;


1

Use the typical trick of sum(IF the column is the right one, use it, if not read 0: SELECT sum(IF(variable_name = 'Com_select', variable_value, 0)) as `Total reads`, sum(IF(variable_name IN ('Com_delete', 'Com_insert', 'Com_update', 'Com_replace'), variable_value, 0)) as `Total writes` FROM information_schema.GLOBAL_STATUS;


1

Substitute $n with the desired number: SELECT c1.id FROM consecutive c1 WHERE $n = (SELECT count(*) FROM consecutive c2 WHERE c2.id BETWEEN c1.id AND (c1.id + $n - 1) ); This does a full table scan for each number, combined with a range scan. It finds all possible combinations (returning the first figure in ...


1

You have 2 temporary tables being created. It is highly likely that the big difference comes from the fact that MEMORY, the engine used by MySQL to create temporary tables in, well, memory does not support TEXT/BLOB data types (independently of max_heap_table_size), so they are forced to be created on disk, having a great slow down . Profiling your query as ...


1

There is a bug on 5.6.10 version, fixed on 5.6.11 Microsoft Windows: In Shared Memory mode, the MySQL Server could crash when receiving requests from multiple threads. (Bug #13934876) http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-11.html


1

select b.*, s.service_name from branches b, branch_services bs, services s where b.branch_id=bs.branch_id and bs.service_id=s.service_id


1

This is a question more for stackoverflow than for here, but it is great that you asked here first because we would tell you that do not need to handle that on PHP code. MySQL has a syntax called INSERT ... ON DUPLICATE KEY UPDATE that does exactly what you want, in only 1 query instead of 3. There is also REPLACE, although it is slightly different (REPLACE ...


1

I have a single MySQL query that do all the heavy lifting SELECT TimeDisplay FROM ( SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'', IF(dy=1,'1 day ',CONCAT(dy,' days '))), IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours '))), IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))), IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),' ',' ...


1

Let's say the table is called mydb.mytable This query SELECT VAR1,VAR2 FROM mydb.mytable where var3=X AND var4=Y order by var5 desc limit 1; can dramatically be improved if you index the table. There is one of two techniques you can try when indexing the table Technique #1 ALTER TABLE mydb.mytable ADD INDEX search_index (var3,var4,var5); Within the ...


1

Well, that is handled in memory and transactionally stored on disk on the transaction log (by default, the ib_logfile0 and i_blogfile1. But if you just want to know its value, you can check SHOW ENGINE INNODB STATUS or simply: mysql> SHOW GLOBAL STATUS like '%lsn%'; +----------------------------+-------------+ | Variable_name | Value | ...


1

MariaDB had the wonderful idea of jumping from 5.5 to 10.x, making some applications to break. If you look at the source code for MHA, the place where you are getting your error, you can read: # The following servers can not be master: # - dead servers # - Set no_master in conf files (i.e. DR servers) # - log_bin is disabled # - Major version is not the ...


1

Doh! @jynus was exactly correct. Here is the fix: $eventid = intval(mysqli_real_escape_string($dbconnect, $_POST['eventid'])); $sql="DELETE FROM Events WHERE EventID='$eventid' LIMIT 1"; if (!mysqli_query($dbconnect,$sql)) { die('Error: ' . mysqli_error($dbconnect)); } echo mysqli_affected_rows() . " Record(s) Dropped";


1

GOT IT! I had to explicitly set my PHP variable types to integer after normalizing them from the Http POST function... settype($foo, "integer"); ...and then use them in the MySQL query.


1

Yes, for that particular statement, MySQL knows how many rows are going to be inserted, and a lock and/or a mutex is used to retrieve the ids to be inserted at the start of the query. It is fully deterministic and values will be consecutive. However, depending on the variable innodb_autoinc_lock_mode, other non-trivial inserts like insert... on duplicate ...


1

Set lower_case_table_names=1 on the Linux server before importing, this will convert all databases to lower case and new queries will use lower case, too. You can see it here. Your problem arises from the fact that the default value on Mac is 2 (names are not case sensitive when queried, but they are when stored) but 0 on Unix/Linux (names are always case ...



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