[PHP-DB] Re: [PHP] Best practices for using MySQL index

[Shelley]

On Wed, Apr 30, 2008 at 7:03 PM, Aschwin Wesselius <[EMAIL PROTECTED]>
wrote:

>  Shelley wrote:
>
>  Don't index just all integer fields. Keep track of the cardinality of a
> column. If you expect a field to have 100.000 records, but with only 500
> distinct values it has no use to put an index on that column. A full record
> search is quicker.
>
>
> Hmmm... That's new. :)
>
>
>
> Well, to give you a good measure: keep the cardinality between 30 to 70-80
> percent of your total records in a column. But sometimes your field is NULL
> or empty, so it really depends. You can't just put it into a standard
> configuration. And it also really depends on how many records a table
> contains etc.
>
> Besides that, benchmarking your development environment (you do have one
> do you?) can gives you a good idea on how your hardware and setup performs.
>
I think I missed that part. For I am concerning query, index, and entity
design most of the time.

>
>
> Aschwin Wesselius
>



-- 
Regards,
Shelley

Re: [PHP-DB]problem in creating the ibm_db2 extension

[Chris]

H Thirividi wrote:
> 
> 
> On Thu, May 1, 2008 at 5:53 AM, Chris <[EMAIL PROTECTED]
> > wrote:
> 
> H Thirividi wrote:
> > Hi All,
> >
> > I have installed db2 on my* fedora core 7* system and now i wan t
> to access
> > the database using the* php apis*. For this reason I downloaded
> the *ibm_db2
> > 1.6* package and tried to create the extension. I did the following
> >
> > phpize
> >
> > output is
> > Configuring for:
> > PHP Api Version: 20041225
> > Zend Module Api No:  20060613
> > Zend Extension Api No:   220060519
> >
> > ./configure --with-IBM_DB2=/opt/ibm/db2/V9.5
> > part of the ouput
> >
> > checking for re2c... no
> > configure: WARNING: You will need re2c 0.12.0 or later if you want to
> > regenerate PHP parsers.
> > checking for gawk... gawk
> > checking for IBM_DB2 support... yes, shared
> > checking Looking for DB2 CLI libraries... checking  in
> > /opt/ibm/db2/V9.5...
> > checking  in /opt/ibm/db2/V9.5/lib64...
> > checking  in /opt/ibm/db2/V9.5/lib32... found
> > checking for DB2 CLI include files in default path... checking in
> > /opt/ibm/db2/V9.5... not found
> 
> It's looking for the include/, lib/ etc folders under that (ie the
> headers and so on).
> 
> Where are they located?
> 
> Hello Chris,
> 
> I am not sure if after getting a reply to the question one should still
> reply to the mailing list or reply to the *person directly*. If I have
> committed a mistake kindly oblige as this is new to me.

mailing list - so others can chime in with suggestions and/or learn from
the questions/comments.

> After posting the question I just *searched* and then came to know that
> while installing db2, it had created 3 users named *db2inst1*,
> *db2fenc1* and one more *administrative user* whose name i am not able
> to recollect and then saw *db2 cli include files* in *db2inst1's home*
> when i logged onto the system as a db2inst1 user and tried to copy those
> files to /opt/ibm/db2/V9.5 (which is where db2 database is installed) 
> but  gave me an error saying that I cannot copy.

Note - I have never installed db2 so not sure how it works/is set up.

However, with mysql, postgresql, gd & other extensions for php, you have
to install the "devel" or "dev" package along with the main one for php
to compile against.

The "dev" package provides the headers which other packages can use, for
example:

/usr/include/postgresql/*.h
/usr/lib/postgresql/*

PHP looks at those headers (*.h) and uses those to work out functionality.

So when you compile, you have to point php to the base folder which
contains the include/ and lib/ subdirectories.

./configure --with-pgsql=/usr

in your case:

./configure --with-db2=/path/to/db2

under /path/to/db2 you need the include/, lib/ etc folders which include
the program headers. Without those files, php won't compile with db2
support.


It looks like you have the lib stuff there (lib32/ is fine, it was
picked up) but there's no include/ stuff which is what configure is
complaining about.

-- 
Postgresql & php tutorials
http://www.designmagick.com/

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB]problem in creating the ibm_db2 extension

[H Thirividi]

On Thu, May 1, 2008 at 5:53 AM, Chris <[EMAIL PROTECTED]> wrote:

> H Thirividi wrote:
> > Hi All,
> >
> > I have installed db2 on my* fedora core 7* system and now i wan t to
> access
> > the database using the* php apis*. For this reason I downloaded the
> *ibm_db2
> > 1.6* package and tried to create the extension. I did the following
> >
> > phpize
> >
> > output is
> > Configuring for:
> > PHP Api Version: 20041225
> > Zend Module Api No:  20060613
> > Zend Extension Api No:   220060519
> >
> > ./configure --with-IBM_DB2=/opt/ibm/db2/V9.5
> > part of the ouput
> >
> > checking for re2c... no
> > configure: WARNING: You will need re2c 0.12.0 or later if you want to
> > regenerate PHP parsers.
> > checking for gawk... gawk
> > checking for IBM_DB2 support... yes, shared
> > checking Looking for DB2 CLI libraries... checking  in
> > /opt/ibm/db2/V9.5...
> > checking  in /opt/ibm/db2/V9.5/lib64...
> > checking  in /opt/ibm/db2/V9.5/lib32... found
> > checking for DB2 CLI include files in default path... checking in
> > /opt/ibm/db2/V9.5... not found
>
> It's looking for the include/, lib/ etc folders under that (ie the
> headers and so on).
>
> Where are they located?
>
> Hello Chris,

I am not sure if after getting a reply to the question one should still
reply to the mailing list or reply to the *person directly*. If I have
committed a mistake kindly oblige as this is new to me.
After posting the question I just *searched* and then came to know that
while installing db2, it had created 3 users named *db2inst1*,
*db2fenc1*and one more
*administrative user* whose name i am not able to recollect and then saw *db2
cli include files* in *db2inst1's home* when i logged onto the system as a
db2inst1 user and tried to copy those files to /opt/ibm/db2/V9.5 (which is
where db2 database is installed)  but  gave me an error saying that I cannot
copy.
I also changed the permissions and even then could not copy.
So does this mean i have to complie the extension by logging in as db2inst1
and cant this be as root or what should I do to continue further.

With Regards,
Harsha

Re: [PHP-DB] Query

[Ron Piggott]

You just drew my attention my  Ron Piggott wrote:
> > I have an interesting question.
> > 
> > When I run the following query through my PHP script it produces 1
> > result.  I know this because I echo the value of $num to the screen from
> > the following syntax: $num=mysql_numrows($result);
> > 
> > When I do the query in the "SQL" tab of phpMyAdmin there are 6 results.
> > The 6 requests are correct.  
> > 
> > Any ideas why I have 2 different search results?
> 
> Show us the php code.. maybe you're overwriting the $result variable in
> your loop.
> 


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Query

[Chris]

Ron Piggott wrote:
> I have an interesting question.
> 
> When I run the following query through my PHP script it produces 1
> result.  I know this because I echo the value of $num to the screen from
> the following syntax: $num=mysql_numrows($result);
> 
> When I do the query in the "SQL" tab of phpMyAdmin there are 6 results.
> The 6 requests are correct.  
> 
> Any ideas why I have 2 different search results?

Show us the php code.. maybe you're overwriting the $result variable in
your loop.


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Query

[Ron Piggott]

I have an interesting question.

When I run the following query through my PHP script it produces 1
result.  I know this because I echo the value of $num to the screen from
the following syntax: $num=mysql_numrows($result);

When I do the query in the "SQL" tab of phpMyAdmin there are 6 results.
The 6 requests are correct.  

Any ideas why I have 2 different search results?

Ron

SELECT * FROM ministry_directory INNER JOIN
ministry_directory_listing_categories ON ministry_directory.entry =
ministry_directory_listing_categories.ministry_directory_entry WHERE
ministry_directory.listing_type = 2 AND
ministry_directory_listing_categories.ministry_directory_category_reference IN 
( 10 ) ORDER BY ministry_directory.name ASC


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Best practices for using MySQL index

[Chris]

>> Index on most integer fields only. Text fields can be indexed, but is not
>> important when you design your DB well.
>>
>> Don't index just all integer fields. Keep track of the cardinality of a
>> column. If you expect a field to have 100.000 records, but with only 500
>> distinct values it has no use to put an index on that column. A full record
>> search is quicker.
> 
>Hmmm... That's new. :)

To explain that further the idea is that if you have something like a
'status' field which can only hold 5 values, there's no point indexing
it if there's a reasonably even spread.

If you could only ever have a handful of fields with a status code of
'1', then it's worth indexing if you have to find those particular
records quickly. I don't think mysql supports partial indexes, but some
databases do so you only index the fields that match a certain criteria.

I'd suggest a more thorough approach to working out what to index rather
than just trying to guess what's going on.

Work out how long queries are taking (either use the mysql slow log or
if you're using a database abstraction class, it should be easy enough
to hack in) and concentrate on those first.

http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database

(While it's on a postgresql site, there's nothing specifically for
postgresql in that article - the same rules apply to mysql, oracle, mssql).

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB]problem in creating the ibm_db2 extension

[Chris]

H Thirividi wrote:
> Hi All,
> 
> I have installed db2 on my* fedora core 7* system and now i wan t to access
> the database using the* php apis*. For this reason I downloaded the *ibm_db2
> 1.6* package and tried to create the extension. I did the following
> 
> phpize
> 
> output is
> Configuring for:
> PHP Api Version: 20041225
> Zend Module Api No:  20060613
> Zend Extension Api No:   220060519
> 
> ./configure --with-IBM_DB2=/opt/ibm/db2/V9.5
> part of the ouput
> 
> checking for re2c... no
> configure: WARNING: You will need re2c 0.12.0 or later if you want to
> regenerate PHP parsers.
> checking for gawk... gawk
> checking for IBM_DB2 support... yes, shared
> checking Looking for DB2 CLI libraries... checking  in
> /opt/ibm/db2/V9.5...
> checking  in /opt/ibm/db2/V9.5/lib64...
> checking  in /opt/ibm/db2/V9.5/lib32... found
> checking for DB2 CLI include files in default path... checking in
> /opt/ibm/db2/V9.5... not found

It's looking for the include/, lib/ etc folders under that (ie the
headers and so on).

Where are they located?


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Timestamps

[Philip Thompson]

On Apr 30, 2008, at 10:54 AM, Jason Pruim wrote:

Hi Yves,

Thanks for the tip, that worked, I think I'll use that from now on..

Just out of curiosity though, any idea why it wasn't working as I  
was writing it :)


Did you try putting the query that PHP is generating in phpMyAdmin or  
MySQL Query Browser? See if it throws an error when attempting to  
update. It *appears* that the query should work.


~Philip

PS... Was it you, Jason, or someone else who asked about the security  
of the community knowing their database structure and I encouraged the  
use of `backticks` around all field and table names?




On Apr 30, 2008, at 11:47 AM, YVES SUCAET wrote:


Hi Jason,

It's not because you create a date/time value that you  
automatically have an
integer-value. You need to specify first that you want the date/ 
time value

converted to an integer value first.

See
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp
for an example of how to do this.

Actually, by using this function, you probably don't even need to  
create the
$modifiedTimestamp variable anymore. You can just write your SQL  
query as

follows:

$sql = "Update `mytable` set timestamp=UNIX_TIMESTAMP() where  
Record='1'";


HTH,

Yves

-- Original Message --
Received: Wed, 30 Apr 2008 10:39:11 AM CDT
From: Jason Pruim <[EMAIL PROTECTED]>
To: Stut <[EMAIL PROTECTED]>Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Timestamps


On Apr 30, 2008, at 11:35 AM, Stut wrote:


On 30 Apr 2008, at 16:29, Jason Pruim wrote:

Okay... So I know this should be simple...

Trying to store a timestamp in a MySQL database... The timestamp I
am making like so: $modifiedTimestamp = time();

and then just $sql = "Update `mytable` set
timestamp='$modifiedTimestamp' where Record='1'";

Simple right? Not quite...in my database it's storing a "0" in the
timestamp field which is a int(10) field.

I have googled, and searched manuals, but have not been able to
figure out what is going on

Any Ideas?


timestamp is a reserved word. Try putting it in backticks.



Okay, so I did a really crappy job at my sudo code... The field name
is actually Last_Updated.

so my update code looks like this: Last_Updated='$modifiedTimestamp'

*Slaps his wrist... Bad copy/paste! BAD!!!


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Timestamps

[Jason Pruim]

Hi Yves,

Thanks for the tip, that worked, I think I'll use that from now on..

Just out of curiosity though, any idea why it wasn't working as I was  
writing it :)



On Apr 30, 2008, at 11:47 AM, YVES SUCAET wrote:


Hi Jason,

It's not because you create a date/time value that you automatically  
have an
integer-value. You need to specify first that you want the date/time  
value

converted to an integer value first.

See
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp
for an example of how to do this.

Actually, by using this function, you probably don't even need to  
create the
$modifiedTimestamp variable anymore. You can just write your SQL  
query as

follows:

$sql = "Update `mytable` set timestamp=UNIX_TIMESTAMP() where  
Record='1'";


HTH,

Yves

-- Original Message --
Received: Wed, 30 Apr 2008 10:39:11 AM CDT
From: Jason Pruim <[EMAIL PROTECTED]>
To: Stut <[EMAIL PROTECTED]>Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Timestamps


On Apr 30, 2008, at 11:35 AM, Stut wrote:


On 30 Apr 2008, at 16:29, Jason Pruim wrote:

Okay... So I know this should be simple...

Trying to store a timestamp in a MySQL database... The timestamp I
am making like so: $modifiedTimestamp = time();

and then just $sql = "Update `mytable` set
timestamp='$modifiedTimestamp' where Record='1'";

Simple right? Not quite...in my database it's storing a "0" in the
timestamp field which is a int(10) field.

I have googled, and searched manuals, but have not been able to
figure out what is going on

Any Ideas?


timestamp is a reserved word. Try putting it in backticks.



Okay, so I did a really crappy job at my sudo code... The field name
is actually Last_Updated.

so my update code looks like this: Last_Updated='$modifiedTimestamp'

*Slaps his wrist... Bad copy/paste! BAD!!!

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
[EMAIL PROTECTED]




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php






--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
[EMAIL PROTECTED]




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Timestamps

[YVES SUCAET]

Hi Jason,

It's not because you create a date/time value that you automatically have an
integer-value. You need to specify first that you want the date/time value
converted to an integer value first. 

See
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp
for an example of how to do this. 

Actually, by using this function, you probably don't even need to create the
$modifiedTimestamp variable anymore. You can just write your SQL query as
follows:

$sql = "Update `mytable` set timestamp=UNIX_TIMESTAMP() where Record='1'";

HTH,

Yves

-- Original Message --
Received: Wed, 30 Apr 2008 10:39:11 AM CDT
From: Jason Pruim <[EMAIL PROTECTED]>
To: Stut <[EMAIL PROTECTED]>Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Timestamps


On Apr 30, 2008, at 11:35 AM, Stut wrote:

> On 30 Apr 2008, at 16:29, Jason Pruim wrote:
>> Okay... So I know this should be simple...
>>
>> Trying to store a timestamp in a MySQL database... The timestamp I  
>> am making like so: $modifiedTimestamp = time();
>>
>> and then just $sql = "Update `mytable` set  
>> timestamp='$modifiedTimestamp' where Record='1'";
>>
>> Simple right? Not quite...in my database it's storing a "0" in the  
>> timestamp field which is a int(10) field.
>>
>> I have googled, and searched manuals, but have not been able to  
>> figure out what is going on
>>
>> Any Ideas?
>
> timestamp is a reserved word. Try putting it in backticks.


Okay, so I did a really crappy job at my sudo code... The field name  
is actually Last_Updated.

so my update code looks like this: Last_Updated='$modifiedTimestamp'

*Slaps his wrist... Bad copy/paste! BAD!!!

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
[EMAIL PROTECTED]




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php






--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Timestamps

[Jason Pruim]

On Apr 30, 2008, at 11:35 AM, Stut wrote:


On 30 Apr 2008, at 16:29, Jason Pruim wrote:

Okay... So I know this should be simple...

Trying to store a timestamp in a MySQL database... The timestamp I  
am making like so: $modifiedTimestamp = time();


and then just $sql = "Update `mytable` set  
timestamp='$modifiedTimestamp' where Record='1'";


Simple right? Not quite...in my database it's storing a "0" in the  
timestamp field which is a int(10) field.


I have googled, and searched manuals, but have not been able to  
figure out what is going on


Any Ideas?


timestamp is a reserved word. Try putting it in backticks.



Okay, so I did a really crappy job at my sudo code... The field name  
is actually Last_Updated.


so my update code looks like this: Last_Updated='$modifiedTimestamp'

*Slaps his wrist... Bad copy/paste! BAD!!!

--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
[EMAIL PROTECTED]




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] Timestamps

[Stut]

On 30 Apr 2008, at 16:29, Jason Pruim wrote:

Okay... So I know this should be simple...

Trying to store a timestamp in a MySQL database... The timestamp I  
am making like so: $modifiedTimestamp = time();


and then just $sql = "Update `mytable` set  
timestamp='$modifiedTimestamp' where Record='1'";


Simple right? Not quite...in my database it's storing a "0" in the  
timestamp field which is a int(10) field.


I have googled, and searched manuals, but have not been able to  
figure out what is going on


Any Ideas?


timestamp is a reserved word. Try putting it in backticks.

-Stut

--
http://stut.net/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Timestamps

[Jason Pruim]

Okay... So I know this should be simple...

Trying to store a timestamp in a MySQL database... The timestamp I am  
making like so: $modifiedTimestamp = time();


and then just $sql = "Update `mytable` set  
timestamp='$modifiedTimestamp' where Record='1'";


Simple right? Not quite...in my database it's storing a "0" in the  
timestamp field which is a int(10) field.


I have googled, and searched manuals, but have not been able to figure  
out what is going on


Any Ideas?
--

Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424-9337
www.raoset.com
[EMAIL PROTECTED]




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Best practices for using MySQL index

[Robert Cummings]

On Wed, 2008-04-30 at 11:14 +0200, Aschwin Wesselius wrote:
> Shelley wrote:
> > Hi all,
> >
> > I am currently responsible for a subscription module and need to design the
> > DB tables and write code.
> >
> > I have described my table design and queries in the post:
> > http://phparch.cn/index.php/mysql/38-MySQL-configuration/152-best-practices-for-using-mysql-index
> >
> > The problem is, in a short time the table will hold millions of records.
> > So the query and index optimization is very important.
> >
> > Any suggestion will be greatly appreciated.
> Hi,
> 
> While this is not a MySQL mailing list, I try to give you some hints and 
> keep it short.
> 
> Index on most integer fields only. Text fields can be indexed, but is 
> not important when you design your DB well.

Could you describe a well designed DB that contains searchable text that
doesn't contain a text index... fulltext or otherwise.

Cheers,
Rob.
-- 
http://www.interjinn.com
Application and Templating Framework for PHP


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[PHP-DB] Re: [PHP] Best practices for using MySQL index

[Aschwin Wesselius]

Shelley wrote:

Don't index just all integer fields. Keep track of the cardinality of a
column. If you expect a field to have 100.000 records, but with only 500
distinct values it has no use to put an index on that column. A full record
search is quicker.



   Hmmm... That's new. :)



Well, to give you a good measure: keep the cardinality between 30 to 
70-80 percent of your total records in a column. But sometimes your 
field is NULL or empty, so it really depends. You can't just put it into 
a standard configuration. And it also really depends on how many records 
a table contains etc.


Besides that, benchmarking your development environment (you do have one 
do you?) can gives you a good idea on how your hardware and setup performs.


Aschwin Wesselius

[PHP-DB] Re: [PHP] Best practices for using MySQL index

[Shelley]

On Wed, Apr 30, 2008 at 5:14 PM, Aschwin Wesselius <[EMAIL PROTECTED]>
wrote:

> Shelley wrote:
>
> > Hi all,
> >
> > I am currently responsible for a subscription module and need to design
> > the
> > DB tables and write code.
> >
> > I have described my table design and queries in the post:
> >
> > http://phparch.cn/index.php/mysql/38-MySQL-configuration/152-best-practices-for-using-mysql-index
> >
> > The problem is, in a short time the table will hold millions of records.
> > So the query and index optimization is very important.
> >
> > Any suggestion will be greatly appreciated.
> >
> Hi,
>
> While this is not a MySQL mailing list, I try to give you some hints and
> keep it short.
>
> Index on most integer fields only. Text fields can be indexed, but is not
> important when you design your DB well.
>
> Don't index just all integer fields. Keep track of the cardinality of a
> column. If you expect a field to have 100.000 records, but with only 500
> distinct values it has no use to put an index on that column. A full record
> search is quicker.

   Hmmm... That's new. :)

>
>
> Put the columns with the highest cardinality as the first keys, since
> MySQL will find these if no index is explicitly given.
>
> You can look at an index with "SHOW INDEX FROM table" and this gives you a
> column "cardinality".
>
> Try out your select statements and use "EXPLAIN SELECT  FROM
> table" and use some joins on other tables. This will show you which possible
> indexes are found and which one is being used for that query. You can
> sometimes force or ignore an index being used like this "SELECT 
> FROM table USE INDEX (userID)". Try the MySQL manual for more options. But
> do use the "EXPLAIN" statement to have a close look on the use of indexes
> and the use of sorting methods. Because both are important. Having a good
> index, but a slow sorting method won't get you good results.
>
> I hope this is a good short hint on using indexes.

Yes. It is.

> But becoming a master does not come over night. Try the website
> www.mysqlperformanceblog.com for more good solid tips on these topics.

Good link. Thanks.

>
>
> Aschwin Wesselius
>



-- 
Regards,
Shelley

[PHP-DB] Re: [PHP] Best practices for using MySQL index

[Aschwin Wesselius]

Shelley wrote:

Hi all,

I am currently responsible for a subscription module and need to design the
DB tables and write code.

I have described my table design and queries in the post:
http://phparch.cn/index.php/mysql/38-MySQL-configuration/152-best-practices-for-using-mysql-index

The problem is, in a short time the table will hold millions of records.
So the query and index optimization is very important.

Any suggestion will be greatly appreciated.

Hi,

While this is not a MySQL mailing list, I try to give you some hints and 
keep it short.


Index on most integer fields only. Text fields can be indexed, but is 
not important when you design your DB well.


Don't index just all integer fields. Keep track of the cardinality of a 
column. If you expect a field to have 100.000 records, but with only 500 
distinct values it has no use to put an index on that column. A full 
record search is quicker.


Put the columns with the highest cardinality as the first keys, since 
MySQL will find these if no index is explicitly given.


You can look at an index with "SHOW INDEX FROM table" and this gives you 
a column "cardinality".


Try out your select statements and use "EXPLAIN SELECT  FROM 
table" and use some joins on other tables. This will show you which 
possible indexes are found and which one is being used for that query. 
You can sometimes force or ignore an index being used like this "SELECT 
 FROM table USE INDEX (userID)". Try the MySQL manual for more 
options. But do use the "EXPLAIN" statement to have a close look on the 
use of indexes and the use of sorting methods. Because both are 
important. Having a good index, but a slow sorting method won't get you 
good results.


I hope this is a good short hint on using indexes. But becoming a master 
does not come over night. Try the website www.mysqlperformanceblog.com 
for more good solid tips on these topics.


Aschwin Wesselius

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php