speed penalties?

Hi everyone,
I'm planning on using MySql/Php on either a Windows or, more likely, a
Linux base. However, my database will be several thousands of records,
in many hundreds of tables, searched using a "Search" query with a
regex expression included. I'm wary about the speed penalties, and I
would be very grateful if some metrics or timings could be
provided....?


Many thanks

Paul
--
http://www.paullee.com

Paul.Lee.1971 wrote:
> Hi everyone,
> I'm planning on using MySql/Php on either a Windows or, more likely, a
> Linux base. However, my database will be several thousands of records,
> in many hundreds of tables, searched using a "Search" query with a
> regex expression included. I'm wary about the speed penalties, and I
> would be very grateful if some metrics or timings could be
> provided....?
>
>
> Many thanks
>
> Paul
> --
> http://www.paullee.com
>

Paul,

Thousands of rows is no problem. But "Hundreds of tables"? Even the
most sophisticated system I ever designed didn't require that many tables!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 30 Jun, 00:56, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Paul.Lee.1971 wrote:
> > Hi everyone,
> > I'm planning on using MySql/Php on either a Windows or, more likely, a
> > Linux base. However, my database will be several thousands of records,
> > in many hundreds of tables, searched using a "Search" query with a
> > regex expression included. I'm wary about the speed penalties, and I
> > would be very grateful if some metrics or timings could be
> > provided....?
>
> > Many thanks
>
> > Paul
> > --
> >http://www.paullee.com
>
> Paul,
>
> Thousands of rows is no problem. But "Hundreds of tables"? Even the
> most sophisticated system I ever designed didn't require that many tables!
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

The problem I see is either hundreds of tables, or a table containing
a huge amount of rows/columns!

Paul.Lee.1971 wrote:
> On 30 Jun, 00:56, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Paul.Lee.1971 wrote:
>>> Hi everyone,
>>> I'm planning on using MySql/Php on either a Windows or, more likely, a
>>> Linux base. However, my database will be several thousands of records,
>>> in many hundreds of tables, searched using a "Search" query with a
>>> regex expression included. I'm wary about the speed penalties, and I
>>> would be very grateful if some metrics or timings could be
>>> provided....?
>>> Many thanks
>>> Paul
>>> --
>>> http://www.paullee.com
>> Paul,
>>
>> Thousands of rows is no problem. But "Hundreds of tables"? Even the
>> most sophisticated system I ever designed didn't require that many tables!
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> The problem I see is either hundreds of tables, or a table containing
> a huge amount of rows/columns!
>

If you properly normalize your database, you should need neither.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 30 Jun, 01:29, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Paul.Lee.1971 wrote:
> > On 30 Jun, 00:56, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Paul.Lee.1971 wrote:
> >>> Hi everyone,
> >>> I'm planning on using MySql/Php on either a Windows or, more likely, a
> >>> Linux base. However, my database will be several thousands of records,
> >>> in many hundreds of tables, searched using a "Search" query with a
> >>> regex expression included. I'm wary about the speed penalties, and I
> >>> would be very grateful if some metrics or timings could be
> >>> provided....?
> >>> Many thanks
> >>> Paul
> >>> --
> >>>http://www.paullee.com
> >> Paul,
>
> >> Thousands of rows is no problem. But "Hundreds of tables"? Even the
> >> most sophisticated system I ever designed didn't require that many tables!
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================
>
> > The problem I see is either hundreds of tables, or a table containing
> > a huge amount of rows/columns!
>
> If you properly normalize your database, you should need neither.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Ulp! I need help!

Paul.Lee.1971 wrote:
> On 30 Jun, 01:29, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Paul.Lee.1971 wrote:
>>> On 30 Jun, 00:56, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>>>> Paul.Lee.1971 wrote:
>>>>> Hi everyone,
>>>>> I'm planning on using MySql/Php on either a Windows or, more likely, a
>>>>> Linux base. However, my database will be several thousands of records,
>>>>> in many hundreds of tables, searched using a "Search" query with a
>>>>> regex expression included. I'm wary about the speed penalties, and I
>>>>> would be very grateful if some metrics or timings could be
>>>>> provided....?
>>>>> Many thanks
>>>>> Paul
>>>>> --
>>>>> http://www.paullee.com
>>>> Paul,
>>>> Thousands of rows is no problem. But "Hundreds of tables"? Even the
>>>> most sophisticated system I ever designed didn't require that many tables!
>>>> --
>>>> ==================
>>>> Remove the "x" from my email address
>>>> Jerry Stuckle
>>>> JDS Computer Training Corp.
>>>> jstuck...@attglobal.net
>>>> ==================
>>> The problem I see is either hundreds of tables, or a table containing
>>> a huge amount of rows/columns!
>> If you properly normalize your database, you should need neither.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================
>
> Ulp! I need help!
>

Start with "Database Normalization". There's some good info on the
MySQL website, or you can google for some topics on it.

Proper database design is not a hit-and-miss proposition. The
procedures are well defined to limit duplicate data while providing good
performance.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>>> The problem I see is either hundreds of tables, or a table containing
>>> a huge amount of rows/columns!
>> If you properly normalize your database, you should need neither.
> Ulp! I need help!

A "huge" amount of rows is not a problem. That is what databases are
designed for. A huge amount of columns or a huge amount of tables
usually is a smell of a poor design.

As you stated earlier, you want to search with a regular expression. Now
that can very well be a really heavy search, because MySQL has to check
all rows to do this. In "normal" searches (that is, search for a given
value of a field) the database does not check all rows but does a binary
search if you have indexed the column properly.
While a regular expression search does take some time, it cannot really
be optimized without knowing more on the searches.

Best regards