Jehad Keriaki
  • Member for 8 years, 3 months
  • Last seen this week
mysql - unique index with nulls - actual solution anyone?
Accepted answer
12 votes

[This is not tested as it requires MySQL 5.7.6 or above] While I agree with the comments above, I still have an idea that can be tried, which I think is not the best in terms of performance, but it ...

View answer
grant select on all databases except one mysql
Accepted answer
9 votes

Run the output of the following query: SELECT CONCAT("GRANT SELECT ON ",SCHEMA_NAME,".* TO 'test_user'@'localhost';") FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT LIKE 'mysql';

View answer
Identify slow query without slow query logs in mysql server
8 votes

You can run the following statement in a loop in a script that triggers the statement every 10 seconds for example. mysql -e 'SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where time>10 and ...

View answer
Is mysqldump a "hot" backup kind?
Accepted answer
6 votes

First of all, categorizing the backup into the four mentioned categories is not accurate. You can categorize into (hot, warm, cold), and into (physical, logical). In other words, the backup can be ...

View answer
INSERT of 1 wrong value -> Error; INSERT of 2 wrong values -> Warning!
Accepted answer
5 votes

It is way MySQL is designed. As per the documentation: If you try to store NULL into a column that doesn't take NULL values, an error occurs for single-row INSERT statements. For multiple-row ...

View answer
inner join on true
Accepted answer
5 votes

The clause after on is the condition which is if met, the row will be returned with the result set. In your example, the condition is always met, so the result is the Cartesian product of the joined ...

View answer
Convert MyISAM to InnoDB
4 votes

InnoDB tables tend to occupy more space than MyISAM tables do. While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I ...

View answer
Is binary logs required on the slave in order to do replication?
4 votes

This is correct: The master writes to its "binary log" files The slave reads its master's binary files and write them to its "relay log" files The slave executes the statements in its "relay log" ...

View answer
How to find the nth highest salary in SQL?
4 votes

If N=4, it returns the salary where there are 4-1=3 higher salaries, in other words, it returns the 4th highest. Example: Salaries (500, 400, 400, 300, 250, 200). Desired result is (250) (the ...

View answer
HA - MySQL Master Master replication - Duplicate entry errors
Accepted answer
4 votes

This is not a hack. It is the intention of them: Use auto_increment_increment and auto_increment_offset http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#...

View answer
Which is better MAX function or ORDER BY column_name DESC with LIMIT 1
Accepted answer
4 votes

Add an index on created_at and use your first query: SELECT MAX(created_at) AS latest FROM sample_table; The suggestion you got is good in other situations, like if you want a max value of a field ...

View answer
Missing commands in MySQL command line history
Accepted answer
4 votes

There is an option to prevent some commands that contain specific words from being logged. One common practice is not to log commands that has the word password. This option is called histignore ...

View answer
MySQL - Large Table best practices/indexes
Accepted answer
3 votes

As a direct answer, what your query needs is a composite index on (shift, timestamp): ALTER TABLE historical_data ADD INDEX shift_ts(shift, timestamp) However, you have a lot of indexes. and some are ...

View answer
Best practice to delete big portion of big mysql table
3 votes

While there is more than one way to do that (including the one you mentioned), if you can temporarily stop the traffic to the DB, I suggest this way: Stop the traffic (At least to that table if ...

View answer
Does the order of the column name assignation in the join part have an impact on indexes in MySQL?
3 votes

In general, no. Inner and Outer joins are not affected by the order tables and columns in the query. This can be confirmed by the result of explain as @ypercube mentioned. However, if you join using ...

View answer
How to select status variables into temp variables in mysql
3 votes

You can use information_schema's table for that purpose: mysql> select VARIABLE_VALUE into @qcache_hits FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME="qcache_hits"; Query OK, 1 row ...

View answer
Select only rows with at least 2 non-missing values
2 votes

I have the following way to get the IDs of the rows that have at least two not null values; however, I am afraid this is not efficient when it comes to few million rows per table. I'd like to share it ...

View answer
I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this?
Accepted answer
2 votes

From the symptoms, it seems that you are using InnoDB engine for your tables. If so, it is normal not to have accurate number of rows shown by clients (Like HeidiSQL or PHPMyAdmin). It is also worth ...

View answer
MySql - Ranking so it only needs to be done once
Accepted answer
2 votes

You're almost there: Add order by Score1, score2, and enclose it with an update statement: update players, (SELECT UserId, Name, Score1, score2, Rank FROM (SELECT userid, Name, ...

View answer
Importing from CSV file, is crashing mysql server
Accepted answer
2 votes

Let's do some calculation: uuid: 36 bytes (Add 1 because it is varchar) ==> 37 uuid_tags_codes: 4 bytes When indexed, full length of uuid field: 100 bytes (37+4+100)bytes * 162,281,544 rows = 21....

View answer
How to debug huge binary log file increases in MySQL replication setup?
Accepted answer
2 votes

I think your replication uses ROW as binlog_format. To verify, please run this on the master: SHOW VARIABLES LIKE 'binlog_format' The result would be one of the following values: statement, row, ...

View answer
Error Referencing wp_users as Foreign Key
2 votes

The MyISAM engine doesn't support foreign keys. Use the InnoDB engine instead. Other observations: The referenced table has no index on display_name. As per documentation, it is required to have an ...

View answer
selecting all row of max value of a column
2 votes

Your query will get the max value of I_COMDTY_ID with ANY combination of the other fields from the rows that satisfies your WHERE condition. A better way to do that: SELECT t3....

View answer
improve mysql query in large tables
2 votes

To enhance the performance: First of all, add an index on calldate. It will avoid you scanning the whole table. In the query: Not sure what is unique in the query. LIMIT 39780720,39790720 means, "...

View answer
Shouldn't mysql be using both indexes in a join?
Accepted answer
2 votes

I think this is due to the partial index. i.e. the index has only 8 char of hashPrevOut It estimated that the number of matching first 8 char will lead to a second read from the table to compare the ...

View answer
How to query if record exists for prior month in mysql
2 votes

TIMESTAMPDIFF function is useful here: [mysql] desc orders; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+----...

View answer
Select results inclusively based on NOW()
2 votes

Another solution would be to change the date to timestamp, remove the reminder of dividing by 60 as follows: select now(); +---------------------+ | now() | +---------------------+ | ...

View answer
Should I store data pre-ordered rather than ordering on the fly?
Accepted answer
2 votes

Storing the data in an ordered way maybe useful in some rare cases, but it doesn't guarantee that the selected rows will be ordered. You will have to use order by to guarantee the order of the ...

View answer
Optimal SQL solution
2 votes

How about only SQL statements! no triggers, and no SPs; within a transaction for integrity: START TRANSACTION; INSERT INTO notification_log SELECT field1, field2, ... FROM notification_table WHERE ...

View answer
mysql load data local infile is skipping the 1st column of the 1st row
Accepted answer
2 votes

Depending on the source of the text file, the csv file here, it may have the byte of order (BOM) character. In this case, the very first column from fist row will be an invalid integer, and will be ...

View answer
1
2 3 4 5