What renders/loads quicker: mysql select, or fopen() in php

[darcyb]

Hey folks. I want to make my own random quotes box in the header of my vb pages, and can't decide which solution to use:

1-Text file, quotes separated by \n, loaded with fopen() in php, grabbing one at random every page load

2-Store the quotes in a table, and have mysql grab one at random every page load

Any experience with this to keep performance up? I had an fopen hack on my phpBB2 forum which loaded really fast, but haven't compared it to a mysql query.

[Xenon]

Storing in MySQL is much better as, DBs are optimized to access to single rows very fast

[darcyb]

Ok, good advice Xenon, thanks! My strategy will now be to count rows, and generate a random int between 1 and max_rows, and then do a SELECT by that random ID, grabbing the quote text.

My next challenge is importing my text file of quotes. Each quote is separated by a newline character (\n). I created a table with a primary ID of type int(11), and a longtext field. I'm having difficulty importing it by means of phpmyadmin, because the ID field is chomping off the first 11 characters of each quote as it tries to fill the auto_completing primary field. Annoying!

I may have to go into Excel and add a prefixing column 11 characters wide.

[Andreas]

Originally Posted by darcyb

Ok, good advice Xenon, thanks! My strategy will now be to count rows, and generate a random int between 1 and max_rows, and then do a SELECT by that random ID, grabbing the quote text.

Hmm ... what about

PHP Code:

$quote = $DB_site->query_first("SELECT * FROM " . TABLE_PREFIX . "quotes ORDER BY RAND() LIMIT 1") 


?

[Xenon]

your Datatypes are too big i think.

You won't need longtext, i assume text would be long enough (or do you have quotes longer than 65k chars?)

also how much quotes do you have

small unsigned int may also fit in better.

Of course your fields will do the correct things, but optimising where possible can't hurt

[darcyb]

Oh ok. I had mistakingly assumed that longtext was good for quotes 3-4 lines long, 80 chars per line. So no, they're not longer than 65k chars hehe. I didn't know 'text' was that roomy.

I have 130 quotes and the list grows daily.

[CarCdr]

Plain fopen() should be faster. Letting Apache handle it without using a script should be MUCH faster. Static content does not have to interact with the MySQL server.

[Code Monkey]

Database is usually faster than file operations.

[Xenon]

Originally Posted by CarCdr

Plain fopen() should be faster. Letting Apache handle it without using a script should be MUCH faster. Static content does not have to interact with the MySQL server.

Erm, as you have to go through the whole file to get a random quote, it will get slower and slower with fopen.

The connection to the file may be faster, but going through the file will be the slower part of it.