By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,118 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,118 IT Pros & Developers. It's quick & easy.

MySQL/PHP Question

P: n/a
Hi,

Just a quick question about performance with MySQL & PHP. If I had a table
in a MySQL database with about 100,000 records in it and I need to find the
last record is there a quick way to do this, other than going through the
whole database to find the last record? Would the amount of records in the
table cause major performance problems?

For example, if I had a html form that people used to log something and
needed to display a unique number each time the page was loaded, reason
being thas this unique number could be given to people as a reference
number.

The reason I'm asking is because I've used an Access database (an ODBC
connection) before and with a large amount of records in the table it can
take up to about 12 seconds for a page to load. Would using MySQL
dramatically speed this up anyway?

Thanks,

Tony.
Jul 17 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
A MySql table can hold up to 2GB of data. It's been my experience that
you will start to see performance issues around 1GB.

Jul 17 '05 #2

P: n/a
Tony Clarke wrote:
Hi,

Just a quick question about performance with MySQL & PHP. If I had a table
in a MySQL database with about 100,000 records in it and I need to find the
last record is there a quick way to do this, other than going through the
whole database to find the last record? Would the amount of records in the
table cause major performance problems?
100,000 records is nothing. Not even to Accesss, I'd think.

In a properly indexed table, picking the first or last record should
happen in an instant, regardless of how many records there are.
For example, if I had a html form that people used to log something and
needed to display a unique number each time the page was loaded, reason
being thas this unique number could be given to people as a reference
number.
If you give each logged item a unique number (this makes for a good
primary key in your table, btw), retrieving that record should be very
fast as long as there's an index that column.
The reason I'm asking is because I've used an Access database (an ODBC
connection) before and with a large amount of records in the table it can
take up to about 12 seconds for a page to load.
A full scan (if there's no index) across 100k records should happen
significantly faster than 12 seconds, even with Access..?

Based on your very first question, I'm guessing you actually retrieve
all 100k rows of data and iterate through it until you find the one
record you're looking for?

If this is the case, a simple modification to your SQL query should work
wonders.
Depending on what you're trying to accomplish, either add a WHERE
statement, to only select the row with a matching value, or if you're
looking for the first/last row, add ORDER BY and LIMIT or TOP to select
only the first row.

If you already do it like this, make sure you have an index on the
column in question. I never did stuff an Access table with 100k+ rows,
but I'm sure _any_ relational DB can handle that just fine.
Would using MySQL
dramatically speed this up anyway?


Why MySQL, btw? I went from Access databases to MySQL as well, but only
because I didn't know any better.

These days, I'm reluctant to touch MySQL with my nine-foot pole.
There are other free databases out there which do the job a whole lot
better. I suggest you use PostgreSQL or Firebird if you have access to
one of those, or maybe SQLite if you're on PHP5 and don't have to worry
about extreme load.
Jul 17 '05 #3

P: n/a
If you know the number of rows in the table then you could use the
following query:

SELECT * FROM <table> LIMIT <offset>,<row_count>

Replacing table, offset and row_count with the appropriate values.
Regards

Richard

Jul 17 '05 #4

P: n/a
On Thu, 9 Dec 2004 12:35:25 -0000, "Tony Clarke"
<cl*****@eircom.net> posted:
Hi, Just a quick question about performance with MySQL & PHP. If I had a table
in a MySQL database with about 100,000 records in it and I need to find the
last record is there a quick way to do this, other than going through the
whole database to find the last record? Would the amount of records in the
table cause major performance problems?


I asked the same question on another forum a couple weeks ago and
learned:
$result = mysql_query("SELECT COUNT(*) FROM table");
$number_of_last = mysql_result($result, 0, 0) + 1;
Mike

Jul 17 '05 #5

P: n/a
.oO(MikeSoja)
I asked the same question on another forum a couple weeks ago and
learned:

$result = mysql_query("SELECT COUNT(*) FROM table");
$number_of_last = mysql_result($result, 0, 0) + 1;


Useless. With the above code you only know the number of records in the
database, but this doesn't necessarily have to be the number of the last
record (if it has a number at all).

What would you do next with the variable $number_of_last?

Micha
Jul 17 '05 #6

P: n/a
.oO(Tony Clarke)
Just a quick question about performance with MySQL & PHP. If I had a table
in a MySQL database with about 100,000 records in it and I need to find the
last record is there a quick way to do this, other than going through the
whole database to find the last record? Would the amount of records in the
table cause major performance problems?
100.000 records are peanuts. Question is what makes a "last record"?
Do they have some kind of numeric ID, which can be used to order them?
The reason I'm asking is because I've used an Access database (an ODBC
connection) before and with a large amount of records in the table it can
take up to about 12 seconds for a page to load.


There was definitely something wrong, either with the database/query or
with the script.

Micha
Jul 17 '05 #7

P: n/a
On Thu, 09 Dec 2004 19:20:00 +0100, Michael Fesser <ne*****@gmx.net>
posted:
.oO(MikeSoja)
I asked the same question on another forum a couple weeks ago and
learned: $result = mysql_query("SELECT COUNT(*) FROM table");
$number_of_last = mysql_result($result, 0, 0) + 1;

Useless. With the above code you only know the number of records in the
database, but this doesn't necessarily have to be the number of the last
record (if it has a number at all).
Well, it would be up to the guy who asked the question exactly what
constituted a "last" record, but I believe the query above can be
ORDERED as wished.
What would you do next with the variable $number_of_last?


On my own little set of web pages I report it as a Visitor Counter.

Mike

Jul 17 '05 #8

P: n/a

"Michael Fesser" <ne*****@gmx.net> wrote in message
news:r5********************************@4ax.com...
.oO(Tony Clarke)
Just a quick question about performance with MySQL & PHP. If I had a tablein a MySQL database with about 100,000 records in it and I need to find thelast record is there a quick way to do this, other than going through the
whole database to find the last record? Would the amount of records in thetable cause major performance problems?


100.000 records are peanuts. Question is what makes a "last record"?
Do they have some kind of numeric ID, which can be used to order them?
The reason I'm asking is because I've used an Access database (an ODBC
connection) before and with a large amount of records in the table it can
take up to about 12 seconds for a page to load.


There was definitely something wrong, either with the database/query or
with the script.

Micha


Firstly, thank you to everyone who answered, I have learned a lot.

Yes there is a numeric ID that I use as reference number. Yes, the reason it
was taking about 12 seconds was because I was forming the query badly (as
Frank said I was retrieving all data instead of what I was looking for).

Thanks again,

Tony.
Jul 17 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.