473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Select query for a search string taking more time

33
Please check this query

[PHP]
$query = "SELECT a.intNEHID AS id,a.vchNEHTitle AS title"
. "\n FROM tbNews AS a, tbNews AS b,tbMinistry AS c"
. "\n WHERE (a.vchNEHDesc LIKE '%".$searchWords."%' OR a.vchNEHTitle LIKE "
. "\n'%".$searchWords."%' OR a.vchNEHAbstract LIKE '%".$searchWords."%')"
. "\n AND a.vchNEHType IN 'News' "
. "\n AND a.chrNEHStatus != 'Delete' "
. "\n AND a.intNEHID=b.intNEHID AND a.intNEHMinstID=c.intMinstID"
. "\n GROUP BY a.intNEHVersion,a.intNEHID"
. "\n HAVING a.intNEHVersion = MAX(b.intNEHVersion)

[/PHP]



Requirement :

This is basically a search query. Need to get some news item based on the user given search word.

Problem :

We are not able to get the search results - it takes many time and the CPU usage is 99% (all time)

Let me explain about the tables :

tbNews table contains more than 10 lakhs records -
tbMinistry contains nearly 20,000 records


* Each news item having different versions (max - version is the latest one) with same intNEHID

* I have used same table (having more than 10 lakh records - tbNews) as 'a' and 'b' in this query to get the latest version of the news item

How can I optimise this query?
Dec 27 '06 #1
4 2867
subash
33
The version of mysql is - 4.1.10a-Max
Dec 27 '06 #2
ronverdonk
4,258 Expert 4TB
First: remove all newline characters from your query! Especially in this part
Expand|Select|Wrap|Line Numbers
  1.        . "\n WHERE (a.vchNEHDesc LIKE '%".$searchWords."%' OR a.vchNEHTitle LIKE "
  2.        . "\n'%".$searchWords."%' 
Be aware that any newline character between double quotes becomes part of the statementas a single newline char. And an SQL statement does not have any makeup, line a newline is, in its statement, so remove them.

Ronald :cool:
Dec 27 '06 #3
subash
33
Hi

I have used the concept of MATCH and AGAINST in the mysql query and I have got the results within 1 second.



Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT intNEHID,intNEHVersion, vchNEHTitle FROM tbMastNewsEventHighlight WHERE MATCH (vchNEHDesc, 
  3. vchNEHTitle, vchNEHAbstract ) AGAINST ('G. A. Coon') ORDER BY intNEHVersion DESC  limit 1
  4.  

This idea has got from the link http://www.petefreitag.com/item/477.cfm


Subash
Dec 28 '06 #4
ronverdonk
4,258 Expert 4TB
That is another way of doing it. You could also have used a regular expression (REGEXP) in your query.

Ronald :cool:
Dec 28 '06 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: psql-mail | last post by:
I am running a SELECT to get all tuples within a given date range. This query is much slwoer than i expected - am i missing something? I have a table 'meta' with a column 'in_date' of type...
19
by: William Wisnieski | last post by:
Hello Everyone, I have a main form with a datasheet subform that I use to query by form. After the user selects two criteria on the main form and clicks the cmdShowResults button on the main...
32
by: tshad | last post by:
Can you do a search for more that one string in another string? Something like: someString.IndexOf("something1","something2","something3",0) or would you have to do something like: if...
8
by: Jacob Arthur | last post by:
How would I go about using a custom select string that is passed from a form to the SelectCommand parameter of SqlDataSource? I tried: SelectCommand = "<% Request.Form("hdnSelect") %>" but I...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
1
by: suslikovich | last post by:
Hi all, I am getting this error when insert values from one table to another in the first table the values are varchar (10). In the second they are datetime. The format of the data is mm/dd/yyyy...
1
by: The.Daryl.Lu | last post by:
Hi, two parts to my problem if someone can help address either one or both: 1. I want to SELECT everything in the table if it matches the criteria when the query button is pressed (this is just...
2
by: Kumarswamy | last post by:
Hi, I have a problem, i have used one condition in where clause ..... but it is taking some time to return values....... so my procedure is taking lot of time....... for the same table if i query...
0
by: KevLe | last post by:
I'm building a log search function in c# for a certain management app and would like some help on the design how to solve this, here is my solution (on paper) so far: The log files are saved to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.