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 3295
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_cuisine 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.
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!
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!
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!
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...
Heya, developing.
well...you couls also sort them asc and then do a binary search...
Only problem with that is that what you gain in the binary search, you lose in the quicksort!
Heya, developing.
Only problem with that is that what you gain in the binary search, you lose in the quicksort!
thats true, buy you could effectively create a data layer (dataset in .net, not sure what its called in php world) that only contains that column and is already sorted. so when the user uses the data, all that happens is the binary search...
yeah, dont do the above...i just talked myself out of it too...lol >.<
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
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: 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: 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,...
|
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...
|
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...
| |