3

I have MySql table with a huge ammount of rows. I m doing the simple request and it works pretty fast. The only one problem fetching the data to client is terribly slow for about 150 secs when the request itself takes only 0.3 sec.

I am using php 5.3 + ZendFramework on client and Amazon EC2 MySql instance. PHP code and MySql are located on the different servers.

Can anyone tell me how to increase the data fetching speed?

Sergey
  • 5,208
  • 25
  • 36
Timur Stash
  • 31
  • 1
  • 3
  • Can you provide table structure and query you are running? need to check what indexes you have etc. If they are all in-order then I'd look at a partitioning strategy. – Ian Wood Oct 22 '12 at 09:11
  • 5
    So much more information required here! Your query, its execution plan and profile, (relevant) database schema, server configuration, network design, hardware specifications... – eggyal Oct 22 '12 at 09:11
  • I think you up to your connection's speed limit through amazon ec2 and your server. Also you have slow connection from your server and your computer – Sergey Oct 22 '12 at 09:13
  • 3
    What is huge? Can you define huge. And how is your query worked out. Did you index your database, are you querying all your data propperly? There is also a difference between retrieving data wireless and through cable, so with the information you give up here is not really alot to work on. – Dorvalla Oct 22 '12 at 09:13
  • The simplest query "select * from some table" and nothing more. There is a primary key. Should be enough. The request itself is fast (0.3 sec). I am worry about the speed of data transfering to client (150 secs). – Timur Stash Oct 22 '12 at 09:13
  • Maybe there are some methods to send the compressed query result? – Timur Stash Oct 22 '12 at 09:16
  • How many rows is it actually fetching? And its not that, Timur. The information is being stored in an array, which is a 2 byte per character or so. So that shouldn't be it. – Dorvalla Oct 22 '12 at 09:18
  • Does the same query execute faster when you run in an sql client? There might be some network issue between your website and mySQL database hosting servers. – Nish Oct 22 '12 at 09:24
  • 1
    Your question, as I understand it, implies that there's something slower than it should be. So it's not about how to reduce the amount of data but to get the expected speed for the given amount of data. In that case you might want to ask this question on http://serverfault.com where competent admins can (most likely better than us mere developers) tell you how to identify the exact component that is the bottleneck and - if it's really the transport - maybe even how to fix it. (But the answer could be the same as here: What do you expect when transferring x*10GB MySQL->PHP ;-) ) – VolkerK Oct 22 '12 at 09:29

3 Answers3

2

Based on the comments you eventually provided the query that you are running.

select * from table

You also said that you have "a huge ammount of rows". This should be simple enough for you to understand why it takes forever to transfer to the box querying your database.

And also, just because your query runs fast, you still have to deal with network limitations. Plus, once you actually receive the result, hypothetically, if you were using an ORM for instance, you could find that hydration of the result set adds complexity and time to your request.

Overall, you answered this question yourself. Don't do a select * from table where the table is "enormous".

You should look at adding limits and offsets to your query and only select the rows that you actually need.

Also, "huge" is extremely subjective. To me, a large table is about 10million rows. But to someone else 10million is pretty small. I wouldn't even like to put a number on what a huge table might be.

Layke
  • 51,422
  • 11
  • 85
  • 111
  • I agree, i was actually trying to edit one of my comments on this. If you say your table is enormous, its just plain stupid to do `select * from table`. I dont know the definition from huge in your eyes... – Dorvalla Oct 22 '12 at 09:26
  • (Also, as a suggestion comment, you should provide as much information as possible in your original question. The iterrogation in the comments above should be sufficient to show that you didn't provide even half the required information we needed to answer). – Layke Oct 22 '12 at 09:27
  • There are no additional information everything is simple. Just a table with 6 columns 50 000 records. And one query: "select * from table_name". That's it. And the main trouble is to send all these records to client. I want to find a way to speed up the data transfering between mysql server and client. As I can't increase a speed of network connection I want to find a way to compress the result data of query to transfer it faster. I need to select every row from the table to put them into csv file. So I do not think that I can use the limitation. – Timur Stash Oct 22 '12 at 09:50
0

The slowness is because you retrieve all the data from the table and since you have two different servers for your database and website all the data needs to be transfered throught the network will introduce additional slowness.

If you ask why it is faster when you run in your client, it is because most of the sql clients adds limit to the number of rows automatically.

Nish
  • 2,296
  • 2
  • 14
  • 19
0

As all comments are saying: you'll have to provide some more info to get an exact (or at least less vague) answer. But a few rules of thumb you should keep in mind for every query you write:

  • Avoid SELECT * whenever possible, especially things like SELECT COUNT(*), only select those fields that are of interest to you.
  • Split your data over cleverly indexed tables and use JOIN. A good JOIN can boost performance, often quite dramatically.
  • Use EXPLAIN EXTENDED, avoid situations where MySQL has to create a temporary table on disk
  • When using EXPLAIN, also check for implicit CAST's, or COLLATION conversions. Unlike PHP, an UNSIGNED INTEGER is not the same as a VARCHAR. Think of it as $x = 123; followed by if ($x === '123') which will be false: a string !== an int...
  • Avoid large data-fields (like MEDIUMTEXT) as they will always result in disk access
  • Avoid LIKE as much as you can, and especially when you're using wildcards (x LIKE 'y%z')
  • Remember: % isn't the only wild-card: _ is a sort of joker for a single char
  • You have an index, but could you do with more indexes, without getting silly? Don't index too little, and don't index too much
  • Have you chosen the right storage engine?
  • EXPLAIN, EXPLAIN and EXPLAIN again...
  • If you have to use a slow query, cache the results
  • LIMIT-ing the results for "huge" data-sets is a must. If you're fetching vast lumps of data, what are you doing with them? I can't possibly conceive a situation where all data is going to be sent to the client as one big lump, is it?
Elias Van Ootegem
  • 74,482
  • 9
  • 111
  • 149
  • Why would you avoid `SELECT COUNT(*)` ? – VolkerK Oct 22 '12 at 09:43
  • @VolkerK: It's a good rule of thumb, because `COUNT(*)` behaves differently on various engines. Whereas it makes little difference on MyISAM tables, `COUNT(*)` is relatively expensive on InnoDB. Using `SELECT(some_index_field)` will behave a lot more predicatble, regardless of what engine you're using – Elias Van Ootegem Oct 22 '12 at 09:55
  • Also, using SELECT * isn't recommended, because of the lack of verbosity and insight that you can actually infer from the query. You have no idea what it is actually returning. Some developer could come along and change the schema and cause complications/problems for your query. – Layke Oct 22 '12 at 11:00