473,890 Members | 1,173 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Google style search

i'm building a website for a band and each section of the site, i want
to have google style searches. mainly, for the news and tour section.
i'm using mysql 4.0.26 and php 4.0 for all web content. below is the
query i created for the tour search (this example looks for 3 search
items). php builds this dynamically but for each search term, there is
an extra statement in the where clause. additionally, there are 5
table joins but i left them out for sake of brevity.

after building this query, i've been reading up on full text searches
in mysql. BUT, i have seen some people complain that it is slow
(however, i do note that they are speaking of tables with millions of
records). also, i would like to include the date within the search but
i assume i'd need to create another field in each table that is a
textual representation of the date field already in the table and then
create a fulltext index on that field

so, my question is, should i stick with this query or build the indexes
and rewrite full text queries? or, is there another method i should
employ -- is my query slow/dumb/ineffecient? this is for a local band
so i do not expect the database of shows to ever reach more than
1000-2000 (and probably about that for the linked tables of other bands
and venues). also, i don't imagine the traffic on this site to ever be
more than a handfull of users at a time. however, i also hate
inefficient code and if the band gains a lot of popularity, i'd love
for this code to still work and be portable enough to use for any size
band.

SELECT DISTINCT `Shows`.`Show_I D`
FROM `Shows`
WHERE CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Busine ss_Name`, `Address`.`Stre et_Address`,
`Address`.`City `, `Address`.`Stat e_Or_Province`, `Address`.`Coun try`,
`Band`.`Busines s_Name`, `Affiliate`.`Bu siness_Name` ) LIKE
'%search_item1% '
AND CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Busine ss_Name`, `Address`.`Stre et_Address`,
`Address`.`City `, `Address`.`Stat e_Or_Province`, `Address`.`Coun try`,
`Band`.`Busines s_Name`, `Affiliate`.`Bu siness_Name` ) LIKE
'%search_item2% '
AND CONCAT_WS(' ', CAST( DATE_FORMAT( `Shows`.`Date`, '%W %M %Y' ) AS
CHAR ), `Venue`.`Busine ss_Name`, `Address`.`Stre et_Address`,
`Address`.`City `, `Address`.`Stat e_Or_Province`, `Address`.`Coun try`,
`Band`.`Busines s_Name`, `Affiliate`.`Bu siness_Name` ) LIKE
'%search_item3% '

thanks,

heath

Jan 6 '06 #1
1 2145
> LIKE '%search_item1% '

You should avoid writing queries like this (if possible) because MySQL can
never use an index for such queries. I think, the fulltext index is still
the best choice that you have in your particular situation.

Markus
Jan 6 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
1839
by: vizziee | last post by:
Hi all, I need some good primer books on Handel-C. Plz. send-in the titles. Thanx in advance.
0
1520
by: tim | last post by:
Hi, I'm playing around with the python external for PureData. (pyext) I want to enable a patch to lookup something on the net. I modified the "search.py" example from Dive Into Python and kind of pasted it into the simple.py example from the pyext docs to be loaded in a pd patch. Loading the script in the patch works, and it receives messages and sends messages back, so I think on the pd side I've got it set up right. If I call my...
0
2015
by: Waran | last post by:
I need to create a Auto suggests Textboox like in http://www.google.com/webhp?complete=1&hl=en I have completed this using AJAX.NET for Framework 1.1 . I have some design issues after the data is populated. For me the problem is I am getting more space after the item is selected. And the items are displayed in a combo when i am going for another search / selection without refreshing the page. I am expecting a same output as google's...
1
7303
by: xahlee | last post by:
Elisp Tutorial: Make Google Earth Xah Lee, 2006-12 This page shows a example of writing a emacs lisp function that creates a Google Earth file, and creates a link to the file, as well a link to Google Map. If you don't know elisp, first take a gander at Elisp Basics. I often write travelogs on my website. If i traveled to Las Vegas, then
8
5765
by: nil | last post by:
Hello all, It's urgent... i want to add autocomplete textbox facility in my application like google. as you type it suggests option to the user..i want the same kind of facility...i know i can do only with ajax(xml and javascript) but i want to fetch possible values from database and want to show as the user types in text box.
0
1094
by: rbunn83815 | last post by:
Hello Everyone! Before we start I'm a javascipt newbie as well as trying to get javascipt to work with the C# code behind page in ASP.NET. C# alone I can do fairly well with however. I'll just use google as an example here since it is well known. Here is a script (below) for allowing people to place a google search box on their page. I plan to show a much larger duplication of it when there is a mouse over event (or any acceptable...
2
8093
by: john | last post by:
All: Does anyone know how Google creates and displays those pop-up windows on their maps? For example, if I go to maps.google.com and do a search, I can mouse-over the results, and an information window pops up. I see a similar effect on www.tvguide.com when I mouse over TV shows (the pop-up box gives a summary fo the show). Is this something that modern Javascript engines provide? Is this something written in another, lower-level...
9
3521
by: artistography | last post by:
Hello, I was wondering: If obfuscation isn't all that good, why does Google obfuscate its search results' code? Okay, I realize they're a commercial entity and need to do it. How do they do it? Could anyone out there decipher and normalize code for a Google search result page? And despite the obfuscation, why is Google so fast?
0
3751
by: TrevRex | last post by:
Hello, I work for a non-profit in San Diego as a GIS Specialist. I have had to teach myself about some scripting to create some dynamic maps, but I am still very limited in my skills, so I have had to explore the internet in order to discover various tutorials and examples that have led me on a positive path. Right now I am working on a Google Mash-Up that will incorporate over 14,000 records, which will appear as separate markers that...
0
9978
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9821
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11229
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9630
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8016
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5850
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4676
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4271
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3276
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.