Hi guys,
I'm developing a high performance, heavy traffic web application and I want to make sure that I get the database structure perfect before I begin. The database could contain several million records and be queried thousands of times each second so optimisation and speed is essential.
Basically the user can provide between one and five words. The idea is that it searches the database to find words that relate to the ones entered.
So for example, if the user enters 'London pizza' it would search for records that matched 'london' and 'pizza'. It doesn't need to look within sentences and the words will match exactly in alphabetical order.
I've created 3 possible database structures to cope with this - could anyone advise as to the best one?
SCENARIO 1 - using 2 tables, 1 inner join - I read somewhere that you should try to avoid joins in tables containing large numbers of records.. although this would minimise disk usage would it be as fast as the other scenarios?
SCENARIO 2 - using 2 tables, 2 queries - similar to scenario one but instead of using an inner join it uses 2 simple select queries. Would this be faster / more efficient?
SCENARIO 3 - using 1 table, 1 query - Additional disk space required but one simple select query. Would the speed of the query outweigh the cost of using one huge table?
Any tips / advice on the above would be brilliant. It might also be the case that there are alternative solutions that I haven't thought of. I'll also have to decide which fields to apply indexes to etc.
Thanks in advance!
I'm developing a high performance, heavy traffic web application and I want to make sure that I get the database structure perfect before I begin. The database could contain several million records and be queried thousands of times each second so optimisation and speed is essential.
Basically the user can provide between one and five words. The idea is that it searches the database to find words that relate to the ones entered.
So for example, if the user enters 'London pizza' it would search for records that matched 'london' and 'pizza'. It doesn't need to look within sentences and the words will match exactly in alphabetical order.
I've created 3 possible database structures to cope with this - could anyone advise as to the best one?
SCENARIO 1 - using 2 tables, 1 inner join - I read somewhere that you should try to avoid joins in tables containing large numbers of records.. although this would minimise disk usage would it be as fast as the other scenarios?
Code:
'lookup' Table
ID | word1 | word 2 | word 3 | word 4 | word 5
---------------------------
1 | london | pizza
2 | london | pasta
3 | london | pizza | takeaway
4 | london | pizza | restaurant
'return' Table
ID | lookup_ID | property
1 | 1 | fastfood
2 | 2 | italian
3 | 1 | takeaway
4 | 3 | bla
4 | 4 | blo
SELECT `return`.`property` FROM `return` INNER JOIN `lookup` ON `return`.`lookup_ID`=`lookup`.`id` WHERE `lookup`.`word1`='london' AND `lookup`.`word2`='pizza'
property
----------
fastfood
takeaway
SCENARIO 2 - using 2 tables, 2 queries - similar to scenario one but instead of using an inner join it uses 2 simple select queries. Would this be faster / more efficient?
Code:
'lookup' Table
ID | word1 | word 2 | word 3 | word 4 | word 5
---------------------------
1 | london | pizza
2 | london | pasta
3 | london | pizza | takeaway
4 | london | pizza | restaurant
'return' Table
ID | lookup_ID | property
1 | 1 | fastfood
2 | 2 | italian
3 | 1 | takeaway
4 | 3 | bla
4 | 4 | blo
SELECT `ID` FROM `lookup` WHERE `word1`='london' AND `word2`='pizza'
ID
----------
1
SELECT `property` FROM `return` WHERE `lookup_ID`='1'
property
----------
fastfood
takeaway
SCENARIO 3 - using 1 table, 1 query - Additional disk space required but one simple select query. Would the speed of the query outweigh the cost of using one huge table?
Code:
'lookup' Table
ID | word1 | word 2 | word 3 | word 4 | word 5 | property
---------------------------
1 | london | pizza | | | | fastfood
1 | london | pizza | | | | takeaway
2 | london | pasta | | | | italian
3 | london | pizza | takeaway | | | bla
4 | london | pizza | restaurant | | | blo
SELECT `property` FROM `lookup` WHERE `word1`='london' AND `word2`='pizza'
property
----------
fastfood
takeaway
Any tips / advice on the above would be brilliant. It might also be the case that there are alternative solutions that I haven't thought of. I'll also have to decide which fields to apply indexes to etc.
Thanks in advance!