Hi all,
I'm still trying to learn the ins and outs of MYSQL. I have a large database of restaurants sitting on our server, and I want to be able to cross reference the cuisines and display a page that says there are 15 Italian restaurants in Los Angeles California, 12 Mexican places in L.A., for example. the cuisine fields are character. For example, the restaurant name might be Charlie's Pizzeria, and the cuisine field could have "pizza, pasta, Italian". There is a separate table for cuisines. The current PHP code is doing a select statement on the cuisine table, going through every record in there against the restaurant table where the city & state are what the user selected, and where the cuisine field in the restaurant table is LIKE the current record from the cuisine table. Then it goes on to the next record in the cuisine table and does that all again.
Using nothing but PHP, MYSQL, and magic, does anyone have any ideas what the best approach would be to select the records out quickly? I tried creating a separate field for every cuisine in the record, and indexing them, but MYSQL can't have that many indexes evidentially. This will be a re-design anyway, so any ideas are appreciated. Even if I have to re-design the database, I'd like to do it the right way.
Thanks!
11 3306 mwasif 802
Recognized Expert Contributor
To get quick results you have to redesign the DB structure. A solution can be creating a table (you can say a middle table, joining restaurants and cuisines) restaurant_cuis ine containing 2 fields
restaurant_id
cuisine_id
create a combined index on restaurant_id and cuisine_id.
In this way, you'll be able to add many cuisines as you can.
pbmods 5,821
Recognized Expert Expert
Heya, cooldht. Welcome to TSDN!
I tried creating a separate field for every cuisine in the record, and indexing them, but MYSQL can't have that many indexes evidentially.
Can you explain what you mean by this? I've had tables with over 20 indexes before.
Hi MW, thanks for the advice! I'll put that on the list of possibilities for the re-design.
PB, I guess there is a limit. I receive the error "#1069 - Too many keys specified. Max 32 keys allowed". There are somewhere around 90 cuisine fields unfortunately.
Thanks!
mwasif 802
Recognized Expert Contributor
This is not a good design to create a seperate column for each cusine against a a restaurant. In this case, how do you know that in which column you saved 'italian', 'pizza' or 'pasta'. In this case you query may be something like - SELECT * FROM restaurants WHERE cuisine1='italian' OR cuisine1='pizza'
This will make the search painfully slow.
This is not a good design to create a seperate column for each cusine against a a restaurant. In this case, how do you know that in which column you saved 'italian', 'pizza' or 'pasta'. In this case you query may be something like - SELECT * FROM restaurants WHERE cuisine1='italian' OR cuisine1='pizza'
This will make the search painfully slow.
Thanks for your reply!
Actually, believe it or not, the code that's in there now is SELECT count(*) FROM restaurants WHERE cuisine1 LIKE '%italian%' and city = 'Los Angeles'. And that is painfully slow. To get past the problem at hand, I think I can add a numeric field for every cuisine to the cities table, which is the total number of it's kind in that city. Then when building the page, I can just go through the cities and display the number and the cuisine on the screen. I'm trying to determine what other problems I might face down the road with this however. I'll have to re-design the way the rest of the pages function for sure.
Thanks!
pbmods 5,821
Recognized Expert Expert
Heya, cooldht.
I'm trying to determine what other problems I might face down the road with this however. I'll have to re-design the way the rest of the pages function for sure.
Since you have a many-to-many relationship between restaurants and cuisines, you will definitely need to create a mapping table as mwasif recommended.
As long as the tables are indexed, MySQL will actually execute much faster than if you had 90 cuisine fields in the restaurants table.
Also, what if a restaurant in Los Angeles had `cuisine1` = 'american' and `cuisine2` = 'italian'?
Using the mapping table you can instead do this: - SELECT * FROM (`data_restaurants` LEFT JOIN `map_restaurant_cuisine` USING(`restaurantid`)) WHERE (`cuisine` LIKE '%italian%');
Thanks for the responses. I was wanting to do something similar, but was having problems with the LEFT JOIN logic and on how the middle man table should work. I think it makes sense now, and think that's the way I'll proceed. Thanks again!
pbmods 5,821
Recognized Expert Expert
Heya, cooldht.
Good luck with your project, and if you ever need anything, post back anytime :)
well...you couls also sort them asc and then do a binary search...
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: mdh |
last post by:
I am trying to learn the basics of MVC applications using a Tomcat
infrastructure. I'm starting by building a simple application with:
* a login.jsp page for a basic login form with a action
pointing to "loginValidate.jsp"
* a loginValidate.jsp page that references logic to
validate requests
* a loginBean class used to pass info between the JSP
and validation logic
* a usersDAO class used to perform lookups in a USERS
|
by: mos |
last post by:
I'm on a trek looking for fast reliable (bug free) MySQL 4.1 components for
Delphi 6/7 for accessing large MyISAM and Innodb tables. Can anyone make
any recommendations?
I've tried MySQLDAC from MicroOlap and unfortunately support is terrible.
(No reply to any of my e-mails in the past 2 weeks).
I'm also looking at Zeos (where are the support newsgroups?), SciBit, and
CoreLab.
|
by: Matt Liverance |
last post by:
I REALLY dont want to switch to oracle :( but I cant get these tables
working any faster.
I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm
raid 5 on u320 perc raid cards, dell 2600/4600's with single channel
backplanes (new ones will have dual channel)
All have 2 gig of ram, but I've never seen mysql use more than 300mb
of ram.
|
by: Torfi Sackbatten |
last post by:
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,
,
keywords TEXT NOT NULL,
|
by: JIM WHITAKER |
last post by:
Are there any good PHP scripts or pre written programs that deal with a
complex database?
Let me explain:
I'm in truck dispatch, and we've moved all tables to mysql and access them
on a network via ms access.
We will open another office in another city, and will want a "web" database.
Now here's what I'm after: Currently one form has various dropdown boxes to
pick customer, shipper, consignee, and carrier information. YES, I even
have...
| |
by: Mairhtin O'Feannag |
last post by:
Hello,
I have a client (customer) who asked the question : "Why would I buy and
use UDB, when MySql is free?"
I had to say I was stunned. I have no experience with MySql, so I was
left sort of stammering and sputtering, and managed to pull out something
I heard a couple of years back - that there was no real transaction
safety in MySql. In flight transactions could be lost.
|
by: StupidScript |
last post by:
>From the manual "Storage Requirements":
"ENUM('value1','value2',...) =1 or 2 bytes, depending on the number
of enumeration values (65,535 values maximum)"
This seems to mean:
"a" = 1 byte of storage
"prestidigitation" = 1 byte of storage
|
by: Andrus |
last post by:
I'm creating a database Winforms application using VCS Express 2005
I have some large lookup tables (may be up to 500000 records) which
contains name and id and are stored in sql server.
I need to create single line combobox style control which:
1. Allows to type first characters in name
2. Auto-completes entered data by using first match
3. Allows to open picklist based by entered data and select name
|
by: Coldfire |
last post by:
Since i cannot show the differences in a two-column like table. I am first putting
MS SQL Server 2005 and then MySQL 5.x.
MS SQL Server 2005
Brief Overview
- SQL Server is a full-fledged database system developed specifically for large enterprise databases. All advanced features of a relational database are fully implemented.
- Once you purchase the product, you are only limited to the Sybase-derived engine.
|
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |