Fast MYSQL table lookup in a large table

[cooldht]

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!

[mwasif]

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]

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.

[cooldht]

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]

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.

[cooldht]

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]

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:

[CODE=MYSQL]SELECT * FROM (`data_restaura nts` LEFT JOIN `map_restaurant _cuisine` USING(`restaura ntid`)) WHERE (`cuisine` LIKE '%italian%');[/CODE]

[cooldht]

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]

Heya, cooldht.

Good luck with your project, and if you ever need anything, post back anytime :)

[developing]

well...you couls also sort them asc and then do a binary search...

[pbmods]

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!

[developing]

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 >.<