Speeding up select queries on table with 3million rows.

[mfyahya@gmail.com]

Hi,
I'm new to databases :) I need help speeding up select queries on my
data which are currently taking 4-5 seconds. I set up a single large
table of coordinates data with an index on the fields I use most
frequently in select queries. The data is about 100MB and index is
80MB. The table has the following structure:

CREATE TABLE `ptimes` (
`id` INT UNSIGNED NOT NULL ,
`rc` CHAR ( 1 ) UNSIGNED NOT NULL ,
`lat` INT NOT NULL ,
`long` INT NOT NULL ,
`ccode` CHAR( 2 ) NOT NULL ,
`admcode` CHAR( 4 ) NOT NULL ,
`nt` CHAR( 1 ) NOT NULL ,
`name` VARCHAR( 30 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
INDEX ( `lat` , `long` , `ccode` , `admcode` , `name` )
);

The rc, nt and ccode fields' data are repeated a lot. I mostly need to
run queries to lookup lat and long fields, given rc, ccode, admcode and
name. I won't be doing many insert or updates. Disk space is not a
problem. System is RedHat Linux AS3 on Pentium 4 with 512MB.

I appreciate any help improving the table structure. Would it be
possible to speed up the queries to under .5 sec on this
hardware/software?

Thanks!

[Bill Karwin]

Re: Speeding up select queries on table with 3million rows.

mfyahya@gmail.c om wrote:[color=blue]
> The rc, nt and ccode fields' data are repeated a lot. I mostly need to
> run queries to lookup lat and long fields, given rc, ccode, admcode and
> name.[/color]

I think you should create individual indexes for the fields you use for
search criteria. That is, rc, ccode, admcode and name each should be
indexed individually (not a compound index as you have defined above).

In my opinion, compound indexes are useful for enforcing uniqueness
across a set of columns. But in practice for speeding up queries, they
frequently do not help, unless your search criteria use a left-sided set
of the column. That is, if you were searching with criteria of lat,
long, and ccode, the index could be used. But if you search with
criteria in ccode, admcode, and name, then the index cannot be used.

To understand why, think of a telephone book. A telephone book is
indexed by the equivalent of a compound index on (lastname, firstname).
If I do a search for all people named "John", the indexed order of the
book is useless to me; I still have to do a page-by-page scan of the book.

And finally, it appears in your table definition that the rc field is
not indexed at all.

So I advise this change:

ALTER TABLE `ptimes` ADD INDEX (`rc`);
ALTER TABLE `ptimes` ADD INDEX (`ccode`);
ALTER TABLE `ptimes` ADD INDEX (`admcode`);
ALTER TABLE `ptimes` ADD INDEX (`name`);

Then try your query again. MySQL might still decide not to use indexes
for columns with a small number of distinct values. I bet the most
useful index you could create would be one on the `name` field.

Regards,
Bill K.

[Thomas Bartkus]

Re: Speeding up select queries on table with 3million rows.

<mfyahya@gmail. com> wrote in message
news:1107209715 .691396.38190@f 14g2000cwb.goog legroups.com...
<snip>[color=blue]
> CREATE TABLE `ptimes` (
> `id` INT UNSIGNED NOT NULL ,
> `rc` CHAR ( 1 ) UNSIGNED NOT NULL ,
> `lat` INT NOT NULL ,
> `long` INT NOT NULL ,
> `ccode` CHAR( 2 ) NOT NULL ,
> `admcode` CHAR( 4 ) NOT NULL ,
> `nt` CHAR( 1 ) NOT NULL ,
> `name` VARCHAR( 30 ) NOT NULL ,
> PRIMARY KEY ( `id` ) ,
> INDEX ( `lat` , `long` , `ccode` , `admcode` , `name` )
> );[/color]
<snip>[color=blue]
> I appreciate any help improving the table structure. Would it be
> possible to speed up the queries to under .5 sec on this
> hardware/software?[/color]

Change [name VARCHAR(30)] to [name CHAR(30)].

By allowing the table to assign a fixed length (30) characters, it will cost
you a little bit of disk space BUT it will pay back by allowing MySQL to
declare the table STATIC rather than DYNAMIC and make indexing and locating
records more efficient. Dramatically more efficient for large tables.

There is no downside to this other than it will consume more diskspace - and
probably not much more at that.
Thomas Bartkus

[Chris Hope]

Re: Speeding up select queries on table with 3million rows.

Thomas Bartkus wrote:
[color=blue]
> <mfyahya@gmail. com> wrote in message
> news:1107209715 .691396.38190@f 14g2000cwb.goog legroups.com...
> <snip>[color=green]
>> CREATE TABLE `ptimes` (
>> `id` INT UNSIGNED NOT NULL ,
>> `rc` CHAR ( 1 ) UNSIGNED NOT NULL ,
>> `lat` INT NOT NULL ,
>> `long` INT NOT NULL ,
>> `ccode` CHAR( 2 ) NOT NULL ,
>> `admcode` CHAR( 4 ) NOT NULL ,
>> `nt` CHAR( 1 ) NOT NULL ,
>> `name` VARCHAR( 30 ) NOT NULL ,
>> PRIMARY KEY ( `id` ) ,
>> INDEX ( `lat` , `long` , `ccode` , `admcode` , `name` )
>> );[/color]
> <snip>[color=green]
>> I appreciate any help improving the table structure. Would it be
>> possible to speed up the queries to under .5 sec on this
>> hardware/software?[/color]
>
> Change [name VARCHAR(30)] to [name CHAR(30)].
>
> By allowing the table to assign a fixed length (30) characters, it
> will cost
> you a little bit of disk space BUT it will pay back by allowing MySQL
> to
> declare the table STATIC rather than DYNAMIC and make indexing and
> locating records more efficient. Dramatically more efficient for large
> tables.
>
> There is no downside to this other than it will consume more diskspace
> - and probably not much more at that.[/color]

Note also http://dev.mysql.com/doc/mysql/en/si...n-changes.html

Although the `admcode` column is declared as a char it will actually be
a varchar under MySQL's silent column changes. As long as there are
*no* varchars in the table then they can all be fixed length char
types. Just another of those MySQL "gotchas"

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Thomas Bartkus]

Re: Speeding up select queries on table with 3million rows.


"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
news:1107287397 _6626@216.128.7 4.129...[color=blue]
> Thomas Bartkus wrote:
>[color=green]
> > <mfyahya@gmail. com> wrote in message
> > news:1107209715 .691396.38190@f 14g2000cwb.goog legroups.com...
> > <snip>[color=darkred]
> >> CREATE TABLE `ptimes` (
> >> `id` INT UNSIGNED NOT NULL ,
> >> `rc` CHAR ( 1 ) UNSIGNED NOT NULL ,
> >> `lat` INT NOT NULL ,
> >> `long` INT NOT NULL ,
> >> `ccode` CHAR( 2 ) NOT NULL ,
> >> `admcode` CHAR( 4 ) NOT NULL ,
> >> `nt` CHAR( 1 ) NOT NULL ,
> >> `name` VARCHAR( 30 ) NOT NULL ,
> >> PRIMARY KEY ( `id` ) ,
> >> INDEX ( `lat` , `long` , `ccode` , `admcode` , `name` )
> >> );[/color]
> > <snip>[color=darkred]
> >> I appreciate any help improving the table structure. Would it be
> >> possible to speed up the queries to under .5 sec on this
> >> hardware/software?[/color]
> >
> > Change [name VARCHAR(30)] to [name CHAR(30)].
> >[/color][/color]
<snip>[color=blue]
>
> Note also http://dev.mysql.com/doc/mysql/en/si...n-changes.html
>
> Although the `admcode` column is declared as a char it will actually be
> a varchar under MySQL's silent column changes. As long as there are
> *no* varchars in the table then they can all be fixed length char
> types. Just another of those MySQL "gotchas"
>
> --
> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/[/color]

His "name" field is the only declared VARCHAR. It is the only non-fixed
length field he has so I *think* he can ALTER it to CHAR and be done with
it. You are correct to point out "As long as there are *no* varchars in the
table " and this is apparently the case here - or at least it will be when
he changes that one column.

