MySQL Forums - MySQL Query Analyzer

SELECT ... SELECT IN MYSQL (no replies)

Aravind Kumar — Tue, 11 Jun 2013 13:48:16 +0000

I am trying to write a query for mysql (Search Module) where I am facing difficulties because of the select operation to be carried out inside same table.

My Query:

(SELECT * FROM user WHERE `user_name` like '%TOM%' OR `user_name` like '%AN%'
and `login_datetime` BETWEEN '2013-01-01 00:00:00' and '2013-02-31 23:59:59')
OR
(SELECT * FROM user WHERE `user_name` like '%PHP%' OR `user_name` like '%BA%'
and `login_datetime` BETWEEN '2013-02-01 00:00:00' and '2013-03-31 23:59:59')
AND
(SELECT * FROM user WHERE `user_name` like '%SUN%' OR `user_name` like '%MOON%'
and `login_datetime` BETWEEN '2013-03-01 00:00:00' and '2013-04-31 23:59:59')
NAND
(SELECT * FROM user WHERE `user_name` like '%RAJ%' OR `user_name` like '%MUTH%'
and `login_datetime` BETWEEN '2013-04-01 00:00:00' and '2013-06-31 23:59:59')
NOR
(SELECT * FROM user WHERE `user_name` like '%BAG%' OR `user_name` like '%LAP%'
and `login_datetime` BETWEEN '2013-05-01 00:00:00' and '2013-07-31 23:59:59')
The above mentioned query is my scenario.. Where this cannot be changed, I am trying to write a Query for this.

Note:
The above query execute in a single table.
OR, NAND, NOR, AND operation can be inter-changed with according to the situation.
It depends with the user, how he needs to search.
I tried many ways to achieve the logic but I am facing issues in all phases.

Query running very slowww.. (no replies)

himanshu patel — Tue, 14 May 2013 14:51:55 +0000

Query Profiling inconsistence (no replies)

Flavio Tobias — Thu, 18 Apr 2013 16:02:22 +0000

I have a strange situation in my hands which I would like to know if someone can help me with.

I have a stored procedure that calls some functions internally, if I call the functions separately they work perfectly, but when I call the main stored it gets very slow like 50 seconds to execute and sometimes it is normal like 2 seconds to execute, with exactly same parameters. I tried to run the profile in order to see what is happening and where it is getting stuck, but the profile result is not consistent with the reality, following an example of a weird result. As you can see the stored took 22 seconds to execute, but by the profile it should not take more than 1 second to execute. So can anyone tell me what is wrong here?

mysql> CALL relatorio_consumo_veiculo (4,'2013-04-12 00:00', '2013-04-12 23:59','3200', 3.00, 2.00, 'pt-BR');
+---------+---------+-----------+---------+-----------+------------------+-----------------+------------------+-----------------+--------------------+----------------+------------------+----------------+-----------------+
| _restag | _resnom | _objidsec | _objnom | _distperc | _consumocombreal | _kmporlitroreal | _consumocombespe | _kmporlitroespe | _desviocomsumocomb | _custoporlitro | _custodesviocomb | _custokmrodado | _desvioporcento |
+---------+---------+-----------+---------+-----------+------------------+-----------------+------------------+-----------------+--------------------+----------------+------------------+----------------+-----------------+
| | | 3200 | 3200 | 444 | 153 | 2.90196 | 148 | 3 | 5 | 2 | 10 | 0.689189 | 3.37838 |
+---------+---------+-----------+---------+-----------+------------------+-----------------+------------------+-----------------+--------------------+----------------+------------------+----------------+-----------------+
1 row in set (22.59 sec)

Query OK, 0 rows affected, 1 warning (15.59 sec)

mysql> SHOW profile;
+---------------------------+----------+
| Status | Duration |
+---------------------------+----------+
| continuing inside routine | 0.000034 |
| Opening tables | 0.000028 |
| System lock | 0.000025 |
| Table lock | 0.000026 |
| init | 0.000040 |
| optimizing | 0.000032 |
| statistics | 0.000023 |
| preparing | 0.000022 |
| executing | 0.000019 |
| Sending data | 0.000183 |
| end | 0.000029 |
| query end | 0.000024 |
| closing tables | 0.000054 |
+---------------------------+----------+
13 rows in set (0.02 sec)

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 73 | 0.00008300 | CREATE TEMPORARY TABLE IF NOT EXISTS itrax.consumo_veiclo_tmp_table(
_restag VARCHAR(20),
_resnom VARCHAR(100),
_objidsec VARCHAR(20),
_objnom VARCHAR(50),
_distperc FLOAT,
_consumocombreal FLOAT,
_kmporlitroreal FLOAT,
_consumocombespe FLOAT,
_kmporlitroespe FLOA |
| 74 | 0.00020000 | DELETE FROM consumo_veiclo_tmp_table |
| 75 | 0.00033500 | SELECT pp.PONPERDISPER INTO _min_dist
FROM ponto_percorrido pp WHERE pp.VEIID=_objid AND pp.PONPERDATHOR
BETWEEN _date_ini AND _date_fim AND pp.PONPERVALID=1
ORDER BY pp.PONPERDATHOR LIMIT 1 |
| 76 | 0.00023500 | SELECT pp.PONPERDISPER INTO _max_dist
FROM ponto_percorrido pp WHERE pp.VEIID=_objid AND pp.PONPERDATHOR
BETWEEN _date_ini AND _date_fim AND pp.PONPERVALID=1
order by pp.PONPERDATHOR desc limit 1 |
| 77 | 0.00026600 | SELECT s.SENSORVALUE into _valor_min FROM sensor_valor s
INNER JOIN ponto_percorrido pp ON (s.PONPERIDENTIFY=pp.PONPERIDENTIFY)
WHERE pp.VEIID=_objid AND s.SENTYP=6 AND s.SENSORVALUE>0 AND pp.PONPERDATHOR BETWEEN _data_ini AND _data_fim
order by pp.PONPERDATHOR limit 1 |
| 78 | 0.00038800 | SELECT s.SENSORVALUE INTO _valor_max FROM sensor_valor s
INNER JOIN ponto_percorrido pp ON (s.PONPERIDENTIFY=pp.PONPERIDENTIFY)
WHERE pp.VEIID=_objid AND s.SENTYP=6 AND s.SENSORVALUE>0 AND pp.PONPERDATHOR BETWEEN _data_ini AND _data_fim
ORDER BY pp.PONPERDATHOR desc LIMIT 1 |
| 79 | 0.00008100 | SELECT _distperc/_consumocombreal INTO _kmporlitroreal |
| 80 | 0.00008100 | SELECT _distperc/_consumo_por_km_esperado INTO _consumocombespe |
| 81 | 0.00007400 | SELECT _consumocombreal-_consumocombespe INTO _desviocomsumocomb |
| 82 | 0.00007300 | SELECT _desviocomsumocomb*_valor_medio_litro_combus INTO _custodesviocomb |
| 83 | 0.00009700 | SELECT (_consumocombreal*_valor_medio_litro_combus)/_distperc INTO _custokmrodado |
| 84 | 0.00007900 | SELECT (_desviocomsumocomb/_consumocombespe)*100 INTO _desvioporcento |
| 85 | 0.00014100 | insert into consumo_veiclo_tmp_table values(_restag,_resnom,_objidsec,_objnom,_distperc,_consumocombreal,_kmporlitroreal,_consumocombespe,_consumo_por_km_esperado,_desviocomsumocomb,_valor_medio_litro_combus,_custodesviocomb,_custokmrodado,_desvioporcento) |
| 86 | 0.00019200 | select * from consumo_veiclo_tmp_table

How to trigger a query every minute (no replies)

Rajesh Swain — Sat, 13 Apr 2013 06:54:06 +0000

I have a system designed where I want a query to be triggered every minute to fetch necessary records from the database and perform certain actions.It would be a back end process and certain notifications would be sent to the concerned mail ids from the data fetched.

Plz suggest how to proceed for the query.

How to subtract 1 minute from time (1 reply)

divya vani — Wed, 03 Apr 2013 14:39:57 +0000

Hi experts,

I have a query contains from time and to time parameters.
I need to subtract 1 minute from totime parameter.
I used totime-1.
It is working in database level and it is not working in my crystal reports.I mean it is subtracting 1 hour in report level.
Can any one please tell me other formula for subtracting a minute.
Please find the where condition in my query

where
ccsr.store_date = {?Transaction Date}
and ccsr.value_type='B'
and (cast(time_format(ccsr.transaction_time,'%H') as unsigned) between
{?From Time} and ({?To Time}-1))
group by
ccsr.brand_code
) cons

Thanks in advance,
divya

Stored Procedure With XML Datas (no replies)

Senthil V — Fri, 15 Mar 2013 09:27:30 +0000

Hi,

Please let me know how to read the values from the below xml.

set @xmlBooks = '

';

Here Category and Item will be dynamically. I want to read the each category and item values one by one. I need this code in Stored Procedure.

I have done the code to find the category as below
DECLARE CCount INT;
DECLARE rowNum INT DEFAULT 1;

SET CCount= extractvalue(xmlBooks, 'count(//category)');
WHILE rowNum <= CCount DO

SELECT
extractvalue(xmlBooks , '//child::*[$rowNum]/@categoryid'),
extractvalue(xmlBooks , '//child::*[$rowNum]/@catcode'),
extractvalue(xmlBooks , '//child::*[$rowNum]/@catgeoryname');

SET rowNum = rowNum + 1;
END WHILE;

Thanks,
Senthil.

How to use table alias in select (no replies)

Pankaj Kumar — Mon, 04 Mar 2013 06:14:34 +0000

Hello all,

I have a query given below:

SELECT A,
B,
( SELECT C
FROM test2 t2
WHERE t.A = t2.A
ORDER BY A DESC LIMIT 1
)

FROM
Test t

I am using mysql yog from executing this query. It is causing an error. Please let me know the correct implementation of this query.

Thanks in advance

Query Performance (no replies)

James Cobban — Tue, 26 Feb 2013 23:19:19 +0000

I am having difficulty figuring out how to improve the performance of a query. The query is used to obtain a summary of individuals from a family tree database based upon their names. Note that each individual may have more than one name. For example a married person has both a birth name and a married name (which may be the same). A person may also have an adopted name, or a nom de plume, etc.

SELECT DISTINCT tblNX.IDIR, tblNX.Surname,
			  tblIR.Surname, tblNX.GivenName,
			  tblIR.BirthD, tblIR.DeathD, tblIR.Gender
			FROM tblNX JOIN tblIR on tblIR.IDIR=tblNX.IDIR
			WHERE ((tblNX.Surname='Campbell' AND 
			                         tblNX.GivenName>='John') OR
			             tblNX.Surname>'Campbell') AND
                                                  tblNX.`Order`>=0 
			ORDER BY tblNX.SurName, tblNX.GivenName,
				 tblIR.BirthSD, tblIR.DeathSD LIMIT 50

The table containing the basic data on the individual is tblIR:

