Suppose you have a simple table with ID, First Name and Last Name.
ID is your primary key, and it is indexed.
If you have 10 million users in your table, and you do a query asking for user with ID 12345, it will be blazingly fast. The DB will essentially do a binary search against the index, and find 12345 after about a dozen compare operations. Milli or microseconds.
But suppose you look for all users with last name='FooBar'. Without an index on the Last Name column, the DB will have to look at all 10 million rows and do a string comparison. Many of those rows are probably not already cached in memory, so it will need to go out and read them from disk. Now you are looking at a query that will take many seconds, maybe even minutes.
So you fix that by adding an index on the Last Name column. Now you can find FooBar in milliseconds. But what if the user wants a last name that just starts with Foo? Usually still OK, with a decent DB. It can still use the index.
But what if you want to search for names like %oBa% ? That will be slow again.
I don't know Python/django so I don't know how isolated you are from the actual SQL that gets executed. If you know the exact SQL that is submitted to the DB, then you can run an Explain Plan on it (see link). That can give you a lot of clues as to why a query might be slow. The first thing to look for is always Full Table Scan (that is how it shows up in Oracle, MySQL might word it a bit differently). If you see those against a large table, then you can expect poor performance, and an index might fix the problem.
That being said, don't go overboard with indexes. They aren't free. They consume memory and disk space. And they slow down insert, update and delete operations. Too many indexes can be just as bad as too few.
When you execute an SQL command, the database software decides how to get the data. Sometimes you can make it more efficient by extracting some data into a temporary table.
For simplicity, assume the cost of the query is directly related to the time it takes to execute it.
AND, I don't mean you should consider a query that takes 10 microseconds to be 10 times more expensive that one that takes 1 microsecond. If the query doesn't take MACROseconds (an amount of time that the user would notice -- making the user think the query is slow) then don't worry about it.
Ella, if you are working with websites, choosing Python is a mistake on your behalf. I only know of one, or maybe a few websites that use Python, but things change, so I don't know if there are websites that still use Python. I visited many websites that are made with PHP, and MySQL. Frankly I think your confused. Whereever you are acquiring your information, well, I think it's an awful source. There is nothing intelligent about what you are asking, and saying. I understand you want to do something, and you think what you want to know will do the job. In my opinion, I think you should revaluate your situation.
Ella, think of this: You might be educated, but you lack knowledge. You seem like you want to acquire experience, but your not knowledgeable in information technology. Your not motivated enough to find out what you want to know by yourself.
If you prefer to work with Python, and Django, then I think you should learn what you are using, and not make a mistake by comparing a database like Django with a scripting language like PHP. Well, maybe you confused a database with a scripting language.
In my opinion, I think if you want to start working from the top, and you don't undertand the basics, then you will fall off from your horse, and hit your face on something that's hard.
You should first learn what the basics are before trying to learn something you don't understand.
I think maybe I know which direction you want to take, but from what I know nobody on yahoo answers has gone this far like a fortune 500 company.
You probably don't realize everything has pros, and cons. You think Python is better than PHP, but what good is Python if you don't even know how to capitalize on this programming language.
Even if you can capitalize on Python, you might not get what you want.
Frankly, I think if you refuse to expand your horizons, then you will never learn what you want to know.
Ella, you said, and I quote "Many times at my work I need to write a small algorithm involving a large DB. In those scripts performance counts. But there are so many questions I can't answer, I end up compromising, using a script I just think is OK."
So you are using scripts, but you never said the scripting language you are using. You might be able to find an abundance of information on scripting languages.
It becomes more complicated when you use the scripting languages with a website. Unfortunatly some people fail to understand how to use the scripting language with a database.
Ella, Django is one type of Database, and MySQL is a different type of database. What you should think about is why you need to use what you are using. C is not used in web development like Python, Django, and MySQL.
You said C is a low level programming language. I think your mistaken. C is a high level programming language. Python is a low level programming language. People would first recommend learning Python before learning C.
If you are focusing on websites, you could set up a C based web server, but you never said anything about how you are using C.
Using a C based web server is only one option.
You seem to be concerned about efficiency. The issue you are facing is you are struggling to understand what you are using, and how you should use it. I think you are focusing on to many topics. You said you know some MySQL syntax, but the reality is, it's not enough. If you want to become good at something, then you have to practice at what you want to become good at doing. I suggest you take your time to learn MySQL.
If you are focusing on websites, then I am not surprised that you will struggle. You might not find any useful information on Python, and Django if you are trying to use it with websites. Because I think you missed a topic.
Using the Internet is an option, and maybe a better option, for you instead of using a book, but you should not close the doors on using a book.
In my opinion, I think it takes time, for people to find what they want.
I don't know what you are doing, and why you need to use MySQL, Django, Python, and C.
I think you should think about the type of projects you are doing.
You said what you are using, but you never mentioned why you are using it.
I don't understand why you are working with C, Python, Django, and MySQL.
You are working with two programming languages, and two databases.
I think Python works best with Django, but you might be missing some knowledge.
I think PHP works well with MySQL.
I use MySQL, with PHP, and SQL. I briefly reviewed Python, and Django. I think you might have a hard time working with Python, and Django.
I have a Python book, and it's a big book. I briefly looked at it just, for me to realize that the book talks about Python, but if your motives are to use it, for the Internet, then you might end up giving up.
Frankly, I think you should start to learn how to use one database at a time. I suggest you start to learn MySQL. There is a learning curve to implement a MySQL database.
I understand you are hoping to learn from a book. I recommend you use the Internet, and books to learn what you want to know. I suggest you invest your time to experiment with what you are using to learn more about it.
Many times at my work I need to write a small algorithm involving a large DB. In those scripts performance counts. But there are so many questions I can't answer, I end up compromising, using a script I just think is OK.
I use MySQL and python with django, but I would like to know the general rules, platform independent. I would like to really understand how everything works. When writing in C, I feel like I really know what's happening. So deciding what is more efficient is easy most of the times. But python with django is so high level, and I also don't know how the DB is implemented, what cost executing a query has, the cost of issuing the query, and so on.
So I'm looking for a comprehensive source of information about working efficiently with databases. A book maybe.
Thank you.
