query

MySQL slow query in rare cases even though index is being used

Hi, I have a query in a large MySQL table (>4 million rows). This query is used in a stored procedure and it searches by surname and another numeric field. When I use different combinations of these search parameters, I get quick results (between 1 and 2s) but with some particular values, I get a query which takes 9s to return results o...

find likewise data from two tables in mysql

i have two tables in my database one is A other one is B A is having few fields in which three are id,name,group B is having feilds like id,title,description, etc. i have to search the id's of title and description that are having data similar to table A's name or group and then have to insert the id's in a field of table A. For exampl...

Most efficient way of selecting the changes between timestamped snapshots

I have a table that holds data about items that existed at a certain time - regular snapshots taken. Simple example: Timestamp ID 1 A 1 B 2 A 2 B 2 C 3 A 3 D 4 D 4 E In this case, Item C gets created sometime between snapshot 1 and 2 and somet...

How can I make a Django query for the first occurrence of a foreign key in a column?

Basically, I have a table with a bunch of foreign keys and I'm trying to query only the first occurrence of a particular key by the "created" field. Using the Blog/Entry example, if the Entry model has a foreign key to Blog and a foreign key to User, then how can I construct a query to select all Entries in which a particular User has wr...

How to write that special select query

Hi , I have a post class : class Post { public int ID; public int? ParentID; } The only difference between answer and question in post class is that question has parend id equals null. And I want to write one query which always return question and it's answers no matter i pass to it ID of question or ID of answer for example: I h...

SQL Query - replace hardcoded values... (MySQL)

Hi, We use a monitoring solution (zabbix). It stores trends data (MySQL 5.X) using a table called trends_uint with the following structure (simplified): itemid clock value_avg 1 1238774400 100 1 1250773900 70 1 1250773200 50 The clock field stores datetime values using Unix time. I'm trying to...

MySQL. I stink at RegExs. Just need one to tell me if string starts with a numeral

I need a MySQL query w/ Regex to tell me if my string's first character is a number from 0 to 9. ...

Query works ok in phpmyadmin but not with mysql_query php function

I have this sql query: SELECT DISTINCT r.uri uri FROM resource r INNER JOIN object o ON o.idResource = r.idResource WHERE r.type = 2 AND r.idResource IN ( SELECT DISTINCT r1.idResource FROM object o1 INNER JOIN resource r1 ON r1.idResource = o1.idResource INNER JOIN class c1 ON c1.idClass = o1.idClass INNER JOIN property p2 ON...

how to select a row where one of several columns equals a certain value?

Say I have a table that includes column A, column B and column C. How do I write I query that selects all rows where either column A OR column B OR column C equals a certain value? Thanks. Update: I think forgot to mention my confusion. Say there is another column (column 1) and I need to select based on the following logic: ...where...

What is the problem in this code?

I have the following code: function query_tip($title,$desc) { $q1=("SELECT id, company, name FROM abc where ('$title' LIKE CONCAT('% ',company,' %') or '$desc' LIKE CONCAT('% ',company,' %') or '$title' LIKE CONCAT('% ',name,' %') or '$desc' LIKE CONCAT('% ',name,'...

Sql query to find MAx row in subqury

Hi All. These are my tables:User,Product,DiscountGroup,DiscountUser,DiscountProduct. DiscountProduct: id discountGroupId productId discount --- -------------- --------- ------- 1 2 8 2000 2 3 8 1000 3 2 4 4500 Dis...

Search MySQL Query

I have the below Code SELECT * FROM table WHERE MATCH(message) AGAINST ('Hello*') If the message string is like this "HelloWhatsHappening" It dosent Work return anything But if i have "Hello WhatsHappening" It works fine It will also work if the string is as such: "Hello= WhatsHappening" Any Ideas? ...

How to parse out quoted values from a column with sql

I have a field that has values like this... s:10:"03/16/1983"; s:4:"Male"; s:2:"No"; I'd like to parse out the quoted values. its going to be some sort of combination of substr and instr its the doublequote i have issues finding its position. i have tried things like select substr(field_value, instr(field_value,'"'),instr(field_val...

Read a specific Element Value based on a specific Attribute in Linq?

I'm just learning Linq, and stuck on what I hope is fairly simple. My xml document is like: <?xml version="1.0" encoding="utf-8"?> <XDOC> ... <ItemsDetail> <Item name="Item1"> <data1> <Data type="classA">55</Data> <Data type="classB">66</Data> </data1> <...

mysql combine query

need help, how can we combine these query? ive a problem in join statement.. SELECT hc.id_company, hc.name, hc.email, dc.country, dc.date_join, sum(if(ha.type='ADMIN',1,0)) as ADMIN, sum(if(length(ha.type)=6,1,0)) as STAFF, sum(if(ha.type='CUST',1,0)) as CUST from h_company hc, d_company dc JOIN h_adminstaffcust ha USING (id_company...

SQL Server SELECT Performance with JOINS

Hi, in SQL Server, I would like to know if there is any performance difference between doing this (A)... SELECT a.field1, a.field2, a.field3, b.field1 FROM table1 a INNER JOIN table2 b ON a.IDfield = b.IDfield and this (B)... SELECT a.field1, a.field2, a.field3, b.field1 FROM table1 a, table2 b WHERE a.IDfield = b.IDfield Well, thi...

Creating Breadcrumbs structure from SQL Query

Hi, I am using SQL Query and below are the tables. Organization OrgID Name RAOGID RAOID SubGroupID 1 Org RAOG 1 NULL NULL 2 Org RAO NULL 1 NULL 3 Org Sub Group NULL NULL 1 RAOG RAOGID AccredID 1 2 RAO RAOID RAOGID 1 1 Sub Group SubG...

sp_send_dbmail will not send query results

I've tried every avenue on every damn forum suggested, but to no avail! Need to send results of SQLPERF(logspace), that have been stored in a table, via sp_send_dbmail to recipient. Step 2 of job is where failure occurs. Please help! EXEC msdb.dbo.sp_send_dbmail @profile_name= 'MyDBA', @recipients= '[email protected]', @subject='Log War...

SQL Find Possible Duplicates

Hi I need SQL code that will identify possible duplicates in a table. Lets say my table has 4 columns: ID (primary key) Date1 Date2 GroupID (Date1, Date2, GroupID) form a unique key. This table gets populated with blocks of data at a time, and it often happens that a new block is loaded in that contains a number of records that are...

Can't assign FULLTEXT index on my table

I am trying to implement a search function on a database table using query expansion, and I'm aware that I have to add the FULLTEXT index on the fields I want to become searchable. I have tried to do this and got no errors from PHPMyAdmin, but when I examine the structure of the table the cardinality of the FULLTEXT index is 0. I have tr...