FAQ Database Discussion Community


Troubleshooting Wordpress/Woocommerce custom SQL query for reporting

mysql,sql,wordpress,woocommerce,query-optimization
Hopefully this is the right forum, my question seems to overlap the stack exchange community so this seemed best. I have some custom reports for my WooCommerce orders on my wordpress site. I have one query that is just freezing locally, meaning in my localhost my CPU goes to 100%...

What difference between ('xyz' != 'xyz') and NOT ('xyz' = 'xyz')

oracle,performance,optimization,query-optimization
Is there any difference between ('xyz' != 'xyz') and NOT ('xyz' = 'xyz') Though I am asking in context of Oracle DB, but would also like to know for same situation in other languages too. Differences mainly like processing speed and all that background stuff....

Stored procedure part optimization

sql-server,stored-procedures,sql-server-2008-r2,query-optimization
I have a stored procedure CREATE PROCEDURE [dbo].[MyProcedure] -- Add the parameters for the stored procedure here @xml XML AS BEGIN SET FMTONLY OFF declare @idoc INT; -- table(CentreId bigint, LanguageId int) EXEC sp_xml_preparedocument @idoc OUTPUT, @xml --declare @LangCentre table (CentreId bigint, LanguageId int) CREATE TABLE #LangCentre ( CentreId bigint,...

SQL JOIN avarage value

mysql,sql-server,performance,optimization,query-optimization
I need some help for optimization of my query I have two tables Table 1 - users_location; Table 2 - users_rating +-------+---------+ +-------+--------+ |user | location| | user | rating | +-------+---------+ +-------+--------+ | user1 | AA | | user1 | 1 | | user2 | BB | | user1...

Slow performance on joining table

mysql,sql,performance,query-optimization,innodb
I am trying to optimize a query as below , I have study how to add index to improve peformance , but the result is still slow. The query below took 20 sec to run , Transaction contains around 100k records and joining table TransactionDetail that contains around 500k records....

get app users role, name for the valid users login using stored procedure

sql,sql-server,stored-procedures,query-optimization
I've made stored procedure which is used to validate user login and get the role and name of the valid user. I've some confusions, please make me clear. is this the optimized way to do this? what is I use IF EXISTS (SELECT 1) to check for username and password...

Mysql query to suggest top 15 most activity user which is not yet subscribe

mysql,query-optimization,autosuggest,friendship
I have 3 tables with structure as below. tbl_activities (id, activity_type, user_id) tbl_subscriptions (id, subscriber_id, subscribed_id) tbl_users (id, user_name) User can do activity such as like or comment. He can also subscribe to many users. Now I want suggest some user for him base on activity type and avoid his...

Testing my website for database performance & traffic handling [closed]

mysql,django,database,query-optimization,web-testing
I am developing a website using Django1.7 on python3.4 along with MySql as database engine. For the next 15-20 days I am planning to test it. The site is something like linkedin in terms of functionality and complexity and I am expecting to get around 20-30 thousand users in the...

Reduce the number of SqlCommands required to send information to three tables

c#,sql,query-optimization,sql-azure,sqlcommand
I have the following code: command = new SqlCommand("SELECT UserId from Users WHERE Username = N'" + userName + " AND " + userPassword + "= N'" + userPassword + "AND AccountStatus = 0"); command.CommandType = System.Data.CommandType.Text; command.Connection = conn; int uid = (int)command.ExecuteScalar(); if(uid > 0) { command =...

Slow query with distinct/group by on varchar column with Postgres

database,performance,postgresql,indexing,query-optimization
I have a company table and an industry table, with a many-to-many relation table linking the two, named company_industry. The company table currently has approximately 750.000 rows. Now I need a query that finds all of the unique city names for a given industry, in which there are at least...

Optimize the execution of select

sql-server,indexing,query-optimization,clustered-index,non-clustered-index
I want optimize this select: Select Dane1, Dane5, Dane6, Dane7 FROM Test INNER JOIN Test2 ON Test.Id=Test2.IdTest WHERE Dane5 > 199850 My database has 2 tables test, test2: test design: Id int ->PRIMARY KEY, Dane1 int, Dane2 int, Dane3 int, Dane4 int, Dane5 int, test2 design: Id int ->PRIMARY KEY,...

MySQL Merge Index Optimization not working

mysql,query-optimization
I was try simulate merge index on MySQL like say here http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html but i have no ideea why i dont take column type = 'index merge'. Here i have contruction table: create table hotel( index1 int not null, nume varchar(100), index2 int ); CREATE UNIQUE INDEX hotel_index1 ON hotel (index1);...

Eager Loading Optimization in ruby on rails

ruby-on-rails,ruby,optimization,query-optimization
I have a product that has_many Variants. And a variant belongs to a product. I want to display the name of the product(which can be found in Product) and the price and quantity(which can be found in Variants). Product table: -id -title -description Variants table: - id - is_active(boolean) -...

DB Engine Tuning Advisor suggestion improvement

sql-server,query-optimization,database-tuning-advisor
We have a table which holds all email messages ready to send and which have already been sent. The table contains over 1 million rows. Below is the query to find the messages which still need to be sent. After 5 errors the message is not attempted anymore and needs...

BaseX query optimization on join

query-optimization,basex
After the issue in the following Stackoverflow is fixed, I have another problem when I try to make a join as below. The last query takes about 250ms while the first two take only 16ms. Is there a better way to perform join between two items? Note: You can find...

Optimize SQL query with and or and structure in WHERE clause

sql,sql-server,query-optimization,where-clause,sql-function
I have a function that has to convert currencies based on a conversion rate retrieved from a a table with historic values. It takes four parameters (@to_curr, @from_curr, @trans_date, @gl_cmp) adn returns the exchange rate. The table structure is currencypk int primary key gl_crcnv_bdate datetime gl_crcnv_edate datetime gl_crcnv_rate float gl_cmp_key...

Time to add default values to a new column by approach

sql,sql-server,query-optimization
I need to add a new non-null column to an existing SQL table and set it to a default value. I understand that there are two different approaches to this: ALTER TABLE Foo ADD Col CHAR(1) NOT NULL DEFAULT('N') ALTER TABLE Foo ADD Col CHAR(1) GO UPDATE Foo SET Col...

why using IN (or NOT IN) clause in a query makes it really slow

mysql,query-optimization,query-performance
I have a query: SELECT DISTINCT field1 FROM table1 WHERE field2 = something (table1 contains 1 million records, execution time:0.106sec, returns: 20 records) Another query SELECT DISTINCT similarField1 FROM table2 WHERE similarField2 = somethingElse (table2 contains half million records, execution time:0.078sec, returns: 20 records) Now if I run a query,...

MySQL/MariaDB slow UPDATE on PRIMARY KEY

mysql,sql,query-optimization,innodb,mariadb
Query: UPDATE `cart` SET `user_id` = NULL, `completed` = 0 WHERE `id` = 6948; Query OK, 0 rows affected (1.21 sec) Rows matched: 1 Changed: 0 Warnings: 0 0 rows affected, but it took 1210ms. SELECT of this row by id always took 0ms. Table size is (6,354 rows). >...

mysql - how to insert multiple records and delete remaining ones

mysql,query-optimization,database-performance
Lets start with an example. There's a table: CREATE TABLE X ( id INT(32) NOT NULL auto_increment, account INT(32), value INT(32), primary KEY (id) ) What I want to achieve is to ensure that given account has specified set of values (and only these values). Basically, how to do below...

Any better alternative for getting last record without limit

mysql,query-optimization
I have a following query DECLARE lv_Duration INT; SET @lv_Duration = 0; SELECT @lv_Duration := TIMESTAMPDIFF(SECOND, changedon,NOW()) FROM `transactionhistory` WHERE transaction_Id = TRIM(_transaction) ORDER BY tsh_id DESC LIMIT 1; In which I am getting time since last entry of transaction_Id fields.But it is taking .25 of a second in relative...

Optimize query many field mysql

mysql,query-optimization
I'm confused about how to chose best index for my table: Have this table: Hotel -has_1 ... -has-5 -nota_AVG -nota_1 -nota_2 -nota_3 and this Nota_hotel -nota_1 -nota_2 -nota_3 -id_hotel Field with name "nota_*" from hotel is update from a trigger on table Nota_hotel and this field changed frequently. My need...

Mysql Group Join Optimization Issue

mysql,database,group-by,query-optimization
I'm trying to optimize this query it returns multiple rows from the building_rent_prices and the building_weather and then groups them and calculates the average of their field. So far the tables are all under a million rows yet it takes several seconds, does anyone know how i could optimize this...

How can I optimize this query of Mysql?

mysql,performance,optimization,query-optimization
I use Mysql,I have a table with about 800,000 records (TablaUnoOriginal), I need to copy the data to another table (TablaUnoCopia) and then delete the original data, I am doing as follows: INSERT INTO TablaUnoCopia SELECT * FROM TablaUnoOriginal WHERE CampoID in (SELECT DISTINCT (CampoID) FROM Key_Table); In this part...

Optimization SQL for getting data from two joined tables (usernames for user-from-id and user-to-id msgs from two tables)

mysql,join,query-optimization
I have table "msgs" with messages between users (their ids): +--------+-------------+------------+---------+---------+ | msg_id |user_from_id | user_to_id | message | room_id | +--------+-------------+------------+---------+---------+ | 1 | 1 | 4 |Hello! | 2 | | 2 | 1 | 5 |Hi there | 1 | | 3 | 2 | 1 |CU...

Using complex statements in SQL queries multiple times in multiple ways

sql-server,sql-server-2008,tsql,query-optimization
I am designing a View on a Microsoft SQL Server 2008 that should be built upon quite a lot of business logic, meaning there is a lot of CASE WHEN THEN ELSE statements. The thing is, result from one CASE statement is often needed in other places in the query,...

MYSQL Slow Insertion and Update in Big Database

mysql,database,performance,query-optimization,my.cnf
I'm having trouble tuning mysql configuration to maximize the speed of insertion and update queries. The problem occurs when we have to insert daily data approximately half a million record everyday and it would run for minutes before it completes. While it performing the job I've checked and found out...

Optimizing a MySQL table for faster select operation

mysql,performance,query-optimization
I have a MySQL table which contains Aggregate Revenue of a company for two year from it's different clients to perform analysis on Current year Revenue and future Revenue forcast, having these fields for time, MonthID, QuarterID and YearID It is having month wise data, also have quarter wise (I...

SQL query optimization - really nothing more to improve?

mysql,sql,database,query-optimization,sqlperformance
I have the following query. I picked it from mysql slow queries log: SELECT AVG(item.duration) AS dur FROM `item` INNER JOIN item_step ON item_step.item_id = item.id WHERE item_step.number = '2' AND (IS_OK(item_step.result) OR item_step.result2 IN ("R1", "R2")) AND item.time >= '2015-03-01 07:00:00' AND item.time < '2015-05-01 07:00:00'; As usually I...

SQL Query Optimizing on large data

sql,sql-server,query-optimization
I'm trying to figure out why my below code goes from 2sec run time to 23min run time simply by adding the indicated where clause (at the bottom). A fix would be awesome but also trying to understand why thats causing it to run 4573468975468% longer, (not working with a...

Mysql IN keyword

mysql,query-optimization
I am trying to write a query using the IN keyword. Table A attrId, attrName Table B key, attrId, attrVal Based on key provided, I want to return the all attrName, attrVal combinations. The result will contain of columns from both tables. I don't want to use join using attrId...

Which is the intermediate language used by PostgreSQL for query processing and optimization?

postgresql,query-optimization
I'm currently doing a paper on PostgreSQL and I can't find anywhere (including their documentation) which is the intermediate language used for query processing and optimization.

Optimize MySQL JOIN with subquery

mysql,query-optimization
My current MySQL query is very slow. I am looking for ways to optimize it. I have a table with giveaways for Steam games (12.711 records). CREATE TABLE IF NOT EXISTS `giveaway` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `creatorid` bigint(20) unsigned NOT NULL, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,...

Optimizing slow MySQL select query

mysql,query-optimization
EDIT: After looking at some of the answers here and hours of research, my team came to the conclusion there was most likely no way to optimize this further than the 4.5 seconds we were able to achieve (unless maybe with partitioning on offers_clicks, but that would have some ugly...

Optimization of parsing a specific format string in Sql Server

sql,sql-server,query-optimization
I need to parse a message which is being passed to the stored procedure. The format of the message could look a like ##tag##message I wrote some lines in the sproc which basically does the job, but i am looking to optimize it in such a way that extra variable...

Efficient way to get last record from the database

mysql,database,jpa,query-optimization
I have a database with following table structure : id | entry | log_type | user_id | created_on | ------------------------------------------------| 1 |a | error | 1 | 1433752884000| 2 |b | warn | 2 | 1433752884001| 3 |c | error | 2 | 1433752884002| 4 |d | warn | 4...

Optimise select distinct from large table

tsql,query-optimization
I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records come down to 145,878,852. The number of distinct records are 107,311,357. select count(*) from [BroadcastOpens] with (nolock) where [OpenTimeUtc] >=...

Execution sequence of IN and JOIN in MySQL

mysql,sql,performance,query-optimization
I have a SQL query which looks like the following. SELECT A.a, count(B.id) FROM TableA A inner join TableA B on A.referId = B.id WHERE A.id in (123,2424,232...) GROUP BY A.id While executing this query The rows are filtered using where clause first and then join is performed or First...

Difference between inner join and left join if tables have matching row counts and primary keys

sql,sql-server-2008-r2,query-optimization,left-join,inner-join
In SQL Server, I'm creating a view that references several tables that should have the same row counts and primary keys, although different column data between them (hence the need to pull them all together for this particular view). I'm not always positive that they do though. I know that...

How to optimize or speed up my subquery in mysql or in php

php,mysql,sql,subquery,query-optimization
Sample query: SELECT table1.t1_id,table1.name, table2.address, ( SELECT message FROM table3 WHERE logid = table1.t1_id AND message NOT LIKE "[ SYSTEM%" ORDER BY logs DESC LIMIT 1 ) as message FROM table1 INNER JOIN table2 ON table1.t1_id = table2.t2_id WHERE table1.dateCreated BETWEEN CAST('2015-01-01' as Date) AND CAST('2015-05-30' as Date) ORDER BY...

MySQL: Partion-like function for a single set of data?

mysql,query-optimization
I have a table that has millions of records, and they utilize EFF_FROM and EFF_TO date fields to version the records. 99% of the time, when this table is queried by an application, it is only concerned with records that have an EFF_TO of 2099-12-31, or records that are active...

Avoiding fullscan on query

sql,oracle,query-optimization
Through this query SELECT * FROM (SELECT T.*, 'T'||to_char(min(T.ORDEN_CAMPO) OVER (PARTITION BY T.NOMBRE_ESQUEMA, T.NOMBRE_TABLA, T.CONDICION_ADICIONAL ORDER by T.ORDEN_CAMPO)) ALIAS1 FROM SOLICITUDES.TBL_QRY_DINAMICO_SOLDEU T WHERE TIPO_QRY = 'SOLICITUD_DEU' AND SUBTIPO_QRY = Numero_Seccion_in ) ORDER BY ORDEN_CAMPO I get a TABLE FULL SCAN when executing an explain plan. Specifically on the part where...

Multi-Column Index Behavior in MySQL with Unused Index Column

mysql,sql,query-optimization
Suppose I define the following index on a table in a MySQL database: (col1, col2, col3) I know that I get indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3). Do I also get indexed search capabilities on (col1, col3)?...

How to make faster queries on my mysql table?

mysql,optimization,query-optimization
I have the following table As you can see It has 1868155 rows. I am attempting to make a realtime graph, but It is impossible since almost any query lasts 1 or 2 seconds. For example, this query SELECT sensor.nombre, temperatura.temperatura FROM sensor, temperatura WHERE sensor.id = temperatura.idsensor ORDER BY...

Why doesn't mysql optimize this simple query

mysql,sql,database,query-optimization
Assume column pkey is the primary key for a mysql table T. Based on the EXPLAIN output: This query needs to scan only 1 row each for both DERIVED and PRIMARY select (as one would expect): SELECT * FROM (SELECT * FROM T where pkey=10) t; But this query need...

Optimizing an union SQL query

mysql,pdo,query-optimization,union
I've got a big query that all contain same types I need for a feed that I list in my app. Now the problem is that this query isn't very fast. I'm thinking that if I limit each individual union it might speed it up a bit but I'm not...

How to query a balance metric from a table that is not snapshotted

sql,sql-server,sql-server-2012,query-optimization,snapshot
Problem: Query optimization - current solution takes 10+ mins to complete, which is too long for reporting purposes. Goal: Report out active customers by day. An active customer is someone who has made a purchase in the last year. Sample Dataset: Customer_Id | Order_Date 1 | 1/1/2014 1 | 1/2/2014...

How can I move rows from one table to another where they don't exist in a third?

mysql,query-optimization,insert-select
I have three tables: grade (grade_id, grade_value, grade_date) ~100M rows grade_archive (grade_id, grade_value, grade_date) 0 rows peer_review (grade_id, peer_review_value, peer_review_date) ~10M rows I want to move all rows from table grade to grade_archive that are older than a month and are not in table peer_review. The tables are actively used...