mysql querying speeds

Anon

I have a table with some 100 rows in it... mainly names, tel #'s and about 5 fields with just integers. About 12 columns in total... it doesn't take any amount of time now to query the table.

What if there are 10,000 rows in this same table... how does this effect the speeds? Is there a way one can test this without actually creating a table with 10,000 rows?

Thanks,

-dr

[deleted]

"Is there a way one can test this without actually creating a table with 10,000 rows?"

No. (but then again, it's not very difficult to put a FOR loop around an insert statement is it? 🙂 )

How the speed will change depends very much on what your query does, and wether you have indexes to help your database find the required data. At 10k I wouldn't worry too much. 10M, then you may have a few sleepless nights over it.

Anon

We have a database with over 7 million records in it... With proper indices and at least some measure of a decently formed query, our select times are still in the tenths if not hundredths of seconds

Anon

mysql?

Anon

Yes

[deleted]

Note the words "proper indices" and "decently performed query"

Those two are very important.
at 7Million records and 255 bytes per row, you're talking about a datafile of 1.7GB.
You do not want to get a sequencial scan on that, for it will take ages. (if your HD can read 100MB/Sec it's still 17 seconds just to read all that data, then you still have process it...)

Anon

This raises an interesting question then. What are proper indices? (I think I've got a good idea of what a decently formed query is, but I'd be open to hearing advice on this as well.)

The way I've always thought about indices is:
-- if you are JOINing on a field, you might want to make it an index (especially when JOINing often or if the table is large)
-- the ID field (autonum) should be an index

Does this sound right? Any other guidelines? Most of my indices are INTs, but are there any disadvantages to making a VARCHAR field an index?

Am I asking right questions?

Anon

ok... if you did a join on a table with 7M records, that will take time on its own.

what my question is, if i have a fast machine with 2GB of PC2100 DDR ECC registered ram, and lets say a 100MB mysql data file... and i am working with that table, why doesn't mysql keep it in memory for later use if there is a ton available?

in this case, if the 1.7GB file could all fit in memory, and updates could be done in memory and queued to write to disk, wouldn't that be a lot better??

do i just have my server configured wrong???

[deleted]

Hark; I found another missing article on yapf ;-)

http://www.hvt-automation.nl/yapf/faq.php?cmd=viewitem&itemid=99

[deleted]

"why doesn't mysql keep it in memory for later use if there is a ton available"

It should. All 'proper' databases do that.

"and updates could be done in memory and queued to write to disk, wouldn't that be a lot better??"

That is usually what happens, but you must synchronise the modified (dirty) pages in memory with the pages on disk 'before the power fails'. A database may not return 'success' for a query untill everything that has been done during the query has been written to disk. Otherwise, some error on the server might cause the data to get lost before it is written to disk.

This is why Informix, and yes, even InnoDB allow you to store the data on raw-devices. That means that the database data is written to disk through unbuffered writes, giving more speed and reliability.

http://www.mysql.com/doc/I/n/InnoDB_Disk_i_o.html

Anon

ok.... 1 machine... just apache, and mysql.

lets say 1GB of data in mysql.

i want to set up my machine to keep ALL of the db data in memory and hog half of it instead of playing nice.

another 1GB for apache is more than enough.

these seems reasonable to me...

i've taken a lot of db classes about disk structure and storing the data, so i know most of the bottlenecks.... it just doesn't make sense that mysql is only using 20MB or so of memory all the time.

[deleted]

You may want to have a peek at this:
http://www.mysql.com/doc/S/e/Server_parameters.html

http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html

Anon

yeah, i've seen and tried to tweak with all of those values

(i use myisam tables) but i can't get it to do what i want...

what settings would you recommend? maybe a certain percentage of average free RAM or something??

my goal is to eliminate the initial disk read 100% of the time (entire DB in memory) thus in most cases halving the execution time of a simple query.

there is more than enough memory to do this, and my guess is mysql is smart enough anyways that this isn't even neccessary, but it just urks me to have so much ram free.

[deleted]

query_buffer_size seems to do that quite nicely, keeping read pages in memory for re-use.