Hi Everyone,
I´m asked to "speed up" a keyword search based on MySQL.
The material i´m given to work with is a quite large MySQL table with
1.5 mio rows, defined something like:
CREATE TABLE datarecords (
id BIGINT(20) NOT NULL auto_increment,
[3-4 other INT type columns],
keywords TEXT NOT NULL,
updated TIMESTAMP(14) NOT NULL,
PRIMARY KEY (id)) TYPE=MyISAM;
The search requires the id-fields of the datarecords table to be
returned, based on keywords within the keywords-field, like so:
SELECT id FROM datarecords WHERE (
(keywords LIKE '%someword%' AND keywords LIKE '%otherword%')
AND
(keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE
'%thisneither%')
)
The table has grown unexpectedly large and will keep growing, so a
different method for keyword searching appears to be required, since
search-speed has decreased significantly (now at about 12 secs,
depending an the number of searched keywords)
Using FULLTEXT indexing has brought no performance increase...
Question:
Does anybody have any suggestion on how to tackle this type of
problem, to get search-speed down to about 2-3 secs?
One way i´m looking at this is to create a seperate keyword-table
something like this:
CREATE TABLE testkwdlist (
keyword varchar(32) NOT NULL default '',
ids text NOT NULL,
PRIMARY KEY (keyword)
) TYPE=MyISAM;
and filling this table up with the keywords and listing the IDs in the
ids-field, doing seperate queries for the positive and negative
searches and compiling the actual resultset in code...
Does this sound it has potential for a performance boost or is it
competely insane?
Any comments gladly appreciated! I will try out and report about any
sane suggestion!
Thanks in advance,
Torfi
Some basis-data:
MySQL 4.0.15, Suse 9.0, kernel 2.4.21-215-smp4G
on dual 3,6GHz XEON, 4GB RAM, enough disk. 2 2701
I would suggest to real "select statement needed"
started a few days a go on this newsgroup.
I'll test the method next week (I hope).
Wouter
"Torfi Sackbatten" <to*******@exsiteing.com> wrote in message
news:96**************************@posting.google.c om...
: Hi Everyone,
:
: I´m asked to "speed up" a keyword search based on MySQL.
: The material i´m given to work with is a quite large MySQL table with
: 1.5 mio rows, defined something like:
:
: CREATE TABLE datarecords (
: id BIGINT(20) NOT NULL auto_increment,
: [3-4 other INT type columns],
: keywords TEXT NOT NULL,
: updated TIMESTAMP(14) NOT NULL,
: PRIMARY KEY (id)) TYPE=MyISAM;
:
: The search requires the id-fields of the datarecords table to be
: returned, based on keywords within the keywords-field, like so:
:
: SELECT id FROM datarecords WHERE (
: (keywords LIKE '%someword%' AND keywords LIKE '%otherword%')
: AND
: (keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE
: '%thisneither%')
: )
:
: The table has grown unexpectedly large and will keep growing, so a
: different method for keyword searching appears to be required, since
: search-speed has decreased significantly (now at about 12 secs,
: depending an the number of searched keywords)
:
: Using FULLTEXT indexing has brought no performance increase...
:
: Question:
: Does anybody have any suggestion on how to tackle this type of
: problem, to get search-speed down to about 2-3 secs?
:
: One way i´m looking at this is to create a seperate keyword-table
: something like this:
:
: CREATE TABLE testkwdlist (
: keyword varchar(32) NOT NULL default '',
: ids text NOT NULL,
: PRIMARY KEY (keyword)
: ) TYPE=MyISAM;
:
: and filling this table up with the keywords and listing the IDs in the
: ids-field, doing seperate queries for the positive and negative
: searches and compiling the actual resultset in code...
:
: Does this sound it has potential for a performance boost or is it
: competely insane?
:
: Any comments gladly appreciated! I will try out and report about any
: sane suggestion!
:
: Thanks in advance,
:
: Torfi
:
: Some basis-data:
: MySQL 4.0.15, Suse 9.0, kernel 2.4.21-215-smp4G
: on dual 3,6GHz XEON, 4GB RAM, enough disk.
: I would suggest to real "select statement needed"
: started a few days a go on this newsgroup.
I mean read articles with the subject "select statement needed"
seems I can't type well today.
Wouter This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Andre |
last post by:
Hello,
I am working on refactoring my companies search engine, and a
critical component of the search is that the results be displayed very
quickly. We have done enourmous work on making the...
|
by: DC |
last post by:
Hi,
I am programming a search catalogue with 200000 items (and growing). I
am currently using the SQL Server 2000 fulltext engine for this task
but it does not fit the requirements anymore.
...
|
by: samjnaa |
last post by:
Please check for sanity and approve for posting at python-dev.
In Visual Basic there is the keyword "with" which allows an object-
name to be declared as governing the following statements. For...
|
by: rlemusic |
last post by:
Hi everybody,
I’m creating a database in Access (I believe it’s 2000) to catalogue items in the archives of a small museum. I’m a total n00b as far as using Access goes, but by looking at some...
|
by: paitoon |
last post by:
Hi,
I got a little bit problem about search result in my site. When i put the keyword and click on search ....everything work fine i got the correct result but they not order by the keyword,but...
|
by: Redbeard |
last post by:
Hi All this is my first time post, be gentle.
I am looking at creating a keyword search that searches multiple fields in a Form and then filters records that match the keyword. The Form...
|
by: kanley |
last post by:
I have a main table with a text description field. In this field, its
populated with a string of data. I need to identify from this string
of data the name of the vendor using some keywords.
I...
|
by: prasath03 |
last post by:
Hi Gurus,
I am doing one website project that project contains one search module.
In that search page i have entered the keyword to search.
If i want to search the keyword with "any keyword" or...
|
by: alamodgal |
last post by:
hiiiiiii
I have a problem in highlighting searching keyword.Actually im using this function for searching
Public Function HighLight(ByVal Keyword As String, ByVal ContentFor As String)
Dim...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |