Pankaj Kumar
  • Member for 3 years, 10 months
  • Last seen more than a month ago
  • Noida, Uttar Pradesh, India
How to get mysql view definitions?
9 votes

Show create view 'viewName' returns the SQL definition with create view statement while SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'DATABASENAME' ...

View answer
Find if any of the rows partially match a string
2 votes

Try it: SELECT * FROM site_core_antispam_banned_domain WHERE RIGHT(domain_name,10)='domain.com';

View answer
Subselect vs join: Are both SQL queries equal?
1 votes

Both query will return same possible data sets as if you use in as sub query, it behaves like inner join in SQL query and where clause in both SQL statements should be same as in your where condition.

View answer
How do you select*from with multiple conditions?
Accepted answer
1 votes

Try it: SELECT * FROM Flights WHERE Departure_IATA_Code = 'STN' AND Arrival_IATA_Code = 'DBN' AND Departure_Date = '2018-04-16';

View answer
DELETE IGNORE on Tables with Foreign Keys Can Break Replication
Accepted answer
1 votes

I have read it here: https://www.percona.com/blog/2012/02/02/stop-delete-ignore-on-tables-with-foreign-keys-can-break-replication/ The details of how DELETE IGNORE can be unsafe for replication is ...

View answer
Auto_Increment After deleted data from the table
1 votes

It will be better to truncate table to start auto_increment from beginning of a auto_increment field: Truncate table tableName; as delete table starts auto_increment from last auto_incremented ...

View answer
Make Unique combinations using mysql
1 votes

Try this, it will give you the specific output as you expect: SELECT t1.name AS color, t2.name AS size FROM table2 t1 JOIN table2 t2 ON t1.table1id <> t2.table1id WHERE t1.table1id=1 AND t2....

View answer
MySQL: creating a user that can connect from multiple hosts
Accepted answer
1 votes

Use this query to creating user account that can be connect from the localhost or from another server CREATE USER 'bob'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

View answer
Transactional Replication breaks when schema is changed in Publisher
0 votes

From Make Schema Changes on Publication Databases: Schema changes to tables must be made by using Transact-SQL or SQL Server Management Objects (SMO). When schema changes are made in SQL Server ...

View answer
Restart event scheduler and alter event when slave being promoted to master
0 votes

As much I understand your question to Automatically restart event scheduler and alter event when slave being promoted to master. You have always have to run the script to manually fill the losing data ...

View answer
How to get INNER JOIN result from two tables
0 votes

Please try: SELECT tf.font_id, tf.font_name, tcf.font_id, tc.charset_name, tcf.font_id, tcf.charset_id FROM table_fonts tf INNER JOIN table_charsets_by_font tcf ON tcf.font_id=tf.font_id INNER ...

View answer
Fetching Data from joining table for any one from condition satisfied
Accepted answer
0 votes

Try it: SELECT * FROM myactivities_4 LEFT JOIN claims2 ON claims2.activityid = myactivities_4.activityid WHERE myactivities_4.activityid NOT IN ( SELECT activityid FROM claims2 ...

View answer
Insert Statement Take too long to insert - MYSQL
0 votes

You just try these three steps to fast insertion: InnoDB supports transaction If you have multiple insertion then use transaction in your insertion. START TRANSACTION Your insertion query Commit; ...

View answer
How to query the top TWO and the bottom TWO values at the same time in MySql
Accepted answer
0 votes

Please edit your answer as SELECT playerName,teamName, scoreChange,category FROM player WHERE scoreChange = (SELECT MAX(scoreChange) FROM players) UNION SELECT playerName,teamName, ...

View answer
MySQL not using primary key on inner join
0 votes

There is no foreign key reference between these two tables. Secondly, In table ediLoad column transactionTypeID datatype is tinyint(2) unsigned DEFAULT NULL while in table lk_transaction_types column ...

View answer
Need help fixing query/indexes
0 votes

You are using too many left joins, which slowed down the query performance. You need proper indexing on columns which is used in where and ON clause. Id's have primary keys which has already ...

View answer
Sequence of dates - months from January to December with SQL
0 votes

May be like this one: SELECT m.MONTH, IFNULL(n.TOTAL,0) TOTAL FROM (SELECT 'January' AS MONTH UNION SELECT 'February' AS MONTH UNION SELECT 'March' AS MONTH UNION SELECT 'April' AS MONTH UNION ...

View answer
Composite vs auto incrementing integer as Primary key
0 votes

You may get your required output by activityID as a foreign key with auto incremented primary key. But there is possibility of duplicate entry in this case. So this is the best option to use composite ...

View answer
Is there benefit from having 2 tables for 1 entity?
0 votes

It is a better option to normalize database with smaller multiple tables having Primary Keys and Reference Keys. It will increase performance by tables having Primary Key in a table and another table ...

View answer
How can I take backup MySQL database from one cloud server to another?
Accepted answer
0 votes

I have take backUp by this query : mysqldump mysqldump --single-transaction --routines --host 10.222.55.111 -u root -p 'Password' -P 3306 ...

View answer
Is it benefit to create a index key if unique key already exists on the same columns?
0 votes

There is already using primary key i.e, clustered Index and then used UNIQUE KEY on uqx_bar1_bar2 (bar1,bar2). So there is no more needed KEY idx_bar1 (bar1), KEY idx_bar2 (bar2).

View answer
Cannot run a job to update table on a linked server
0 votes

The SQL Server 2012 builds that were released after SQL Server 2012 was released. You must apply a SQL Server 2012 hotfix to an installation of SQL Server 2012. The fix for this issue was released in ...

View answer
SQL DBA data type is changed after upgrade
0 votes

You should perform the following tasks after updating : Upgrade Database Compatibility Level The database compatibility level needs to be manually changed on the 2014 instance Run DBCC CHECKDB WITH ...

View answer
How to Audit any change in Permission?
0 votes

You can find sys.database_permissions for all the permissions of all objects in database including who did grant permission.

View answer
In a MySQL Master/Slave replication, what would happen if I write to the master table and in slave table is drop?
0 votes

If the Slaves do not find 'DROP TABLE' command in the Master's binary logs, it cannot run the DROP TABLE either DROP TABLE command has not been run on Master.

View answer
MySQL does not read user specific options in ~/.my.cnf
0 votes

With the mysql client, to use a character set different from the default, you could explicitly execute SET NAMES every time you start up. To accomplish the same result more easily, add the --default-...

View answer
CPU,Memory and disk I/O utilization for each query in mysql
0 votes

Using IOTOP command you can see CPU,Memory and disk I/O utilization for each query. For each query mysql generates an ID. You can find CPU,Memory and disk I/O utilization against your query ID. How ...

View answer
Which index type is best suited for exact-match against a list of values?
-1 votes

Hash Index: Hash indexes are used only for equality comparisons that use the = or <=> operators. They are not used for comparison operators such as "<" that find a range of values. The ...

View answer