Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a lot of data to filter and therefore I wonder is it better to create somekind of indexes and save them to one table and use MATCH or LIKE to find matches or should I use many different tables and JOIN query. Wich is faster?

Example:

CAR ID: 123456789-1234-1234-1982-372 (first number is key to car's table, after dash, the number indicates maker (ie Opel), third number shows model (ie Omega), after this, year of manufacture and last number indicates the region the car is currently located. To find all Opels that are made on 1997 and are at specific area, I could use match or like on CAR ID.

Or would it be better if I just use JOIN to query mark, year and location?

Thank you!

share|improve this question
    
I'm not sure I understand your situation. It looks like you can split 123456789-1234-1234-1982-372 into separate columns (carID,makeID,modelID....). If you can, searching becomes trivial and very fast (with appropriate indexes). –  Vatev Aug 6 '13 at 9:47
    
Thanks. Im asking if it is faster to save these long numeral indexes into one table or should I split these numbers into different tables. so that i could use MATCH within one table versus SELECT from different tables. –  fallenboy Aug 6 '13 at 9:52
add comment

1 Answer

up vote 1 down vote accepted

If you are filtering by the middle of a string and that is the only (or the primary) filter in your query, then you are at best going to see an index scan and if there is no index on that column a full table scan. This is usually not desirable.

If all those properties are stored in columns within the cars table then querying FROM cars WHERE maker='opel' AND date_of_manufacture BETWEEN '1997-01-01' AND '1997-12-31' would likely be much more efficient if there is an index over maker, date_of_manufacture or date_of_manufacture, maker than looking for WHERE manufactured_id LIKE '%-open-1997-%' as the query runner will be able to seek to the first match (an operation of complexity log(n) so for a million records it would need to compare ~30 index blocks before finding the right place) then scan down the matches and lookup the rest of the columns from the heap/cluster. With the LIKE/MATCH approach looking at the middle of a string it would have to compare all the million strings against your match criteria (1,000,000 comparisons rather than ~30) - even if the whole index is in memory at the time that is going to consume noticeable extra CPU time and if it is not in memory at that point there will be the extra IO hit to consider too.

You question is a little unclear on what you are considering the alternate structures to be (I don't see why JOINs necessarily need to be involved at all) so I may be missing your point. It would be useful to add to the question a diagram or other more detailed indication of the table structures you are considering.

share|improve this answer
    
Thank you, thats what I wanted to know! The problem was that sometimes I need to query from at least 5 different tables AND within each table I need to find specific matches. To use your example, sometimes I need to find car location (country, region, town) and driver (all 22 year old blondes) + driver education ect... So I might end up querying from 10 tables and I was afraid it might turn out to be extremely slow (find me all blond drivers, who have higher education in IT, driving 2001 year mercedes benz OR opel omega in Sydney, Australia) etc..) –  fallenboy Aug 6 '13 at 10:44
    
What would happen for that query depends on the DB engine, the amount/mix of data in each table, and how you write the query. It would either find the cars as I described above, find the people similarly, then do many index seeks on the car/person relationship table based on the cartesean product of the two result sets to see if any match up, or it'll find one of the dimensions (cars or people), lookup the related objects in the other using the cars/people relation table and the PK for the second dimension table, then apply the second set of filters to those rows. –  David Spillett Aug 6 '13 at 11:03
    
I've done this kind of queries before, it's not very ... nice :), but now I have huge amount of rows in altogether 15 tables (atm) and in worse case I need to find specific rows considering all 15 tables. I tried to use indexes and they seemed to work fine, but I have only few hundred rows, unfortunately cannot test on real-life conditions (millions of rows). Thats why I had doubts if it's good idea to use id codes that are more than just keys. Thank you again! –  fallenboy Aug 6 '13 at 15:18
    
You should be able to test with real sized data, either by taking a copy of real data and "randomising" it to disguise sensitive information or (much better from a data use/retention compliance PoV if nothing else) generate arbitrary data yourself. If the scripts to generate the test data are flexible enough you can go one better and test against larger data than current production systems to make sure there is room to scale. –  David Spillett Aug 6 '13 at 16:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.