Databases
StackOverflow2013
Postsdbo
POSQL Slow Loading Time
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Slow Loading Time
    primarykey
    data
    text
    <p>I have the below code and it loads fine when there are only a few <code>follower_id</code> or <code>username</code> in the table <code>tblfollowers</code>.<br> But as soon as it gets into the thousands, it loads really slowly.<br> Is there a better way to write this? </p> <p>I added indexes on the fields it joins on and that doesnt seem to make a difference.</p> <pre><code>$scheduler-&gt;render_table("events LEFT JOIN tblfollowers ON events.id_user = tblfollowers.username WHERE events.status ='active' AND((tblfollowers.follower_id)='$test') OR ((events.id_user) ='$test') GROUP BY events.event_id, events.event_name, events.user_name, events.id_user, events.time, events.details, events.location, events.dresscode ORDER BY events.timestamp DESC" ,"event_id","start_date, start_date,event_name,details"); $scheduler-&gt;render_sql("SELECT event_id, start_date, end_date, event_name, details FROM events "); </code></pre> <p>Here's the explain for this query:</p> <pre> 1 SIMPLE events ALL NULL NULL NULL NULL 1593 Using where; Using temporary; Using filesort 1 SIMPLE tblfollowers ref PRIMARY PRIMARY 4 dbhappps.events.id_user 17 Using where; Using index </pre> <p><strong>Here are the tables</strong></p> <blockquote> <p>Blockquote</p> </blockquote> <p><strong>Table Events:</strong></p> <pre><code>event_id int(11) Primary Unique Index event_name varchar(400) user_name varchar(155) id_user int(11) Primary Unique Index start_date datetime end_date datetime details varchar(700) location varchar(255) dresscode varchar(255) timestamp timestamp on update CURRENT_TIMESTAMP </code></pre> <blockquote> <p>Blockquote </p> </blockquote> <p><strong>tblfollowers:</strong></p> <pre><code>username int(11) primary follower_id int(11) primary timestamp timestamp on update CURRENT_TIMESTAMP </code></pre> <p><strong>How do I speed this query up?</strong> </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSteven
    1. USSteven
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Slow Loading Time
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Slow Loading Time
      1. This table or related slice is empty.
      1. VTDownMod
    3. VO
      singulars
      1. POSQL Slow Loading Time
      1. USSteven
      1. VTBountyStart
    1. COCan you better explain your table schema. Specifically, provide information on your indexes.
      singulars
      1. POSQL Slow Loading Time
      1. USMike Brant
    2. CO@MikeBrant There are no indexes on the table. At one point I had an index on the id_user and event_id but as you see below the answer says that indexes would slow upload time. If you look at the very bottom I did and EXPLAIN SELECT on the query so you can see the results of that
      singulars
      1. POSQL Slow Loading Time
      1. USSteven
    3. COWithout your index you are doing full table scans. Yes indexes will add to load time, but that's the price you pay. Also look at "(tblfollowers.follower_id)='$test') OR ((events.id_user) ='$test')". Might it not be better to move id_user check to a where clause?
      singulars
      1. POSQL Slow Loading Time
      1. UScrafter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload