0

I am having an issue querying a couple of MySQL VIEWS.

The MySQL tables are generated hourly from a process that pulls data from a central Oracle database. Once the update is complete, a query automatically updates the VIEWS. (I do believe this is standard. I could be wrong).

At this point, I am using PHP to query said VIEWS to return data to the page. This is where my problem begins.

When the query begins, it can take well over 30 seconds to return 4 records. The VIEW contains no more than 30K records. But when I try to return a larger data set to the page, it now causes the server to freeze.

The query from my PHP script is very simple. It is looking like the following:

SELECT 
    DISCHARGE_ETA, 
    TERMINAL_DISCH, 
    IMPORT_RAMP, 
    ORIG_RAMP_FINAL_DEST_ETA,  
    POOL_LOCATION_FROM_IMP,  
    POOL_LOCATION_TO_IMP,  
    ROAD_HAULIER_IMP
// few more columns
FROM  
    view1
WHERE  
    " . $_SESSION['where'] . ";

I am using jQuery to send parameters over to the PHP script. PHP then builds the query by plugging all the parameters into the WHERE clause. The data is then returned via JSON.

The query that builds the actual VIEW in MySQL is a little more intense (currently not shown here).

My question is: are VIEWS the best route in retrieving the data I need?

I was thinking it would seem easier to query a TABLE instead of a VIEW. Perhaps the same hourly process that is updating the VIEWS could instead update a single TABLE. That way, I could query the TABLE instead of the VIEW.

Would this be the best bet? If not, are there any alternatives?

Edit

Here is the results from EXPLAIN

enter image description here

halfer
  • 19,824
  • 17
  • 99
  • 186
John Beasley
  • 2,577
  • 9
  • 43
  • 89
  • 1
    the view is already a query, so probably the query to generate the view is heavy. – farbiondriven Dec 05 '17 at 15:45
  • 2
    Please include the results of `SHOW EXPLAIN [the sql select for the view]`. A view doesn't work quite the way you think, it simply stores the command and joins, to run a little bit faster. When you query the view, it is still simply running that full select command. Chances are, you have an index issue somewhere (a foreign key relationship, or something in the WHERE clause, which isn't indexed), or your view has a nested query. – Tony Chiboucas Dec 05 '17 at 15:47
  • @TonyChiboucas - I can confirm there are no nested selects in the query. However, I will speak to my DBA about the foreign key relationships. – John Beasley Dec 05 '17 at 15:50
  • @farbiondriven - The view query is indeed very heavy. Should I display it? – John Beasley Dec 05 '17 at 15:50
  • 1
    @JohnBeasley yeah actually it is the only 'real' query. PHP is not asking much. – farbiondriven Dec 05 '17 at 15:52
  • 1
    @JohnBeasley, we _may_ need it, but for now, just the results of `EXPLAIN SELECT ...`. Ultimately, this is a query optimization issue, and you're going to probably have to rewrite that query, and maybe even restructure the tables. – Tony Chiboucas Dec 05 '17 at 15:53
  • I just added the results from EXPLAIN – John Beasley Dec 05 '17 at 15:56
  • 1
    @TonyChiboucas - I guess there are subqueries. – John Beasley Dec 05 '17 at 15:57
  • 1
    @JohnBeasley, there's also your MAIN index for that query, having NO INDEX. Plus all other indices are really big, 99-length key!? You're in need of some serious DB cleanup. – Tony Chiboucas Dec 05 '17 at 16:06
  • @TonyChiboucas Should my DBA know what that means? If not, how would I go about reducing the 99-length key? – John Beasley Dec 05 '17 at 16:10
  • 1
    @JohnBeasley, you should run a [BENCHMARK](https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_benchmark) of that query, as well as the `EXPLAIN`, and copypasta the results to your DBA. Reducing the [key_len](https://stackoverflow.com/questions/7643491/understanding-mysql-key-len-in-explain-statement) requires modifying the data-types of those columns. Getting the first key in your query (idx_pkg_api_hazd) INDEXED is even more important. Ultimately this optimization will require significant changes to your DB, as well as rewriting this query. – Tony Chiboucas Dec 05 '17 at 16:21
  • I thank all of you for your input. I'll relay this information to my DBA, and hopefully, I can resolve this issue. – John Beasley Dec 05 '17 at 16:24

0 Answers0