I have noted in the past that if there are multiple VARCHAR fields that
MySQL will refuse (without telling you!) to change it to CHAR. How rude!
The only way I have found out of this is to re-create the whole table in one
swoop with all fixed width CHAR in a CREATE TABLE declaration. However,
with only one single VARCHAR, I think you can just ALTER it to CHAR.

But - I have been known to be wrong on rare occasion :-)
Thomas Bartkus

[mfyahya@gmail.com]

Re: Speeding up select queries on table with 3million rows.

Thank you for the suggestions. The queries are taking around 2 secs
after I created separate indexes and changed varchars to chars. How
does google do it in milliseconds?? Is it just super hardware?

What happens when there are both multicolumn and single column indexes?
Which one does MySQL use?

I'd appreciate if anyone could suggest a good introductory book on
these database topics. I find this fascinating.

Yahya

[Chris Hope]

Re: Speeding up select queries on table with 3million rows.

mfyahya@gmail.c om wrote:
[color=blue]
> Thank you for the suggestions. The queries are taking around 2 secs
> after I created separate indexes and changed varchars to chars. How
> does google do it in milliseconds?? Is it just super hardware?[/color]

They don't use a relational database; IIRC it's some sort of file based
tree system.
[color=blue]
> What happens when there are both multicolumn and single column
> indexes? Which one does MySQL use?[/color]

If you index col1, col2 and col3 in a multicolumn index and nothing
else, then a query on col2 or col3 alone won't be able to use the index
and you would need to create separate indexes for them.

However, if you query col1 only it will use this index; if you use col1
and col2 it will use this index; same for querying all three columns.

If you want to see what MySQL is doing when it runs a query use EXPLAIN
eg "explain select foo from bar where ..."
[color=blue]
> I'd appreciate if anyone could suggest a good introductory book on
> these database topics. I find this fascinating.[/color]

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Bill Karwin]

Re: Speeding up select queries on table with 3million rows.

mfyahya@gmail.c om wrote:[color=blue]
> Thank you for the suggestions. The queries are taking around 2 secs
> after I created separate indexes and changed varchars to chars. How
> does google do it in milliseconds?? Is it just super hardware?[/color]

Super hardware (actually a whole building of hardware) and some patented
proprietary indexing technology that is quite unlike a mere RDBMS.

I'd say if you need performance and capabilities like Google, MySQL is
not the right technology to use. You might want to consider one of
their rackmount servers:

http://www.google.com/enterprise/

[color=blue]
> What happens when there are both multicolumn and single column indexes?
> Which one does MySQL use?[/color]

MySQL has a series of criteria for when to use indexes. You should read
chapter 7 of the MySQL online docs for starters:

http://dev.mysql.com/doc/mysql/en/mysql-optimization.html

The MySQL web site also has a few pages for books that are useful for
MySQL users. The one I see that you might find most interesting is "SQL
Performance Tuning" by Peter Gulutzan, Trudy Pelzer.

Regards,
Bill K.

[Bill Karwin]

Re: Speeding up select queries on table with 3million rows.

mfyahya@gmail.c om wrote:[color=blue]
> How does google do it in milliseconds?? Is it just super hardware?[/color]

Here's a page where you can read quite a bit about the Google technology!

http://en.wikipedia.org/wiki/Google

Regards,
Bill K.

[Paul]

Re: Speeding up select queries on table with 3million rows.

On 1 Feb 2005 13:20:31 -0800, mfyahya@gmail.c om wrote:
[color=blue]
>Thank you for the suggestions. The queries are taking around 2 secs
>after I created separate indexes and changed varchars to chars. How
>does google do it in milliseconds?? Is it just super hardware?
>
>What happens when there are both multicolumn and single column indexes?
>Which one does MySQL use?
>
>I'd appreciate if anyone could suggest a good introductory book on
>these database topics. I find this fascinating.
>
>Yahya[/color]

"MySQL" by Paul DuBois covers a lot of these topics pretty well.