473,320 Members | 1,580 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Speeding up select queries on table with 3million rows.

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!

Jul 23 '05 #1
9 3380
mf*****@gmail.com wrote:
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 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.
Jul 23 '05 #2
<mf*****@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
<snip>
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` )
); <snip> 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?


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
Jul 23 '05 #3
Thomas Bartkus wrote:
<mf*****@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
<snip>
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` )
);

<snip>
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?


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.


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/
Jul 23 '05 #4

"Chris Hope" <bl*******@electrictoolbox.com> wrote in message
news:11*************@216.128.74.129...
Thomas Bartkus wrote:
<mf*****@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
<snip>
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` )
); <snip>
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?


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

<snip>
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/


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
Jul 23 '05 #5
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

Jul 23 '05 #6
mf*****@gmail.com wrote:
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?
They don't use a relational database; IIRC it's some sort of file based
tree system.
What happens when there are both multicolumn and single column
indexes? Which one does MySQL use?
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 ..."
I'd appreciate if anyone could suggest a good introductory book on
these database topics. I find this fascinating.


--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
Jul 23 '05 #7
mf*****@gmail.com wrote:
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?
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/
What happens when there are both multicolumn and single column indexes?
Which one does MySQL use?


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/my...imization.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.
Jul 23 '05 #8
mf*****@gmail.com wrote:
How does google do it in milliseconds?? Is it just super hardware?


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

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

Regards,
Bill K.
Jul 23 '05 #9
On 1 Feb 2005 13:20:31 -0800, mf*****@gmail.com wrote:
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


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

Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Eric B. | last post by:
Hi, I'm somewhat new to MySql. I've been using it for a while, but pretty much out of the box setup, and am starting to suffer heavily with my larger tables. I have a table with 5,000,000+...
6
by: LRW | last post by:
I have a querey formed with a few joins and a lot of fields, and no kidding, it takes up to 2 minutes for the PHP page to poulate with all the results (usually around 300). Now, we have a pretty...
9
by: Rowland Hills | last post by:
I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM...
5
by: malcolm | last post by:
Example, suppose you have these 2 tables (NOTE: My example is totally different, but I'm simply trying to setup the a simpler version, so excuse the bad design; not the point here) CarsSold {...
3
by: John D | last post by:
We have a dynamic SP that dependant on a user name will run a selected tailored to them. One of the criteria is the number of rows retrieved, which we include using 'top @varNoOfRows' in the...
4
by: psql-mail | last post by:
I am running a SELECT to get all tuples within a given date range. This query is much slwoer than i expected - am i missing something? I have a table 'meta' with a column 'in_date' of type...
12
by: TP | last post by:
Here is my problem. I need to display a table about which I have no information except the table name. Using metadata I can somehow show the column names and record values. But my table has 1...
6
by: A_M_IS | last post by:
Hello group, hope to anybodys help on my temporary blackout. (Using Access 2003 on XP Win.) I know how to create and edit temporary query recordset, then I can set this data source as my form...
4
by: Chris | last post by:
Can't seem to figure out how to do this and have been reading for some time now...... I want to select a row count from a table name in SYSTABLES. This statement does not return what I needed,...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.