CREATE TABLE `tblIR` ( 
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
  `IDIR` INT(10) UNSIGNED DEFAULT NULL, 
  `FSID` VARCHAR(255) DEFAULT NULL, 
  `Surname` VARCHAR(120) DEFAULT NULL, 
  `SoundsLike` VARCHAR(4) DEFAULT NULL, 
  `GivenName` VARCHAR(120) DEFAULT NULL, 
  `Prefix` VARCHAR(120) DEFAULT NULL, 
  `Title` VARCHAR(120) DEFAULT NULL, 
  `NameNote` LONGTEXT, 
  `Gender` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, 
  `BirthD` VARCHAR(100) DEFAULT NULL, 
  `BirthSD` INT(10) NOT NULL DEFAULT -99999999, 
  `IDLRBirth` INT(10) UNSIGNED DEFAULT NULL, 
  `ChrisD` VARCHAR(100) DEFAULT NULL, 
  `ChrisSD` INT(10) NOT NULL DEFAULT -99999999, 
  `IDLRChris` INT(10) UNSIGNED DEFAULT NULL, 
  `ChrTerm` VARCHAR(100) DEFAULT NULL, 
  `DeathD` VARCHAR(100) DEFAULT NULL, 
  `DeathSD` INT(10) NOT NULL DEFAULT -99999999, 
  `IDLRDeath` INT(10) UNSIGNED DEFAULT NULL, 
  `BuriedD` VARCHAR(100) DEFAULT NULL, 
  `BuriedSD` INT(10) NOT NULL DEFAULT -99999999, 
  `IDLRBuried` INT(10) UNSIGNED DEFAULT NULL, 
  ...
  PRIMARY KEY (`ID`), 
  UNIQUE KEY `IDIR` (`IDIR`), 
  KEY `Surname` (`Surname`), 
  KEY `GivenName` (`GivenName`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8

The nominal index, which includes all of the alternative names, is in table tblNX:

CREATE TABLE `tblNX` ( 
  `IDNX` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
  `IDNR` INT(10) UNSIGNED DEFAULT NULL, 
  `IDIR` INT(10) UNSIGNED DEFAULT NULL, 
  `Order` smallint(5) NOT NULL DEFAULT 0, 
  `MarriedNameCreatedBy` TINYINT(3) UNSIGNED DEFAULT NULL, 
  `MarriedNameMarIDID` INT(10) UNSIGNED DEFAULT NULL, 
  `Prefix` VARCHAR(120) DEFAULT NULL, 
  `Title` VARCHAR(120) DEFAULT NULL, 
  `Surname` VARCHAR(120) NOT NULL DEFAULT '', 
  `GivenName` VARCHAR(120) NOT NULL DEFAULT '', 
  `SoundsLike` VARCHAR(4) NOT NULL DEFAULT '', 
  `UserRef` VARCHAR(50) DEFAULT NULL, 
  `AKANote` LONGTEXT, 
  `PreferredAKA` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, 
  `BirthSD` INT(10) NOT NULL DEFAULT -99999999, 
  `SrchTag` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, 
  `qsTag` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0, 
  PRIMARY KEY (`IDNX`), 
  KEY `IDIR` (`IDIR`), 
  KEY `Surname` (`Surname`), 
  KEY `GivenName` (`GivenName`), 
  KEY `SoundsLike` (`SoundsLike`), 
) ENGINE=MyISAM DEFAULT CHARSET=utf8

problem
Explain on this query returns:

+----+-------------+-------+------+------------------------+------+---------+------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys          | key  | key_len | ref                          | rows  | Extra                           |
+----+-------------+-------+------+------------------------+------+---------+------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | tblIR | ALL  | IDIR                   | NULL | NULL    | NULL                         | 67863 | Using temporary; Using filesort |
|  1 | SIMPLE      | tblNX | ref  | IDIR,Surname,GivenName | IDIR | 5       | jcobban_genealogy.tblIR.IDIR |     1 | Using where                     |
+----+-------------+-------+------+------------------------+------+---------+------------------------------+-------+---------------------------------+

As I understand this, the server is performing a linear search of the entire table tblIR, which currently has 67863 rows, and then uses IDIR to examine the associated rows of tblNX. This has poor performance and will only get worse as more rows are added. I do not understand why the primary search is not on tblNX, which is the only table referenced in the WHERE clause, or why the key of tblIR, which is explicitly mentioned in the JOIN, is not being used.

If I extract the core of the query I get:

explain select idir, surname, givenname from tblNX where ((surname='Campbell' and givenname>='John') or surname>'Campbell') order by surname, givenname, birthsd limit 20;
+----+-------------+-------+-------+-------------------+---------+---------+------+-------+-----------------------------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref  | rows  | Extra                       |
+----+-------------+-------+-------+-------------------+---------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | tblNX | range | Surname,GivenName | Surname | 363     | NULL | 78515 | Using where; Using filesort |
+----+-------------+-------+-------+-------------------+---------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

This does show the query exploiting the surname index, although not the given name index. The performance of this query is probably acceptable. The problem is that while tblNX contains all of the name information it does not contain the "maiden" surname, gender, birth date, and death date information which I need to display to the end user, which is why I did the join of tblIR.

How do I restructure this query to get MySQL to do the tblNX query exploiting the surname index and then join the tblIR information into the response?

Select Last Distinct record (no replies)

Moh Methew — Thu, 07 Feb 2013 08:19:44 +0000

i have a table with three fields
id,bank_id,amount

it has alot of data....how i can select last entry for all distinct banks ?
lets say(Below code has not tested,it is from sqlserver)
IF OBJECT_ID('TempDB..#mytable1','U') IS NOT NULL
DROP TABLE #mytable1
CREATE TABLE #mytable1
(
Id INT IDENTITY(1,1) primary key,
bank_id INT,
amount BIGINT
)

INSERT INTO #mytable1
(bank_id,amount )
SELECT 1,'2000' UNION ALL
SELECT 2,'1000' UNION ALL
SELECT 3,'6000' UNION ALL
SELECT 2,'100' UNION ALL
SELECT 2,'500' UNION ALL
SELECT 1,'900';

i need this result
bank_id amount
1 900
2 500
3 6000

Query with duplicate key (URGENT) (no replies)

Simone Messina — Fri, 18 Jan 2013 08:02:17 +0000

Hi,
i have a DB called "csv_db" with a tbale called "reportinc".
The tabale have this column:

`ID incidente`, `Aperto da`, `Stato`, `Categoria`, `Area`, `Sottoarea`, `Titolo`, `Priorità`, `Data/ora apertura`, `Data/Ora risoluzione`, `Data/ora chiusura`, `Inizio Interruzione di servizio`, `Fine interruzione di servizio`, `Conteggio riassegnazioni`, `Gruppo di assegnazione`, `Assegnatario`, `Risolto da`, `Gruppo risoluzione`, `Chiuso da`, `Gruppo di chiusura`, `ID interazione`, `Id Remedy`, `Descrizione`, `Soluzione`, `Servizio Interessato`, `Servizi Interessati`, `CI interessato`, `CI operativo`, `Ultimo aggiornamento da`

The primary and unique key is `ID incidente`

I need to import each day from .CSV (comma separated) and:

1) Inseret new rows with new value (`ID incidente` also)
2) Update existing rows in the list.

I tried a query smilar to:

