Sign in to follow this  
Azh321

Speed of DBs

Recommended Posts

Ive never been a database programmer and was only recently introduced to them. I have a PHP script using a mysql database using the method I thought would work best, though I have someone else telling me other wise and I find it hard to believe. My method is to have a 2 tables in the db. The first table has a varchar of a list of numbers that are each 8 digits long. Then the second table has a field for the corresponding number to the first table. Then my script would get the varchar from the first table, if it isnt empty, then search the second table for the corresponding matches. The method ive been told to use has 2 tables. The first table has a number that corresponds to a field in the second table. Then the script gets the number from the first table and searches the second table for ALL(possibly 0) matches. I understand that DBs are fast but I would think that my method, when it doesnt search the second table because the number was empty, would be faster than the second method if it had a few entries. Isnt comparing a string to "" faster than a DB searching through 0-1000000 IDs? Now that I look at it, my method is very dirty and is because of my addiction to bitfields. The other method looks much more modern and probaly does beat my method out big time. Can someone please explain and perhaps give me some resources on how databases work internally so I can understand their optimizations better? Thanks! I hope you understood!

Share this post


Link to post
Share on other sites
If you have a number, why is it varchar? That's the first problem.

Secondly, what do these numbers and tables represent? Because I have uncanny suspicion you're trying to do some form of indexing, which is best done in a different way.

When starting with DBs, you'll need to first look at database theory, then forget the usual coding practices. Performance problems with databases are completely different than those of code.

Share this post


Link to post
Share on other sites
My method uses a number that is stored as a varchar because it is a series of flags and unless mySQL now supports bitfields or arrays, thats how I know how to do it. I wasnt asking if there was a problem with my method, I was asking if a DB search is faster than a string being compared to "".

Share this post


Link to post
Share on other sites
Quote:
Original post by Falling Sky
My method uses a number that is stored as a varchar because it is a series of flags and unless mySQL now supports bitfields or arrays, thats how I know how to do it. I wasnt asking if there was a problem with my method, I was asking if a DB search is faster than a string being compared to "".


There's SET type, but it's limited to 64 bits.

http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

A single string comparison is O(1). A search through database of millions of rows will not be O(1). So yes, it's faster.

The reason I mentioned that is because comparing a string to "" or comparing a number value to 0, the second is usually faster, or at very least stored more efficiently, and as such has less overhead.

Share this post


Link to post
Share on other sites
nothing is scarier for a database admin that a developer with no database experience diving in thinking it is the same. i mean no offense by that, but it is a completely different paradigm.

relational databases are designed to work with sets of data, and are optimized for that. so, assuming you have to search a table for a value before comparing it to that "", and then look up in another table if that isn't true, it might be best to include the other table right off the bat and let the database engine do its thing. then again it might not be.

you would get the best advice if you said what exactly you are trying to do at a higher level.

Share this post


Link to post
Share on other sites
I don't understand why you are using 2 tables. This seems like a very simple lookup table. Set your "bit-field" column as the primary key and your other data as more columns in the same table:

CREATE TABLE table
(
id varchar(8),
data varchar(255), // whatever data type you need here
PRIMARY KEY (id)
)

Then a query like this should work:

SELECT data FROM table WHERE id = <lookup_value>;

Perhaps I am misunderstanding what you're trying to do. This type of searching is standard and very fast.

NOTE: This syntax is from the MySQL documentation, but other dbs should be similar if not identical.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this