MySQL/PHP Question

[Tony Clarke]

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.

[Steve]

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.

[Frank]

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.

[richard.kendall]

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

[MikeSoja]

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

[Michael Fesser]

.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

[Michael Fesser]

.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

[MikeSoja]

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

[Tony Clarke]

"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.