Hey guys,

I'm running MySQL on a windows NT 4 server, and it's been running like a dream until I imported a database with close to 70,000 unique records in it.

The database itself takes up right around 9 mb, and every query I make to the larger tables times out and slows my web server to a crawl. I'm using MyISAM tables and the most recent non beta release of MySQL.

Is there anything I can do to make these queries faster? Could modifying the table type improve performance? If so what table type do you recomend?

This is an enterprise envirenment, and the release notes on the newer version of MySQL advised against using the beta version in a mission critical setting. Has anyone tried this? What kind of results did you have?

Thanks for the help.

I know this is a silly question, and that you've already looked into it...

But, you do have indexes on the table / columns that you're querying, right?

The table has a primary key, but I don't think the person who designed the origenal database put any other keys or indexes in.

Yes.
Does the table need more indexes?

There are a few things you could do:


Like others has said, you need to add more keys. Try enabling slow query log to identify which field(s) that need indexing
Optimize the mysql server, see http://www.mysql.com/doc/O/p/Optimising_the_Server.html
Last but not least, you can always upgrade to a better o/s and/or hardware.


And also if you look at the MySQL manual, it contains one full chapter on optimizing MySQL.

Nameless - rather than playing twenty questions, it would be most efficient if you posted

- the primary key
- a sample SQL query that performs poorly

Based on what little you've reported it seems highly likely that your queries are forcing a complete table scan, if not more than one depending on the query, because the queries reference columns not included within an index.

This is basic stuff, SQL 101.

Rather than leaping to conclusions (different table types, beta software etc), its best to report the basic facts as you know them so that people can assist.

Offered with respect, I highly recommend reading:

How To Ask Questions The Smart Way
http://www.tuxedo.org/~esr/faqs/smart-questions.html

Originally posted by priyadi
Like others has said, you need to add more keys. Try enabling slow query log to identify which field(s) that need indexing

Is that displayed in the part "slow_queries" in the mysqladmin extended-status part?

'cause I'm interrested in this thing too ;)

The slow queries log is either setup in my my.cnf or via the command line. Command line doc (http://www.mysql.com/doc/S/l/Slow_query_log.html). I don't remember the value for my.cnf, but I'm sure you'll be able to find it on the MySQL site.

Frank

Since I'm using Ensim, and I have no idea where to alter startup command lines for that piece of software, and my slow_queries is saying 0, I s'pose I don't have any slow queries?

Just search your file system for my.cnf and see if you find something. On a single user NT environment it would be within the /mysql subdir; I have no idea where Ensim would have such things.

And its always possible there is only one my.cnf on NT. I don't have an answer for you.

But - you can make progress simply by analysing your queries the old fashioned way, with your eyes. In the simplest case, consider the following:

select user_name, password, email from people where email = 'me@foo.com'

If your table looks like this:

user_id integer primary key
user_name varchar(50)
password varchar ...
email ...
... other stuff

The above query across 70,000 records *will* take some time unless you have an index on

email


Imagine a python function called
get_password(user_name):
r = query("""select password from people where user_name = %s""", user_name)
return r.password

Means that your table had better have an index on user_name as well.

A basic rule of thumb - any column on the right side of the where clause (or joins, unions) will likely need an index built upon it when the table is large.

There are some exceptions to this but that discussion is beyond a simple post here.

A quick check of your code in an area that is running slowly will undoubtedly turn up some SQL that is comparing columns that are not indexed. In large tables this will certainly cause the problems you described.

I just changed some mySQL setting in Ensim myself. To change the settings you'll have to edit the my.cnf and my.cnf.admin_appl files, which both can be found in /etc

Or you can just do it the easy way :D.

Grab a copy of the SQL trans running slow. Log in with the mysql client (i.e.)

mysql -u user-id -p database

then just add "explain" in front of your query and MySQL will tell you how it will process the query.

explain select blah
from table_1
where blah

Frank

I suppose explain (like show query plan in SQL server) is useful, but frankly if someone is running any sort of significant sql application they'd best know what the impact of writing even a simple query is.

Its also doubtful that a display like this will help someone who doesn't understand the basic concepts:

Query column not indexed:

mysql> explain select fullname from users where fullname = 'Sid Savon';
+-------+------+---------------+------+---------+------+-------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+-------+------------+
| users | ALL | NULL | NULL | NULL | NULL | 19747 | where used |
+-------+------+---------------+------+---------+------+-------+------------+
1 row in set (0.00 sec)

Query indexed column 'email':

mysql> explain select fullname from users where email = 'foo@bar.com';
+-------+------+---------------+-------+---------+-------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+-------+---------+-------+------+------------+
| users | ref | email | email | 97 | const | 4 | where used |
+-------+------+---------------+-------+---------+-------+------+------------+
1 row in set (0.01 sec)

Okay, I have just added the line

log-slow-queries=/var/lib/slow.log

under the part of [mysqld] in the my.cnf file... however, how am I able to check if it really *IS* working?

.. since "mysqladmin variables" doens't return anything about log-slow-queries or sth ;)

Thanks in advance!

mwatkins,

Well you would "think" that you would know how the optimizer would process a query, but it's better if it would actually tell you (i.e. explain).

blazenet,

You have to stop/start mysql for that my.cnf setting to take effect.

Frank

Thanks ffeingol!

Strange that a normal restart didn't work...

umm. What did not worK? Did you get it going again?

Frank

Yeah, well, when I just did a restart, there wasn't a slow.log being created... when I did a stop and then a start, a slow.log *WAS* created...

Seemed strange to me ;)

I'd be interested in seeing how you did a "restart" on MySQL. I though you had to do a stop/start.

Frank

ffeingol

I guess my point on this thread is that the 'basics' are the place to start if one hopes to have any mastery. The thread starter was ready to dive into esoteric remedies including possibly running a beta version, all for a measly 70,000 record table!

When writing, modifying or even managing code I think its not too much to expect that someone would know the impact of even simple queries.

select * from users where email = 'foo@bar.com' is not a complex query yet can run very slow if column email does not have an index on it and the table is large. Not very complex thought process and something that should become second nature.

Why care at all? Think about what happens to a shared machine when you've got dozens if not hundreds of SQL newbies or lusers all running software they can't maintain, manage or understand - only takes a couple queries against a large table to really kill the machine.

cheers
Mike

Mike,
Thanks for the advice, but you leap to conclusions about people's backgrounds to quickly. Even though your posting was useful, I can't help but be offended by your tone.

How do you know intuitively (without a lick of research on your part) and suddenly have this indepth knowledge of my portfolio?

Although reletively new to MySQL, I've been working with SQL server since it was released. Obviously they are different programs. But I think that you are incinuating that I am not a competent DBA. This my friend is not true.

I ask a simple question, and you fly off the handle and give us all a speach about losers on shared servers. I'm not on a shared server, nor do my employers consider me a loser. Hence the current term of employment with this employer for an extended period of time.

See if I ever ask for technical help here again.

But not looking at the offended tone, did the answer fix your problem?

Still working on it.
I have limited access over the weekend from home.
Should know by tuesday when I get back to the office.