Re: showing warnings

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 <j.asim@yahoo.com> 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 <not@chance.now> 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