Re: showing warnings
am 10.09.2007 16:12:50 von John Mertic
Run the query through MySQL directly prepending EXPLAIN to the query
and attach the results here.
John
On 9/10/07, Asim wrote:
> Hi
>
> i have added index on both columns but still same warning is issued by mysql
>
> then what i do now
>
> John Mertic wrote:
> Is the table 'Mobile_Tunes' indexed at all? If not, you may want to
> consider adding one at least one 'Tune_Type' and another on
> 'Tune_Name'.
>
> John
>
> On 9/10/07, Asim wrote:
> > Hi
> >
> > script is following
> >
> > if(isset($_GET['SearchTune']))
> > {
> > $tunename = substr($_GET['tune_name'],0,3);
> > $tunetype = $_GET['tune_type'];
> > $sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes WHERE Tune_Name LIKE
> > '".$tunename."%' AND Tune_Type = '".$tunetype."'";
> > }
> > else
> > {
> > $sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes";
> > }
> >
> >
> ............................................................ ...............
> >
> > function
> > max_page($sql,$total_rows,$rows_per_page,$max_pages)
> > {
> > $all_rs = mysql_query($sql);
> > if(!$all_rs)
> > {
> > echo "Sorry! Try again.";
> > return false;
> > }
> > $total_rows = mysql_num_rows($all_rs);
> > mysql_free_result($all_rs);
> > //mysql_close($link);
> > $max_pages = ceil($total_rows/$rows_per_page);
> > return $max_pages;
> > }
> >
> >
> > when i use this function it shows following warning
> >
> > Warning: mysql_query(): Your query requires a full tablescan (table
> > Mobile_Tunes, 5 rows affected). Use EXPLAIN to optimize your query. in
> > D:\Program Files\Apache
> > Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
> >
> >
> >
> > now can you tell me some solution
> >
> > bye
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > John Mertic wrote:
> > Can you provide the script you are running and the exact error message
> > you are getting?
> >
> > John
> >
> > On 9/9/07, Asim wrote:
> > > Hi
> > >
> > >
> > > in my script when i scan whole table for result set to display in my
> page
> > it shows warning that suggest me to use
> > >
> > >
> > > EXPLAIN statement but i donot need it as i have to show records on page
> > >
> > > how to avoid this warning?
> > >
> > > Bye
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Asim Jamil - 0092 345 4025907, Pakistan
> > >
> > > ---------------------------------
> > > Be a better Globetrotter. Get better travel answers from someone who
> > knows.
> > > Yahoo! Answers - Check it out.
> >
> >
> > --
> > --
> > John Mertic "Explaining a joke
> > is like dissecting a frog: you
> > jmertic@gmail.com understand it better,
> > but the frog dies in the
> > process."
> >
> > -Mark Twain
> >
> > --
> > PHP Windows Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
> >
> >
> > Asim Jamil - 0092 345 4025907, Pakistan
> >
> > ________________________________
> > Be a better Heartthrob. Get better relationship answers from someone who
> > knows.
> > Yahoo! Answers - Check it out.
> >
> >
>
>
> --
> --
> John Mertic "Explaining a joke
> is like dissecting a frog: you
> jmertic@gmail.com understand it better,
> but the frog dies in the
> process."
>
> -Mark Twain
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>
> Asim Jamil - 0092 345 4025907, Pakistan
>
> ________________________________
> Looking for a deal? Find great prices on flights and hotels with Yahoo!
> FareChase.
>
>
--
--
John Mertic "Explaining a joke
is like dissecting a frog: you
jmertic@gmail.com understand it better,
but the frog dies in the
process."
-Mark Twain
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
showing warnings
am 11.09.2007 07:33:34 von Asim
--0-1038040981-1189488814=:59745
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Hi
this result is shown by mysql on running command prompt
mysql> explain SELECT Tune_Name,Tune_Type FROM Mobile_Tunes;
+--------------+------+---------------+------+---------+---- --+------+-------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+--------------+------+---------------+------+---------+---- --+------+-------+
| Mobile_Tunes | ALL | NULL | NULL | NULL | NULL | 4 |
|
+--------------+------+---------------+------+---------+---- --+------+-------+
1 row in set (0.00 sec)
mysql>
and
mysql> SELECT Tune_Name,Tune_Type FROM Mobile_Tunes;
+-----------+-----------+
| Tune_Name | Tune_Type |
+-----------+-----------+
| pakistani | midi |
| pakistan2 | midi |
| usa | midi |
| uk | midi |
+-----------+-----------+
4 rows in set (0.00 sec)
mysql>
now what next?
bye
John Mertic wrote: Run the query through MySQL directly prepending EXPLAIN to the query
and attach the results here.
John
On 9/10/07, Asim wrote:
> Hi
>
> i have added index on both columns but still same warning is issued by mysql
>
> then what i do now
>
> John Mertic wrote:
> Is the table 'Mobile_Tunes' indexed at all? If not, you may want to
> consider adding one at least one 'Tune_Type' and another on
> 'Tune_Name'.
>
> John
>
> On 9/10/07, Asim wrote:
> > Hi
> >
> > script is following
> >
> > if(isset($_GET['SearchTune']))
> > {
> > $tunename = substr($_GET['tune_name'],0,3);
> > $tunetype = $_GET['tune_type'];
> > $sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes WHERE Tune_Name LIKE
> > '".$tunename."%' AND Tune_Type = '".$tunetype."'";
> > }
> > else
> > {
> > $sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes";
> > }
> >
> >
> ............................................................ ...............
> >
> > function
> > max_page($sql,$total_rows,$rows_per_page,$max_pages)
> > {
> > $all_rs = mysql_query($sql);
> > if(!$all_rs)
> > {
> > echo "Sorry! Try again.";
> > return false;
> > }
> > $total_rows = mysql_num_rows($all_rs);
> > mysql_free_result($all_rs);
> > //mysql_close($link);
> > $max_pages = ceil($total_rows/$rows_per_page);
> > return $max_pages;
> > }
> >
> >
> > when i use this function it shows following warning
> >
> > Warning: mysql_query(): Your query requires a full tablescan (table
> > Mobile_Tunes, 5 rows affected). Use EXPLAIN to optimize your query. in
> > D:\Program Files\Apache
> > Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
> >
> >
> >
> > now can you tell me some solution
> >
> > bye
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > John Mertic wrote:
> > Can you provide the script you are running and the exact error message
> > you are getting?
> >
> > John
> >
> > On 9/9/07, Asim wrote:
> > > Hi
> > >
> > >
> > > in my script when i scan whole table for result set to display in my
> page
> > it shows warning that suggest me to use
> > >
> > >
> > > EXPLAIN statement but i donot need it as i have to show records on page
> > >
> > > how to avoid this warning?
> > >
> > > Bye
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Asim Jamil - 0092 345 4025907, Pakistan
> > >
> > > ---------------------------------
> > > Be a better Globetrotter. Get better travel answers from someone who
> > knows.
> > > Yahoo! Answers - Check it out.
> >
> >
> > --
> > --
> > John Mertic "Explaining a joke
> > is like dissecting a frog: you
> > jmertic@gmail.com understand it better,
> > but the frog dies in the
> > process."
> >
> > -Mark Twain
> >
> > --
> > PHP Windows Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
> >
> >
> > Asim Jamil - 0092 345 4025907, Pakistan
> >
> > ________________________________
> > Be a better Heartthrob. Get better relationship answers from someone who
> > knows.
> > Yahoo! Answers - Check it out.
> >
> >
>
>
> --
> --
> John Mertic "Explaining a joke
> is like dissecting a frog: you
> jmertic@gmail.com understand it better,
> but the frog dies in the
> process."
>
> -Mark Twain
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>
> Asim Jamil - 0092 345 4025907, Pakistan
>
> ________________________________
> Looking for a deal? Find great prices on flights and hotels with Yahoo!
> FareChase.
>
>
--
--
John Mertic "Explaining a joke
is like dissecting a frog: you
jmertic@gmail.com understand it better,
but the frog dies in the
process."
-Mark Twain
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Asim Jamil - 0092 345 4025907, Pakistan
---------------------------------
Tonight's top picks. What will you watch tonight? Preview the hottest shows on Yahoo! TV.
--0-1038040981-1189488814=:59745--
showing warnings
am 11.09.2007 07:34:01 von Asim
--0-246574969-1189488841=:85301
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Hi
this result is shown by mysql on running command prompt
mysql> explain SELECT Tune_Name,Tune_Type FROM Mobile_Tunes;
+--------------+------+---------------+------+---------+---- --+------+-------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+--------------+------+---------------+------+---------+---- --+------+-------+
| Mobile_Tunes | ALL | NULL | NULL | NULL | NULL | 4 |
|
+--------------+------+---------------+------+---------+---- --+------+-------+
1 row in set (0.00 sec)
mysql>
and
mysql> SELECT Tune_Name,Tune_Type FROM Mobile_Tunes;
+-----------+-----------+
| Tune_Name | Tune_Type |
+-----------+-----------+
| pakistani | midi |
| pakistan2 | midi |
| usa | midi |
| uk | midi |
+-----------+-----------+
4 rows in set (0.00 sec)
mysql>
now what next?
bye
John Mertic wrote: Run the query through MySQL directly prepending EXPLAIN to the query
and attach the results here.
John
On 9/10/07, Asim wrote:
> Hi
>
> i have added index on both columns but still same warning is issued by mysql
>
> then what i do now
>
> John Mertic wrote:
> Is the table 'Mobile_Tunes' indexed at all? If not, you may want to
> consider adding one at least one 'Tune_Type' and another on
> 'Tune_Name'.
>
> John
>
> On 9/10/07, Asim wrote:
> > Hi
> >
> > script is following
> >
> > if(isset($_GET['SearchTune']))
> > {
> > $tunename = substr($_GET['tune_name'],0,3);
> > $tunetype = $_GET['tune_type'];
> > $sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes WHERE Tune_Name LIKE
> > '".$tunename."%' AND Tune_Type = '".$tunetype."'";
> > }
> > else
> > {
> > $sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes";
> > }
> >
> >
> ............................................................ ...............
> >
> > function
> > max_page($sql,$total_rows,$rows_per_page,$max_pages)
> > {
> > $all_rs = mysql_query($sql);
> > if(!$all_rs)
> > {
> > echo "Sorry! Try again.";
> > return false;
> > }
> > $total_rows = mysql_num_rows($all_rs);
> > mysql_free_result($all_rs);
> > //mysql_close($link);
> > $max_pages = ceil($total_rows/$rows_per_page);
> > return $max_pages;
> > }
> >
> >
> > when i use this function it shows following warning
> >
> > Warning: mysql_query(): Your query requires a full tablescan (table
> > Mobile_Tunes, 5 rows affected). Use EXPLAIN to optimize your query. in
> > D:\Program Files\Apache
> > Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
> >
> >
> >
> > now can you tell me some solution
> >
> > bye
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > John Mertic wrote:
> > Can you provide the script you are running and the exact error message
> > you are getting?
> >
> > John
> >
> > On 9/9/07, Asim wrote:
> > > Hi
> > >
> > >
> > > in my script when i scan whole table for result set to display in my
> page
> > it shows warning that suggest me to use
> > >
> > >
> > > EXPLAIN statement but i donot need it as i have to show records on page
> > >
> > > how to avoid this warning?
> > >
> > > Bye
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Asim Jamil - 0092 345 4025907, Pakistan
> > >
> > > ---------------------------------
> > > Be a better Globetrotter. Get better travel answers from someone who
> > knows.
> > > Yahoo! Answers - Check it out.
> >
> >
> > --
> > --
> > John Mertic "Explaining a joke
> > is like dissecting a frog: you
> > jmertic@gmail.com understand it better,
> > but the frog dies in the
> > process."
> >
> > -Mark Twain
> >
> > --
> > PHP Windows Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
> >
> >
> > Asim Jamil - 0092 345 4025907, Pakistan
> >
> > ________________________________
> > Be a better Heartthrob. Get better relationship answers from someone who
> > knows.
> > Yahoo! Answers - Check it out.
> >
> >
>
>
> --
> --
> John Mertic "Explaining a joke
> is like dissecting a frog: you
> jmertic@gmail.com understand it better,
> but the frog dies in the
> process."
>
> -Mark Twain
>
> --
> PHP Windows Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>
> Asim Jamil - 0092 345 4025907, Pakistan
>
> ________________________________
> Looking for a deal? Find great prices on flights and hotels with Yahoo!
> FareChase.
>
>
--
--
John Mertic "Explaining a joke
is like dissecting a frog: you
jmertic@gmail.com understand it better,
but the frog dies in the
process."
-Mark Twain
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Asim Jamil - 0092 345 4025907, Pakistan
---------------------------------
Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more.
--0-246574969-1189488841=:85301--
Re: showing warnings
am 11.09.2007 11:31:01 von Niel Archer
>
> mysql> explain SELECT Tune_Name,Tune_Type FROM Mobile_Tunes;
> +--------------+------+---------------+------+---------+---- --+------+-------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
> +--------------+------+---------------+------+---------+---- --+------+-------+
> | Mobile_Tunes | ALL | NULL | NULL | NULL | NULL | 4 |
> |
> +--------------+------+---------------+------+---------+---- --+------+-------+
> 1 row in set (0.00 sec)
> mysql>
This shows you have NO indexes on the table. Assuming that 'Tune_Name'
contains unique entries for each row, I'd suggest you do the following
as a minimum:
ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
--
Niel Archer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: showing warnings
am 11.09.2007 12:35:42 von dmagick
On 9/11/07, Niel Archer wrote:
> >
> > mysql> explain SELECT Tune_Name,Tune_Type FROM Mobile_Tunes;
> > +--------------+------+---------------+------+---------+---- --+------+-------+
> > | table | type | possible_keys | key | key_len | ref | rows |
> > Extra |
> > +--------------+------+---------------+------+---------+---- --+------+-------+
> > | Mobile_Tunes | ALL | NULL | NULL | NULL | NULL | 4 |
> > |
> > +--------------+------+---------------+------+---------+---- --+------+-------+
> > 1 row in set (0.00 sec)
> > mysql>
>
> This shows you have NO indexes on the table. Assuming that 'Tune_Name'
> contains unique entries for each row, I'd suggest you do the following
> as a minimum:
>
> ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
How's that going to help if he's getting all records from the table?
Also there's no way a database is going to use an index if there are
only 4 rows in the table.
--
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: showing warnings
am 11.09.2007 13:46:01 von Niel Archer
> > This shows you have NO indexes on the table. Assuming that 'Tune_Name'
> > contains unique entries for each row, I'd suggest you do the following
> > as a minimum:
> >
> > ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
>
> How's that going to help if he's getting all records from the table?
Depends what you mean by help.
Improve performance of the query, not at all, but that wasn't the
original question.
In a previous post he said he'd added indexes which his EXPLAIN output
clearly shows is not the case. That is simply an example of how to add
a PRIMARY KEY after creating the table. However, it will stop duplicate
entries from being entered and prepare the way for when he's NOT
selecting all rows, so is not entirely wasted.
> Also there's no way a database is going to use an index if there are
> only 4 rows in the table.
True, but if it was only ever going to have 4 entries in it I doubt he'd
be using a Db at all. I'm assuming this is just a sample.
--
Niel Archer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: showing warnings
am 12.09.2007 07:55:58 von Asim
--0-1260310874-1189576558=:49461
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Hi
i am including table structure and other results to you
CREATE TABLE Mobile_Tunes
(
Tune_ID int(6) NOT NULL AUTO_INCREMENT,
Tune_Name varchar(30) NOT NULL,
Tune_Type varchar(10) NOT NULL,
PRIMARY KEY (Tune_ID),
UNIQUE KEY Tune_Name (Tune_Name),
INDEX Tune_Type (Tune_Type)
)ENGINE=MyISAM;
mysql> desc mobile_tunes;
+-----------+-------------+------+-----+---------+---------- ------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+---------- ------+
| Tune_ID | int(6) | | PRI | NULL | auto_increment |
| Tune_Name | varchar(30) | | UNI | | |
| Tune_Type | varchar(10) | | MUL | | |
+-----------+-------------+------+-----+---------+---------- ------+
3 rows in set (0.03 sec)
mysql> explain select Tune_name, tune_type from mobile_tunes;
+--------------+------+---------------+------+---------+---- --+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+------+---------------+------+---------+---- --+------+-------+
| mobile_tunes | ALL | NULL | NULL | NULL | NULL | 4 | |
+--------------+------+---------------+------+---------+---- --+------+-------+
1 row in set (0.02 sec)
mysql> select Tune_name, tune_type from mobile_tunes;
+-----------+-----------+
| Tune_name | tune_type |
+-----------+-----------+
| pakistani | midi |
| pakistan2 | midi |
| usa | midi |
| uk | midi |
+-----------+-----------+
4 rows in set (0.01 sec)
ISSUED WARNINGS
****************************************
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 70
************************************************************
this table will be populated with at least 60,000 records. these 4 records are just for testing purpose.
so if you can tell me what to do to avoid this warning, then it will be great help for me.
Bye
Niel Archer wrote:
> > This shows you have NO indexes on the table. Assuming that 'Tune_Name'
> > contains unique entries for each row, I'd suggest you do the following
> > as a minimum:
> >
> > ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
>
> How's that going to help if he's getting all records from the table?
Depends what you mean by help.
Improve performance of the query, not at all, but that wasn't the
original question.
In a previous post he said he'd added indexes which his EXPLAIN output
clearly shows is not the case. That is simply an example of how to add
a PRIMARY KEY after creating the table. However, it will stop duplicate
entries from being entered and prepare the way for when he's NOT
selecting all rows, so is not entirely wasted.
> Also there's no way a database is going to use an index if there are
> only 4 rows in the table.
True, but if it was only ever going to have 4 entries in it I doubt he'd
be using a Db at all. I'm assuming this is just a sample.
--
Niel Archer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Asim Jamil - 0092 345 4025907, Pakistan
---------------------------------
Catch up on fall's hot new shows on Yahoo! TV. Watch previews, get listings, and more!
--0-1260310874-1189576558=:49461--
Re: showing warnings
am 12.09.2007 07:56:28 von Asim
--0-130866799-1189576588=:86920
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Hi
i am including table structure and other results to you
CREATE TABLE Mobile_Tunes
(
Tune_ID int(6) NOT NULL AUTO_INCREMENT,
Tune_Name varchar(30) NOT NULL,
Tune_Type varchar(10) NOT NULL,
PRIMARY KEY (Tune_ID),
UNIQUE KEY Tune_Name (Tune_Name),
INDEX Tune_Type (Tune_Type)
)ENGINE=MyISAM;
mysql> desc mobile_tunes;
+-----------+-------------+------+-----+---------+---------- ------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+---------- ------+
| Tune_ID | int(6) | | PRI | NULL | auto_increment |
| Tune_Name | varchar(30) | | UNI | | |
| Tune_Type | varchar(10) | | MUL | | |
+-----------+-------------+------+-----+---------+---------- ------+
3 rows in set (0.03 sec)
mysql> explain select Tune_name, tune_type from mobile_tunes;
+--------------+------+---------------+------+---------+---- --+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+------+---------------+------+---------+---- --+------+-------+
| mobile_tunes | ALL | NULL | NULL | NULL | NULL | 4 | |
+--------------+------+---------------+------+---------+---- --+------+-------+
1 row in set (0.02 sec)
mysql> select Tune_name, tune_type from mobile_tunes;
+-----------+-----------+
| Tune_name | tune_type |
+-----------+-----------+
| pakistani | midi |
| pakistan2 | midi |
| usa | midi |
| uk | midi |
+-----------+-----------+
4 rows in set (0.01 sec)
ISSUED WARNINGS
****************************************
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 70
************************************************************
this table will be populated with at least 60,000 records. these 4 records are just for testing purpose.
so if you can tell me what to do to avoid this warning, then it will be great help for me.
Bye
Niel Archer wrote:
> > This shows you have NO indexes on the table. Assuming that 'Tune_Name'
> > contains unique entries for each row, I'd suggest you do the following
> > as a minimum:
> >
> > ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
>
> How's that going to help if he's getting all records from the table?
Depends what you mean by help.
Improve performance of the query, not at all, but that wasn't the
original question.
In a previous post he said he'd added indexes which his EXPLAIN output
clearly shows is not the case. That is simply an example of how to add
a PRIMARY KEY after creating the table. However, it will stop duplicate
entries from being entered and prepare the way for when he's NOT
selecting all rows, so is not entirely wasted.
> Also there's no way a database is going to use an index if there are
> only 4 rows in the table.
True, but if it was only ever going to have 4 entries in it I doubt he'd
be using a Db at all. I'm assuming this is just a sample.
--
Niel Archer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Asim Jamil - 0092 345 4025907, Pakistan
---------------------------------
Moody friends. Drama queens. Your life? Nope! - their life, your story.
Play Sims Stories at Yahoo! Games.
--0-130866799-1189576588=:86920--
Re: showing warnings
am 12.09.2007 07:57:02 von Asim
--0-939594005-1189576622=:64339
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Hi
i am including table structure and other results to you
CREATE TABLE Mobile_Tunes
(
Tune_ID int(6) NOT NULL AUTO_INCREMENT,
Tune_Name varchar(30) NOT NULL,
Tune_Type varchar(10) NOT NULL,
PRIMARY KEY (Tune_ID),
UNIQUE KEY Tune_Name (Tune_Name),
INDEX Tune_Type (Tune_Type)
)ENGINE=MyISAM;
mysql> desc mobile_tunes;
+-----------+-------------+------+-----+---------+---------- ------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+---------- ------+
| Tune_ID | int(6) | | PRI | NULL | auto_increment |
| Tune_Name | varchar(30) | | UNI | | |
| Tune_Type | varchar(10) | | MUL | | |
+-----------+-------------+------+-----+---------+---------- ------+
3 rows in set (0.03 sec)
mysql> explain select Tune_name, tune_type from mobile_tunes;
+--------------+------+---------------+------+---------+---- --+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+------+---------------+------+---------+---- --+------+-------+
| mobile_tunes | ALL | NULL | NULL | NULL | NULL | 4 | |
+--------------+------+---------------+------+---------+---- --+------+-------+
1 row in set (0.02 sec)
mysql> select Tune_name, tune_type from mobile_tunes;
+-----------+-----------+
| Tune_name | tune_type |
+-----------+-----------+
| pakistani | midi |
| pakistan2 | midi |
| usa | midi |
| uk | midi |
+-----------+-----------+
4 rows in set (0.01 sec)
ISSUED WARNINGS
****************************************
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 70
************************************************************
this table will be populated with at least 60,000 records. these 4 records are just for testing purpose.
so if you can tell me what to do to avoid this warning, then it will be great help for me.
Bye
Niel Archer wrote:
> > This shows you have NO indexes on the table. Assuming that 'Tune_Name'
> > contains unique entries for each row, I'd suggest you do the following
> > as a minimum:
> >
> > ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
>
> How's that going to help if he's getting all records from the table?
Depends what you mean by help.
Improve performance of the query, not at all, but that wasn't the
original question.
In a previous post he said he'd added indexes which his EXPLAIN output
clearly shows is not the case. That is simply an example of how to add
a PRIMARY KEY after creating the table. However, it will stop duplicate
entries from being entered and prepare the way for when he's NOT
selecting all rows, so is not entirely wasted.
> Also there's no way a database is going to use an index if there are
> only 4 rows in the table.
True, but if it was only ever going to have 4 entries in it I doubt he'd
be using a Db at all. I'm assuming this is just a sample.
--
Niel Archer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Asim Jamil - 0092 345 4025907, Pakistan
---------------------------------
Boardwalk for $500? In 2007? Ha!
Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
--0-939594005-1189576622=:64339--
Re: how to avoid showing warnings
am 12.09.2007 07:57:16 von Asim
--0-687107049-1189576636=:70262
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Hi
i am including table structure and other results to you
CREATE TABLE Mobile_Tunes
(
Tune_ID int(6) NOT NULL AUTO_INCREMENT,
Tune_Name varchar(30) NOT NULL,
Tune_Type varchar(10) NOT NULL,
PRIMARY KEY (Tune_ID),
UNIQUE KEY Tune_Name (Tune_Name),
INDEX Tune_Type (Tune_Type)
)ENGINE=MyISAM;
mysql> desc mobile_tunes;
+-----------+-------------+------+-----+---------+---------- ------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+---------- ------+
| Tune_ID | int(6) | | PRI | NULL | auto_increment |
| Tune_Name | varchar(30) | | UNI | | |
| Tune_Type | varchar(10) | | MUL | | |
+-----------+-------------+------+-----+---------+---------- ------+
3 rows in set (0.03 sec)
mysql> explain select Tune_name, tune_type from mobile_tunes;
+--------------+------+---------------+------+---------+---- --+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+--------------+------+---------------+------+---------+---- --+------+-------+
| mobile_tunes | ALL | NULL | NULL | NULL | NULL | 4 | |
+--------------+------+---------------+------+---------+---- --+------+-------+
1 row in set (0.02 sec)
mysql> select Tune_name, tune_type from mobile_tunes;
+-----------+-----------+
| Tune_name | tune_type |
+-----------+-----------+
| pakistani | midi |
| pakistan2 | midi |
| usa | midi |
| uk | midi |
+-----------+-----------+
4 rows in set (0.01 sec)
ISSUED WARNINGS
****************************************
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 70
************************************************************
this table will be populated with at least 60,000 records. these 4 records are just for testing purpose.
so if you can tell me what to do to avoid this warning, then it will be great help for me.
Bye
Niel Archer wrote:
> > This shows you have NO indexes on the table. Assuming that 'Tune_Name'
> > contains unique entries for each row, I'd suggest you do the following
> > as a minimum:
> >
> > ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
>
> How's that going to help if he's getting all records from the table?
Depends what you mean by help.
Improve performance of the query, not at all, but that wasn't the
original question.
In a previous post he said he'd added indexes which his EXPLAIN output
clearly shows is not the case. That is simply an example of how to add
a PRIMARY KEY after creating the table. However, it will stop duplicate
entries from being entered and prepare the way for when he's NOT
selecting all rows, so is not entirely wasted.
> Also there's no way a database is going to use an index if there are
> only 4 rows in the table.
True, but if it was only ever going to have 4 entries in it I doubt he'd
be using a Db at all. I'm assuming this is just a sample.
--
Niel Archer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Asim Jamil - 0092 345 4025907, Pakistan
---------------------------------
Shape Yahoo! in your own image. Join our Network Research Panel today!
--0-687107049-1189576636=:70262--
Re: how to avoid showing warnings
am 12.09.2007 09:14:01 von dmagick
> Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
>
> Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 70
>
>
> ************************************************************
> this table will be populated with at least 60,000 records. these 4 records are just for testing purpose.
> so if you can tell me what to do to avoid this warning, then it will be great help for me.
If you're selecting all rows from the table (bad idea for 60,000 rows),
then you won't be able to avoid it.
If you want to select a portion of the rows, create an index on the
relevant fields.
--
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: showing warnings
am 12.09.2007 13:50:41 von John Mertic
How about an EXPLAIN for the other query, namely:
$sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes WHERE Tune_Name
LIKE '".$tunename."%' AND Tune_Type = '".$tunetype."'";
I think that's the one that's an issue. You may also want to try
rewriting it like this:
$sql = "SELECT Tune_Name,Tune_Type FROM Mobile_Tunes WHERE Tune_Type =
'".$tunetype."' AND Tune_Name LIKE '".$tunename."%' ";
Also be sure to run each of the parameters ( $tunetype and $tunename )
thru mysql_real_escape_string() to prevent SQL Injection attacks.
Sometimes the SQL optimizer needs a little help ;->
John
On 9/12/07, Asim wrote:
> Hi
>
>
> i am including table structure and other results to you
>
>
> CREATE TABLE Mobile_Tunes
> (
> Tune_ID int(6) NOT NULL AUTO_INCREMENT,
> Tune_Name varchar(30) NOT NULL,
> Tune_Type varchar(10) NOT NULL,
> PRIMARY KEY (Tune_ID),
> UNIQUE KEY Tune_Name (Tune_Name),
> INDEX Tune_Type (Tune_Type)
> )ENGINE=MyISAM;
>
>
> mysql> desc mobile_tunes;
> +-----------+-------------+------+-----+---------+---------- ------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+---------- ------+
> | Tune_ID | int(6) | | PRI | NULL | auto_increment |
> | Tune_Name | varchar(30) | | UNI | | |
> | Tune_Type | varchar(10) | | MUL | | |
> +-----------+-------------+------+-----+---------+---------- ------+
> 3 rows in set (0.03 sec)
>
>
> mysql> explain select Tune_name, tune_type from mobile_tunes;
> +--------------+------+---------------+------+---------+---- --+------+-------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +--------------+------+---------------+------+---------+---- --+------+-------+
> | mobile_tunes | ALL | NULL | NULL | NULL | NULL | 4 | |
> +--------------+------+---------------+------+---------+---- --+------+-------+
> 1 row in set (0.02 sec)
>
>
> mysql> select Tune_name, tune_type from mobile_tunes;
> +-----------+-----------+
> | Tune_name | tune_type |
> +-----------+-----------+
> | pakistani | midi |
> | pakistan2 | midi |
> | usa | midi |
> | uk | midi |
> +-----------+-----------+
> 4 rows in set (0.01 sec)
>
>
>
> ISSUED WARNINGS
> ****************************************
>
> Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 47
>
> Warning: mysql_query(): Your query requires a full tablescan (table Mobile_Tunes, 4 rows affected). Use EXPLAIN to optimize your query. in D:\Program Files\Apache Group\Apache2\htdocs\Breez\mtunes_main.php on line 70
>
>
> ************************************************************
> this table will be populated with at least 60,000 records. these 4 records are just for testing purpose.
> so if you can tell me what to do to avoid this warning, then it will be great help for me.
>
> Bye
>
>
>
>
>
> Niel Archer wrote:
> > > This shows you have NO indexes on the table. Assuming that 'Tune_Name'
> > > contains unique entries for each row, I'd suggest you do the following
> > > as a minimum:
> > >
> > > ALTER TABLE Mobile_Tunes ADD PRIMARY KEY (Tune_Name);
> >
> > How's that going to help if he's getting all records from the table?
>
> Depends what you mean by help.
> Improve performance of the query, not at all, but that wasn't the
> original question.
> In a previous post he said he'd added indexes which his EXPLAIN output
> clearly shows is not the case. That is simply an example of how to add
> a PRIMARY KEY after creating the table. However, it will stop duplicate
> entries from being entered and prepare the way for when he's NOT
> selecting all rows, so is not entirely wasted.
>
> > Also there's no way a database is going to use an index if there are
> > only 4 rows in the table.
>
> True, but if it was only ever going to have 4 entries in it I doubt he'd
> be using a Db at all. I'm assuming this is just a sample.
>
> --
> Niel Archer
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>
> Asim Jamil - 0092 345 4025907, Pakistan
>
> ---------------------------------
> Moody friends. Drama queens. Your life? Nope! - their life, your story.
> Play Sims Stories at Yahoo! Games.
--
--
John Mertic "Explaining a joke
is like dissecting a frog: you
jmertic@gmail.com understand it better,
but the frog dies in the
process."
-Mark Twain
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php