Error msg that keeps haunting me when using 'select' in mysql_query

Pulkkinen

What does this error message exactly tell:

Warning: mysql_query() [http://www.mysql.com/doc]: Your query requires a full tablescan (table tunnukset, 10 rows affected). Use EXPLAIN to optimize your query.

mysql_query function is (I suppose) correct. 😕

mourisj

This means that you should use EXPLAIN to get hints on how to optimize your query.

If a full tablescan is required, it means that there is no index/primary key on the table; for each execution, the table has to be looked up record by record. This is time consuming and even will cause heavy activity on your harddrives. With smalls tables, this problem is less important, but if you have huge tables with thousands ore more records, your query times will be awfully slow, you might even get timeouts.

An index can speed up the query significantely and reduce disc access.

I don't know about MySql, but on Oracle DBs , full tablescns are never cached, so you start the same thing over and over again. Index queries are cached which, again, increases performance.

Pulkkinen

Hm... I have already set primary key for one field. It still keeps printing warnings. My first field in that table is called 'id' and it's mediumint type, auto-incrementing.

ahundiak

Actually, it's a warning not an error so you could just ignore it.

But the basic problem is that your SELECT statement is doing a search on an unindexed field which in turn requires a full scan of the table. Does not really matter if you have a promary key or not.

So something like this:
SELECT user_id FROM user WHERE username='whatever';
Can trigger the warning unless you create an index on username

There could be other causes for the warning. If creating indexes does not help then please post your query along with the results of EXPLAIN.