Home > Archive > PHP DB > September 2007 > [PHP-DB] showing warnings
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
[PHP-DB] showing warnings
|
|
|
|
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 <jmertic@gmail.com> 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:
> ...........................................................................
> page
>
>
> --
> --
> 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.
| |
| Niel Archer 2007-09-11, 7:58 am |
| >
> 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
| |
| Niel Archer 2007-09-11, 7:58 am |
| > > This shows you have NO indexes on the table. Assuming that '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
| |
|
| 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 <not@chance.now> wrote:
>
> 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!
| |
|
| 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 <not@chance.now> wrote:
>
> 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.
|
|
|
|
|