CODEWITHSUNDEEP
mysqlsql
Rich C
Rich C

Reputation: 3244

MySQL query slow on localhost - mostly network duration

I am running a very simple query on an indexed column in a 20 mln row table.

select * from prvol where date = '20100203';

It takes about 22 seconds. I am new to sql, but think that an indexed column should be faster than this. There is no memory issue. Also, the output says the time is mostly in network. I'm running the query on the same machine the server is on.

/* 0 rows affected, 6,882 rows found. Duration for 1 query: 0.828 sec. (+ 21.438 sec. network) */

What does that network time mean? Would you expect this query to run faster?

EDIT: as requested, here is some output.

EXPLAIN SELECT * FROM prvol WHERE date = '20100203';
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
"1","SIMPLE","prvol","ref","Index 1","Index 1","4","const","6881","Using where"


SHOW CREATE TABLE prvol;
"Table","Create Table"
"prvol","CREATE TABLE `prvol` (
  `exch` varchar(10) DEFAULT NULL,
  `ticker` varchar(10) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `open` float unsigned DEFAULT NULL,
  `high` float unsigned DEFAULT NULL,
  `low` float unsigned DEFAULT NULL,
  `close` float unsigned DEFAULT NULL,
  `vs` float unsigned DEFAULT NULL,
  `aclose` float DEFAULT NULL,
  KEY `Index 1` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"

Upvotes: 1

Views: 8484

Answers (4)

user6407136
user6407136

Reputation:

I'm using this library. It was faster than google's.

<script src="https://api.mqcdn.com/sdk/place-search-js/v1.0.0/place-search.js"></script>
<link type="text/css" rel="stylesheet" href="https://api.mqcdn.com/sdk/place-search-js/v1.0.0/place-search.css"/>


<script type="text/javascript">

var ps;
window.onload = function () {
    ps = placeSearch({
        key: 'lYrP4vF3Uk5zgTiGGuEzQGwGIVDGuy24',
        container: document.querySelector('#place-search-input'),
        useDeviceLocation: false,
        collection: [
            'poi',
            'airport',
            'address',
            'adminArea',
        ]
    });
}

Upvotes: 0

Rich C
Rich C

Reputation: 3244

I eventually figured out why my query was slow. See here for answer. It ended up having nothing to do with network time. It was a cache size issue.

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107796

I actually think that the query is running perfectly fine.

To return 6,882 rows of N-columns (select *) in 0.828 sec is reasonable timing on reasonable hardware.

The network time 21.438 s is just how long it takes to transfer x MB over the network, where x = bytes per row * 7k, which could be tens of MB. But 21s on a network is a bit on the slow side - but this is not a query issue.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838974

Yes, absolutely it should run faster.

You probably have made one of these common mistakes:

  • You indexed a column, but it wasn't the date column.
  • You created a multicolumn index but the date column is not the first column in the index and therefore cannot be used for this query.
  • You clearly remembering adding an index but somehow the index seems to have "vanished" (possibly because you ran the query and it gave an error but you didn't notice the error message).

To find out which it is, run SHOW CREATE TABLE prvol and post the output.

Another thing you could do to improve the situation is to avoid the use of SELECT *. Always select only the columns you need. Even if you think you need all columns you should probably still list them explicitly for safety in case the schema changes in the future.

Upvotes: 2

Related Questions