LOAD DATA LOCAL INFILE 'd:\export.csv'
INSERT INTO `reportinc` (`ID incidente`, `Aperto da`, `Stato`, `Categoria`, `Area`, `Sottoarea`, `Titolo`, `Priorità`, `Data/ora apertura`, `Data/Ora risoluzione`, `Data/ora chiusura`, `Inizio Interruzione di servizio`, `Fine interruzione di servizio`, `Conteggio riassegnazioni`, `Gruppo di assegnazione`, `Assegnatario`, `Risolto da`, `Gruppo risoluzione`, `Chiuso da`, `Gruppo di chiusura`, `ID interazione`, `Id Remedy`, `Descrizione`, `Soluzione`, `Servizio Interessato`, `Servizi Interessati`, `CI interessato`, `CI operativo`, `Ultimo aggiornamento da`) VALUES ($ID incidente, $Aperto da, $Stato, $Categoria, $Area, $Sottoarea, $Titolo, $Priorità, $Data/ora apertura, $Data/Ora risoluzione, $Data/ora chiusura, $Inizio Interruzione di servizio, $Fine interruzione di servizio, $Conteggio riassegnazioni, $Gruppo di assegnazione, $Assegnatario, $Risolto da, $Gruppo risoluzione, $Chiuso da, $Gruppo di chiusura, $ID interazione, $Id Remedy, $Descrizione, $Soluzione, $Servizio Interessato, $Servizi Interessati, $CI interessato, $CI operativo, $Ultimo aggiornamento da)
ON DUPLICATE KEY UPDATE active = 1

But i snot working.
I need to update rows if duplicate, and NOT append.

How to?
Please me me (newbie)

Thanks in advance.

Tuning help with OR and UNION (2 replies)

Jed Walker — Thu, 17 Jan 2013 17:23:55 +0000

I have a slow query that I'm trying to tune and am hoping someone might be able to assit. It is a 2rd party product so I can't change the query.

SELECT *
FROM `jobs`
WHERE (
(jobs.pre = 1 OR jobs.post = 1)
AND DATE_ADD(UTC_TIMESTAMP(), INTERVAL -10800 SECOND) > jobs.updated_at
)
ORDER BY jobs.priority DESC, jobs.created_at ASC
LIMIT 20;

The columns pre and post are almost always 0. I tried an index on updated_at, and it uses it but still select almost half the rows to perform the query, so it is just as slow as the full table scan. I then tried adding indexes on many combinations of update_at/pre/post, pre, post, pre/post, etc. Even with "force index" no luck. Given the highly skewed data, pre and post seem optimal, so...

I then created an index on pre and another on post and wrote the query this way:

SELECT *
FROM `jobs`
WHERE jobs.pre = 1 AND DATE_ADD(UTC_TIMESTAMP(), INTERVAL -10800 SECOND) > jobs.updated_at
union
SELECT *
FROM `jobs`
WHERE jobs.post = 1 AND DATE_ADD(UTC_TIMESTAMP(), INTERVAL -10800 SECOND) > jobs.updated_at
ORDER BY priority DESC, created_at ASC LIMIT 20;

This uses both indexes and retrieves only 1 row per query. Very, very fast.

Is there a way I can convince the originall query to work as it does in the union form?

Enum or its Equivalent in mysql!! (1 reply)

Tarun Gurmukhani — Thu, 17 Jan 2013 21:43:23 +0000

Hi,
I have one table in my mysql database, where one of the fields is Country. I am in process of developing GEO tracking system where users who upload the video will have the facility to restrict (like allow/deny) the video for any country.

Since there are so many countries, Is there any possibility that whenever someone uploads a video and selects a country to allow/deny the video from a particular GEO location, the country gets automatically added to the database.

The catch here is, If a user selects more that one country to allow/deny, I do not want more than one entry about the country in my database table.

So I was looking out for a solution in Enum. If I can create a column (as country) which can takes care of multiple values all dynamically, one of my purpose will be resolved. Something like:

user_id Country Video_Id type
1 Ind,Pak,GB 20145 Allow
2 Ind,USA 20176 Deny

Also, If I want to search the Countries on the basis on the basis of either type/Video_ID, I should get the complete set of Country List.

Your Help is appreciated!!

Thanks

Query Optimization (no replies)

Tarun Gurmukhani — Fri, 11 Jan 2013 07:27:48 +0000

Hi,
I have a query, which is being used at 10 places. Per query it is taking around 5-6 Ms to execute and generate the result.
Only Id(One of the fields in the query/database) is getting changed (dynamically), but rest all of the parameters are same.
Can someone please throw some insight if I can optimize the complete set (all 10) so that total execution for all the 10 queries can be reduced to 10 Ms.

Your help is appreciated!!

Thanks

Need help! To find the query for my task (no replies)

Ruin Dash — Thu, 10 Jan 2013 03:23:13 +0000

print all columns for where datetime=max(datetime) group by date(datetime) in Mysql (1 reply)

Mannoj Kumar — Thu, 13 Dec 2012 14:00:53 +0000

I have problem in selecting other columns for the max(datetime) records. In layman terms I need to get max(DialDateTime) records for all the dates in Mysql.

mysql> select max(DialDateTime) as max from log_AP group by date(DialDateTime) ;
+---------------------+
| max |
+---------------------+
| 2012-12-03 07:37:26 |
| 2012-12-04 07:37:04 |
| 2012-12-05 07:37:04 |
| 2012-12-06 07:37:04 |
| 2012-12-07 07:37:04 |
| 2012-12-08 07:37:04 |
| 2012-12-09 07:37:04 |
+---------------------+

Finding loop in indexes (1 reply)

Seema Bhatia — Tue, 11 Dec 2012 23:11:19 +0000

A simple query
Update Table1 set col1 = 12 where col2='13' which is taking long long time like 4 or more minutes even though table has less than 100 rows.
Memory consumption - 99%
CPU - 20%
However, table has so many joins with other tables, indexes, foreign keys, etc. All tables are so interrelated like x is connected to y and then y connected to z and then z comes back to x.

So how could I check that it is forming a loop while doing update or some lock problems.

search criteria - int datatype (1 reply)

Seema Bhatia — Tue, 11 Dec 2012 23:11:41 +0000

Query - col1 and col2 are both int
col2 is primary
col1 also has non clustered index
1) update table1 set col1 = 12 where col2 = '13'

2) update table1 set col1 = '12' where col2 = 13

How these queries will behave?
Are there performance issues as numbers are enclosed in quotes.

I know at mysql will do implicit conversion. Will it take benefit of indexes in both the queries.

select query with ident_current function to retreive the last inserted record. (1 reply)

priya sarin — Tue, 11 Dec 2012 23:12:02 +0000

Hello Friends,

I am working on .net application. For database previously I was using MS SQL, But nowadays as per requirement, I am using the mysql.

While working with MS SQL, I had used indent_Current function to retrieve the last insert record id "(SELECT IDENT_CURRENT('country') As Country_Uid)". But in Mysql it gives me error.. I tried to found on google but didtnt get the solution. Please help me. Any reply are highly appreciated.

Thanks in advance.

Need a little help with this query (no replies)

Amardulguun Sandag — Wed, 05 Dec 2012 04:00:54 +0000

I am trying to edit this report to include the product sell price. I am a novice at MySQL so if any users familiar would help that would be great!
I am trying to get the actual discount amount given from individual sales and I believe that I can get it by subtracting the ticketline.price from the product.pricesell for each row to get the actual discount amount, and then sum up the total discount. How would I join the product.pricesell of the item to this code?
report = new com.openbravo.pos.reports.PanelReportBean();
report.setTitleKey("Menu.SalesByCustomer");
report.setReport("/com/openbravo/reports/salebycustomer");
report.setResourceBundle("com/openbravo/reports/salebycustomer_messages");
report.setSentence("SELECT " +
"PEOPLE.NAME AS CNAME, " +
"RECEIPTS.DATENEW, " +
"TICKETS.TICKETID, " +
"PRODUCTS.NAME AS PNAME, " +
"TICKETS.TICKETTYPE, " +
"SUM(TICKETLINES.UNITS) AS UNITS, " +
"SUM(TICKETLINES.UNITS * TICKETLINES.PRICE) AS TOTAL " +
"FROM RECEIPTS, PEOPLE, TICKETS, TICKETLINES LEFT OUTER JOIN PRODUCTS ON TICKETLINES.PRODUCT = PRODUCTS.ID " +
"WHERE PEOPLE.ID = TICKETS.PERSON AND TICKETLINES.PRODUCT = PRODUCTS.ID AND RECEIPTS.ID = TICKETS.ID AND TICKETS.ID = TICKETLINES.TICKET AND ?(QBF_FILTER) " +
"GROUP BY PEOPLE.NAME, RECEIPTS.DATENEW, TICKETS.TICKETID, PRODUCTS.NAME, TICKETS.TICKETTYPE " +
"ORDER BY PEOPLE.NAME, PRODUCTS.NAME, RECEIPTS.DATENEW");
report.addParameter("RECEIPTS.DATENEW");
report.addParameter("RECEIPTS.DATENEW");
paramdates = new com.openbravo.pos.reports.JParamsDatesInterval();
paramdates.setEndDate(com.openbravo.beans.DateUtils.getToday());
report.addQBFFilter(paramdates);
report.addField("CNAME", com.openbravo.data.loader.Datas.STRING);
report.addField("DATENEW", com.openbravo.data.loader.Datas.TIMESTAMP);
report.addField("TICKETID", com.openbravo.data.loader.Datas.STRING);
report.addField("PNAME", com.openbravo.data.loader.Datas.STRING);
report.addField("TICKETTYPE", com.openbravo.data.loader.Datas.INT);
report.addField("UNITS", com.openbravo.data.loader.Datas.DOUBLE);
report.addField("TOTAL", com.openbravo.data.loader.Datas.DOUBLE);
report;

Help :Is this Query right? How to Get the Result: Which to use union OR join AND How? (1 reply)

Vinod sl — Tue, 04 Dec 2012 13:27:28 +0000

Can you please help me?

QUERY1:

SELECT COUNT(T1.ID) AS T1_COUNT,createdtime AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY createdtime ORDER BY T1_TIME DESC

GETTING RESULT:
T1_COUNT ---------- T1_TIME
3 ---------------- 2010-09-25
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11

QUERY2:

SELECT COUNT(T1.ID) AS T2_COUNT,createdtime AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY createdtime ORDER BY T2_TIME DESC

GETTING RESULT:
T2_COUNT ---------------- T2_TIME
1 ---------------- 2010-09-25
2 ---------------- 2010-09-18

Sample Query:
SELECT * FROM (
SELECT COUNT(T1.ID) AS T1_COUNT,createdtime AS T1_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY createdtime
UNION
SELECT COUNT(T1.ID) AS T2_COUNT,createdtime AS T2_TIME FROM TABLE_1 AS T1,TABLE_2 AS T2 WHERE T2.ID=T1.ID AND T1.STATE != 'C' AND T1.STATE ='E' AND T1.TIME BETWEEN '2010-01-01 00:00:00' AND '2010-09-25 23:59:59' AND PRTY='F' GROUP BY createdtime AS inLineView ORDER BY T1_TIME DESC

RESULT:
T1_COUNT ---------- T1_TIME
1 ---------------- 2010-09-25
3 ---------------- 2010-09-25
2 ---------------- 2010-09-18
4 ---------------- 2010-09-18
2 ---------------- 2010-09-11
(Please Give me idea or correct the sample Query to get Result like follows)
NEEDED RESULT:
T1_COUNT ----- T1_TIME ------ T2_COUNT ------ T2_TIME
1 ------------- 2010-09-25 ----- 3 ----------- 2010-09-25
2 ------------- 2010-09-18 ----- 4 ----------- 2010-09-18
2 ------------- 2010-09-11

partition in the table (1 reply)

sumit vedi — Tue, 04 Dec 2012 13:28:30 +0000

Hello,

I want to create a table in which data is store in partition as well as sub-partition.

Suppose I want to insert the data under the partition (P_name) of the table. If partition is already exist then insert the data under this partition otherwise create the partition and then insert the data.

Can you please help me on the same problem for sub-partition.

Thanks in advance.

return 0 (1 reply)

Kelly Brace — Tue, 04 Dec 2012 13:27:55 +0000

I am trying to return all portfolios, even if they don not have any that have NEWBIZ (return 0 with portfolio). I tried IFNULL. Not sure how to go about it. Any advice?

select ifnull(count(*),0) ,d.portfolio from dbase d, portfolios pf where statusname='NEWBIZ'
and pf.portfolio=d.portfolio 
group by pf.portfolio order by pf.placementdate;

MySql Cache Dependency (no replies)

sagar pavan — Fri, 09 Nov 2012 06:09:52 +0000

Hello,

In my project we developed cache on a table from php side, currently, the cache is update for every 1min even though, there is no updates on that table. So, my requirment is to update cache only when there is update on that table. Its like Sql Cache Dependency, but i din't find anywhere Sql Cache Dependency in mysql.Please help me how do Sql Cache Dependency in mysql.

How to get query plan for a query using mysql command line tool (no replies)

Javin Paul — Sun, 04 Nov 2012 05:42:25 +0000

I am using mysql command line client, which comes along mysql 5.5 installer in windows. I want to check how my query is executed, where its spending time e.g. something like query plan available in SQL Server. Is there way I can see using mysql command line client ?

Thanks

Complex queries (no replies)

Marian Nedelcu — Mon, 29 Oct 2012 11:15:22 +0000

I have a database made by three tables: athlets, competitions and results, with following colums:

- athlets (id, name)
- competitions (id, name, date)
- results (place, id_athlets, id_competition, ranking_points)

with following relationships:
- results.id_couple=athlets.id;
- results.id_competition=competitions.id

I need to make a query wich give the lastest (by competitions.date) ranking_points for each athlets.id

create table from multiple tables (3 replies)

Kelly Brace — Tue, 04 Dec 2012 13:28:44 +0000

I'm sorry I just realized I didn't explain what I was trying to do.

I'm trying to create a report procedure. I created a report table, liqReports and I need columns/data from 3 other tables. This is what I have done, it's wrong. When I run the 3 selects alone, I get the correct data. I thought I could just do 3 inserts, then I thought I could do updates. Please advise.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `liqReport`()
BEGIN
truncate table liqReports;
create table if not exists liqReports (face_amt decimal(12,2), pending_amt decimal(12,2), posted_amt decimal(12,2), portfolio_name varchar(24),place_date date);

insert into liqReports(face_amt, pending_amt, posted_amt, portfolio_name, place_date)
select 0, 0, sum(p.paymentamount), pf.portfolio, pf.placementdate from payments p,portfolios pf where p.portfolio=pf.portfolio and p.paymentstatus='POSTED'
group by pf.portfolio order by pf.placementdate;

update liqReports set pending_amt=(
select sum(p.paymentamount) from payments p,liqReports lr where p.portfolio=lr.portfolio_name and p.paymentstatus='PENDING'
group by lr.portfolio_name order by lr.place_date);

insert into liqReports(face_amt, pending_amt, posted_amt, portfolio_name, place_date)
select sum(d.originalbalance), lr.pending_amt, lr.posted_amt, lr.portfolio_name, lr.place_date from dbase d,liqReports lr where d.portfolio=lr.portfolio_name
group by lr.portfolio_name order by lr.place_date;

END

How to convert a Longblob to varchar? (1 reply)

radhika rao — Fri, 26 Oct 2012 20:09:19 +0000

Hi,
Can anyone please let me know where i am going wrong with the below query?

select * from openquery(MYSQL_LINKED, 'select
t.Id as Ticket_id,
max(tr.Created),
cast(a.content as varchar(50)) as Attachment_content,
oc.ObjectType,
oc.Content as ObjectCF_content
from rt.Tickets t
inner join rt.Transactions tr on tr.ObjectId = t.Id
inner join rt.Attachments a on a.TransactionId = tr.Id
inner join rt.Users u on u.Id = a.Creator
inner join rt.ObjectCustomFieldValues oc on oc.ObjectId = tr.ObjectId
where Status in ("pending","open","new")
and a.Content not like "%WordDocument%"
and a.Content not like "%word%" and a.Content not like "%div%"
and a.Content not like "%http%"
and oc.CustomField in ("2","17")
and t.Id = "6179"')

I get below error:
OLE DB provider "MSDASQL" for linked server "MYSQL_LINKED" returned message "[MySQL][ODBC 5.2(w) Driver][mysqld-5.0.92-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'utf-8) as Attachment_content,
(a.Id) as Attachement_Id,
(a.TransactionId),
a.' at line 14".
Msg 7350, Level 16, State 2, Line 2
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "MYSQL_LINKED".

Thanks!!

Update table from one database with data from another database (1 reply)

Stuff Dole — Fri, 26 Oct 2012 12:26:35 +0000

Hello I have 2 MySQL databases on the same server with same structure and same data. Occasionally data in one database are changed for 7 days and then should be back to the same value as in first database. Through PhpMyAdmin I am trying to write statement that should do thi but something I am missing. I have databases: db1_local and db2_local Table gitem column gbaseprice (should be synchronized from db1 to db2) column gitemcode (has same value in both databases and synchronizing should be followed with this parameter so that id's don't mess up) Appreciate any help

Here is statement e.g. SQL statement that run in db2

UPDATE gitem
SET gbaseprice=db1_local.gbaseprice
FROM
db1_local.gitem(`gbaseprice`)
INNER JOIN db1_local.gitemtable
ON db1_local.gitem(`gitemcode`)=db2_local.gitem(`gitemcode`)

mysql query help (3 replies)

Suleman Butt — Fri, 02 Nov 2012 10:23:43 +0000

Hi all,
I am finding it difficult to build one mysql query.

I have a table messages_stats_analysis_ota, with 4 columns: [brand_code, date_time_from, date_time_to, success_count].

I want the value inside 'success_count' against individual 'brand_code', which lie within the last 7 days from the current date.

u2, 01.10.2012, 07.10.2012, 100
u2, 02.10.2012, 08.10.2012, 100
u2, 03.10.2012, 07.10.2012, 100
u2, 08.10.2012, 14.10.2012, 50
fr, 01.10.2012, 07.10.2012, 200

Now lets assume, the current date is 08.10.2012.

I want only the follwoing 2 rows to be retrieved:

u2, 01.10.2012, 07.10.2012, 100
fr, 01.10.2012, 07.10.2012, 200

I tried to build this query:

SELECT * FROM messages_stats_analysis_ota WHERE date_time_from >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

but it gives me, the follwoing, which i dont want:

u2, 01.10.2012, 07.10.2012, 100
u2, 02.10.2012, 08.10.2012, 100
u2, 03.10.2012, 07.10.2012, 100
fr, 01.10.2012, 07.10.2012, 200

Assumtpion is current date is: 08.10.2012.

Coudl someone please help me out?

Thanks.

order by alphanumeric in mysql (1 reply)

vaibhav shahu — Thu, 11 Oct 2012 13:59:29 +0000

Can you help me?

my query is
SELECT num FROM sortnum ORDER BY lpad(num, 10, 0)

result of this query is not good
here is the result
1
2
3
4
5
6
7
8
9
10
11
12
13
14
A1
A2
A3
A4
B1
A10
A11
B10

What is the best query for this type of data
i want number first, then data starting with a , then with b

Thanks in advance